editable datagrid dropdownlist problem in webpart

[Daniel Doyle]

Hello and apologies in advance for the amount of code in this post. I've
also sent this message to the Sharepoint group, but thought that ASP.NET
developers may also be able to help, even though it's a Sharepoint WebPart.
I'm trying to do something fairly simple, create a datagrid that displays
where
and when a person works and allows them to change some of the information
via DropDownLists.
When the user clicks to edit a row, three of the columns appear as
DropDownLists and should show the current value.
For the 'day of the week' and 'part of the day', selecting the current value
is easy as it's the same as the value shown by the label, i.e. the
DataTextField and DataValueField are the same for those DropDownLists.
However, the 'place' DropDownList has a DataTextField of 'place' and the
DataValueField is 'place code'. I can't select an item in the DropDownList
by using what is in the label, as the label displays the 'place'.
To get around this, I added an extra column for 'place code' and hoped I
could set the SelectedValue of the DropDownList equal to the value of this
label when I'm binding the DropDownList, but I can't seem to get this
working.
Ideally, I would like to do this without the extra 'place code' column,
which I intended to make invisible, but anything is better than what I have
now!

Any help would be greatly appreciated.

Here's the code.... I've tried to make it a little more generic and removed
the namespace declaration etc.
--------------------------------------------
public class EditDays : Microsoft.SharePoint.WebPartPages.WebPart
{
// My controls
protected DataGrid dgDays;;
Label lblMessages;

private const string defaultText = "";

private string text = defaultText;

[Browsable(false),
Category("Miscellaneous"),
DefaultValue(defaultText),
WebPartStorage(Storage.Personal),
FriendlyName("Text"),
Description("Text Property")]
public string Text
{
get
{
return text;
}

set
{
text = value;
}
}

protected override void RenderWebPart(HtmlTextWriter output)
{
if (dgDays.Items.Count <= 0 && lblMessages.Text == "")
{
lblMessages.Text = "<b>No days found.</b>";
}
else
{
dgDays.RenderControl(output);
}
lblMessages.RenderControl(output);
}

protected override void CreateChildControls()
{
//base.CreateChildControls ();
dgDays = new DataGrid();
dgDays.BorderWidth = 0;
dgDays.CellPadding = 5;
dgDays.AlternatingItemStyle.CssClass = "ms-alternating";
dgDays.HeaderStyle.CssClass = "ms-alternating";
dgDays.HeaderStyle.Font.Bold = true;
dgDays.AutoGenerateColumns = false;
// Add Edit and Delete Columns
EditCommandColumn colEdit = new EditCommandColumn();
colEdit.ButtonType = ButtonColumnType.LinkButton;
colEdit.EditText = "Edit";
colEdit.CancelText = "Cancel";
colEdit.UpdateText = "Update";

// Add the columns
TemplateColumn colPerson = new TemplateColumn();
TemplateColumn colPlace = new TemplateColumn();
TemplateColumn colDayOfTheWeek = new TemplateColumn();
TemplateColumn colPartOfTheDay = new TemplateColumn();
TemplateColumn colPlaceCode = new TemplateColumn();

colPerson.ItemTemplate =
new DataGridTemplate(ListItemType.Item, "person");
colPerson.EditItemTemplate =
new DataGridTemplate(ListItemType.Item, "person");
colPerson.HeaderTemplate =
new DataGridTemplate(ListItemType.Header, "person");
dgDays.Columns.Add(colPerson);

colPlaceCode.ItemTemplate =
new DataGridTemplate(ListItemType.Item, "place code");
colPlaceCode.EditItemTemplate =
new DataGridTemplate(ListItemType.Item, "place code");
colPlaceCode.HeaderTemplate =
new DataGridTemplate(ListItemType.Header, "place code");
dgDays.Columns.Add(colPlaceCode);

colPlace.ItemTemplate =
new DataGridTemplate(ListItemType.Item, "place");
colPlace.EditItemTemplate =
new DataGridTemplate(ListItemType.EditItem, "place");
colPlace.HeaderTemplate =
new DataGridTemplate(ListItemType.Header, "place");
dgDays.Columns.Add(colPlace);

colDayOfTheWeek.ItemTemplate =
new DataGridTemplate(ListItemType.Item, "day of the week");
colDayOfTheWeek.EditItemTemplate =
new DataGridTemplate(ListItemType.EditItem, "day of the week");
colDayOfTheWeek.HeaderTemplate =
new DataGridTemplate(ListItemType.Header, "day of the week");
dgDays.Columns.Add(colDayOfTheWeek);

colPartOfTheDay.ItemTemplate =
new DataGridTemplate(ListItemType.Item, "part of the day");
colPartOfTheDay.EditItemTemplate =
new DataGridTemplate(ListItemType.EditItem, "part of the day");
colPartOfTheDay.HeaderTemplate =
new DataGridTemplate(ListItemType.Header, "part of the day");
dgDays.Columns.Add(colPartOfTheDay);

// Add edit and delete columns last so that they appear at the end
dgDays.Columns.Add(colEdit);

// Add event handler
dgDays.ItemCommand +=
new DataGridCommandEventHandler(dgDays_ItemCommand);

lblMessages = new Label();

this.Controls.Add(dgDays);
this.Controls.Add(lblMessages);

BindGrid();
}

protected void BindGrid()
{
SqlConnection cnn = new SqlConnection
(ConfigurationSettings.AppSettings["sqlConnectionString"]);

SqlDataAdapter da = new SqlDataAdapter("get_days_byid", cnn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@user_id", SqlDbType.VarChar,20);
da.SelectCommand.Parameters["@user_id"].Value =
HttpContext.Current.User.Identity.Name;
DataTable dt = new DataTable();

try
{
cnn.Open();
da.Fill(dt);

dgDays.DataSource = dt;
dgDays.DataBind();
}
catch(Exception ex)
{
lblMessages.Text = ex.Message;
}
finally
{
cnn.Close();
}
}

protected void dgDays_ItemCommand(Object source, DataGridCommandEventArgs
e)
{
switch (e.CommandName.ToLower())
{
case "edit":
dgDays.EditItemIndex = e.Item.ItemIndex;
break;
case "cancel":
dgDays.EditItemIndex = -1;
break;
}

BindGrid();
}
}

// Class used to add template columns to datagrid
public class DataGridTemplate : ITemplate
{
private string columnName;
private ListItemType itemType;

public DataGridTemplate(ListItemType ItemType, string ColumnName)
{
columnName = ColumnName;
itemType = ItemType;
}

public void InstantiateIn(Control container)
{
DropDownList ddl;
Literal l;
Label lbl;

switch (columnName.ToLower())
{
case "person" :
switch (itemType)
{
case (ListItemType.Header) :
l = new Literal();
l.Text = "Person";
container.Controls.Add(l);
break;
case (ListItemType.Item) :
lbl = new Label();
lbl.DataBinding += new EventHandler(lblPerson_DataBinding);
container.Controls.Add(lbl);
break;
}
break;

case "place" :
switch (itemType)
{
case (ListItemType.Header) :
l = new Literal();
l.Text = "Place";
container.Controls.Add(l);
break;
case (ListItemType.Item) :
lbl = new Label();
lbl.DataBinding += new EventHandler(lblPlace_DataBinding);
container.Controls.Add(lbl);
break;

case (ListItemType.EditItem) :
ddl = new DropDownList();
ddl.DataBinding += new EventHandler(ddlPlace_DataBinding);
container.Controls.Add(ddl);
break;
}
break;

case "part of the day" :
switch (itemType)
{
case (ListItemType.Header) :
l = new Literal();
l.Text = "Part of the Day";
container.Controls.Add(l);
break;
case (ListItemType.Item) :
lbl = new Label();
lbl.DataBinding +=
new EventHandler(lblPartOfTheDay_DataBinding);
container.Controls.Add(lbl);
break;

case (ListItemType.EditItem) :
ddl = new DropDownList();
ddl.DataBinding +=
new EventHandler(ddlPartOfTheDay_DataBinding);
container.Controls.Add(ddl);
break;
}
break;

case "day of the week" :
switch (itemType)
{
case (ListItemType.Header) :
l = new Literal();
l.Text = "Day of the Week";
container.Controls.Add(l);
break;
case (ListItemType.Item) :
lbl = new Label();
lbl.DataBinding +=
new EventHandler(lblDayOfTheWeek_DataBinding);
container.Controls.Add(lbl);
break;

case (ListItemType.EditItem) :
ddl = new DropDownList();
ddl.DataBinding +=
new EventHandler(ddlDayOfTheWeek_DataBinding);
container.Controls.Add(ddl);
break;
}
break;

case "place code" :
switch (itemType)
{
case (ListItemType.Header) :
l = new Literal();
l.Text = "Place Code";
container.Controls.Add(l);
break;
case (ListItemType.Item) :
lbl = new Label();
lbl.ID = "place code";
lbl.DataBinding +=
new EventHandler(lblPlaceCode_DataBinding);
container.Controls.Add(lbl);
break;
}
break;
}
}

protected void lblDayOfTheWeek_DataBinding(Object source, System.EventArgs
e)
{
Label lbl = (Label) source;
DataGridItem container = (DataGridItem) lbl.NamingContainer;
lbl.Text = (string) DataBinder.Eval(container.DataItem, "day of the
week");
}

protected void lblPlace_DataBinding(Object source, System.EventArgs e)
{
Label lbl = (Label) source;
DataGridItem container = (DataGridItem) lbl.NamingContainer;
lbl.Text = (string) DataBinder.Eval(container.DataItem, "place");
}

protected void lblPartOfTheDay_DataBinding(Object source, System.EventArgs
e)
{
Label lbl = (Label) source;
DataGridItem container = (DataGridItem) lbl.NamingContainer;
lbl.Text = (string) DataBinder.Eval(container.DataItem, "part of the
day");
}

protected void ddlDayOfTheWeek_DataBinding(Object source, System.EventArgs
e)
{
DropDownList ddl = (DropDownList) source;
DataGridItem container = (DataGridItem) ddl.NamingContainer;
ddl.Items.Add(new ListItem("Mon","Mon"));
ddl.Items.Add(new ListItem("Tue","Tue"));
ddl.Items.Add(new ListItem("Wed","Wed"));
ddl.Items.Add(new ListItem("Thu","Thu"));
ddl.Items.Add(new ListItem("Fri","Fri"));
ddl.Items.Add(new ListItem("Sat","Sat"));
ddl.Items.Add(new ListItem("Sun","Sun"));
ddl.SelectedValue =
(string) DataBinder.Eval(container.DataItem, "day of the week");
}

protected void ddlPlace_DataBinding(Object source, System.EventArgs e)
{
DropDownList ddl = (DropDownList) source;
DataGridItem container = (DataGridItem) ddl.NamingContainer;
ddl.DataTextField = "place name";
ddl.DataValueField = "place code";
ddl.DataSource = GetLookupTable("get_places");
//
}

protected void ddlPartOfTheDay_DataBinding(Object source, System.EventArgs
e)
{
DropDownList ddl = (DropDownList) source;
DataGridItem container = (DataGridItem) ddl.NamingContainer;
ddl.Items.Add(new ListItem("All Day","All Day"));
ddl.Items.Add(new ListItem("Morning","Morning"));
ddl.Items.Add(new ListItem("Afternoon","Afternoon"));
ddl.SelectedValue =
(string) DataBinder.Eval(container.DataItem, "part of the day");
}

protected void lblPerson_DataBinding(Object source, System.EventArgs e)
{
Label lbl = (Label) source;
DataGridItem container = (DataGridItem) lbl.NamingContainer;
lbl.Text = (string) DataBinder.Eval(container.DataItem, "person");
}

protected void lblPlaceCode_DataBinding(Object source, System.EventArgs e)
{
Label lbl = (Label) source;
DataGridItem container = (DataGridItem) lbl.NamingContainer;
lbl.Text = (string) DataBinder.Eval(container.DataItem, "place code");
}

protected SqlDataReader GetLookupTable(string StoredProcedure)
{
SqlConnection cnn = new SqlConnection
(ConfigurationSettings.AppSettings["sqlConnectionString"]);
SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
cmd.CommandType = CommandType.StoredProcedure;

try
{
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch(Exception ex)
{
return null;
}
}
}
}