Binding a dropdownlist control within a datagrid control

[David Livingston]

I have a page which displays a datagrid bound to a database table. The
datagrid also contains in-line editing functionality. As users click the
edit button and the records displays the EditItemTemplate, I want to display
a dropdownlist for one of the fields. This dropdownlist will contain data
stored in another table (list of offices for example).

I can't figure out how do bind the dropdownlist in the EditItemTemplate of
the datagrid. All the samples I've seen are using hardcoded data which is
not useful. Can anyone help?

Here is my code:
=================================================================
<td width="660" valign="top" align="left">

<asp:datagrid id="MyDataGrid" runat="server"

BackColor="#ffffff"

BorderColor="black"

ShowFooter="false"

CellPadding="3"

CellSpacing="0"

Font-Name="Arial"

Font-Size="10pt"

HeaderStyle-backcolor="#000000"

Headerstyle-forecolor="#ffffff"

Headerstyle-font-bold="true"

Headerstyle-font-Size="8pt"

OnEditCommand="MyDataGrid_Edit"

OnCancelCommand="MyDataGrid_Cancel"

OnUpdateCommand="MyDataGrid_Update"

OnDeleteCommand="MyDataGrid_Delete"

Datakeyfield="revenue_id"

AlternatingItemStyle-BackColor="#99CCFF"

EditItemStyle-BackColor="#FFFFCC"

EditItemStyle-Font-Size="8pt"

AutoGenerateColumns="false">


<property name="Columns">

<asp:BoundColumn HeaderText="Revenue ID" SortField="revenue_id"
ReadOnly="True" DataField="revenue_id" ItemStyle-Wrap="false" visible=False
/>

<asp:TemplateColumn HeaderText="Revenue Date" SortField="revenue_date">

<template name="ItemTemplate">

<asp:Label Width="80" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}")%>' />

</template>

<template name="EditItemTemplate">

<nobr>

<asp:TextBox Width="80" runat="server" id="edit_RevenueDate" Text='<%#
DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}") %>'/>

</template>

</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Client Name" SortField="client_id">

<template name="ItemTemplate">

<asp:Label Width="180" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "client_name") %>' />

</template>

<template name="EditItemTemplate">

<aspropDownList id="DropDownList2" runat="server"

DataTextField="client_name"

DataValueField="client_id"

height="10"

width="200"

/>

</template>

</asp:TemplateColumn>

<asp:TemplateColumn HeaderText="Revenue" SortField="revenue">

<template name="ItemTemplate">

<asp:Label Width="100px" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "revenue", "{0:C}")%>' />

</template>

<template name="EditItemTemplate">

<nobr>

<asp:TextBox Width="100px" runat="server" id="edit_Revenue" Text='<%#
DataBinder.Eval(Container.DataItem, "revenue", "{0:C}") %>'/>

</template>

</asp:TemplateColumn>


<asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
UpdateText="Update" ItemStyle-Wrap="false" />

<asp:ButtonColumn Text="Delete" CommandName="Delete" />

</property>


</asp:datagrid>

<span id="MessageUpdate" MaintainState="false" style="font: arial 11pt;"
runat="server"/>

</td>

============================================================================
=========


Thanks in Advance!

David Livingston

[Hersh]

Specify a dropdownlist instead of a text box in the edit template. Bind it
to a ds at page load.

<aspropDownList DataTextField = "code_display"
DataValueField = "code_value" id="acode_category" runat="server" />

This gives the list. However it does not maintain state

"David Livingston" <david.livingston@marchFIRST.com> wrote:
>I have a page which displays a datagrid bound to a database table. The
>datagrid also contains in-line editing functionality. As users click the
>edit button and the records displays the EditItemTemplate, I want to display
>a dropdownlist for one of the fields. This dropdownlist will contain data
>stored in another table (list of offices for example).
>
>I can't figure out how do bind the dropdownlist in the EditItemTemplate
of
>the datagrid. All the samples I've seen are using hardcoded data which
is
>not useful. Can anyone help?
>
>Here is my code:
>=================================================================
><td width="660" valign="top" align="left">
>
><asp:datagrid id="MyDataGrid" runat="server"
>
>BackColor="#ffffff"
>
>BorderColor="black"
>
>ShowFooter="false"
>
>CellPadding="3"
>
>CellSpacing="0"
>
>Font-Name="Arial"
>
>Font-Size="10pt"
>
>HeaderStyle-backcolor="#000000"
>
>Headerstyle-forecolor="#ffffff"
>
>Headerstyle-font-bold="true"
>
>Headerstyle-font-Size="8pt"
>
>OnEditCommand="MyDataGrid_Edit"
>
>OnCancelCommand="MyDataGrid_Cancel"
>
>OnUpdateCommand="MyDataGrid_Update"
>
>OnDeleteCommand="MyDataGrid_Delete"
>
>Datakeyfield="revenue_id"
>
>AlternatingItemStyle-BackColor="#99CCFF"
>
>EditItemStyle-BackColor="#FFFFCC"
>
>EditItemStyle-Font-Size="8pt"
>
>AutoGenerateColumns="false">
>
>
><property name="Columns">
>
><asp:BoundColumn HeaderText="Revenue ID" SortField="revenue_id"
>ReadOnly="True" DataField="revenue_id" ItemStyle-Wrap="false" visible=False
>/>
>
><asp:TemplateColumn HeaderText="Revenue Date" SortField="revenue_date">
>
><template name="ItemTemplate">
>
><asp:Label Width="80" runat="server" Text='<%#
>DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}")%>' />
>
></template>
>
><template name="EditItemTemplate">
>
><nobr>
>
><asp:TextBox Width="80" runat="server" id="edit_RevenueDate" Text='<%#
>DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}") %>'/>
>
></template>
>
></asp:TemplateColumn>
>
><asp:TemplateColumn HeaderText="Client Name" SortField="client_id">
>
><template name="ItemTemplate">
>
><asp:Label Width="180" runat="server" Text='<%#
>DataBinder.Eval(Container.DataItem, "client_name") %>' />
>
></template>
>
><template name="EditItemTemplate">
>
><aspropDownList id="DropDownList2" runat="server"
>
>DataTextField="client_name"
>
>DataValueField="client_id"
>
>height="10"
>
>width="200"
>
>/>
>
></template>
>
></asp:TemplateColumn>
>
><asp:TemplateColumn HeaderText="Revenue" SortField="revenue">
>
><template name="ItemTemplate">
>
><asp:Label Width="100px" runat="server" Text='<%#
>DataBinder.Eval(Container.DataItem, "revenue", "{0:C}")%>' />
>
></template>
>
><template name="EditItemTemplate">
>
><nobr>
>
><asp:TextBox Width="100px" runat="server" id="edit_Revenue" Text='<%#
>DataBinder.Eval(Container.DataItem, "revenue", "{0:C}") %>'/>
>
></template>
>
></asp:TemplateColumn>
>
>
><asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
>UpdateText="Update" ItemStyle-Wrap="false" />
>
><asp:ButtonColumn Text="Delete" CommandName="Delete" />
>
></property>
>
>
></asp:datagrid>
>
><span id="MessageUpdate" MaintainState="false" style="font: arial 11pt;"
>runat="server"/>
>
></td>
>
>============================================================================
>=========
>
>
>Thanks in Advance!
>
>David Livingston
>
>

[Hersh Bhasin]

Question
-----------
> Thanks for the help! Now that I'm binding my data, how do I set the
> SelectedIndex to be the current item in the list?
> For example: if the record I'm editing contains the client name "ACME",
how
> do I set my dropdown list to display "ACME" as the current selected item?
> **************************************************************************************

Answer
---------
Thats what I ment.. it does not handle state i.e it does not go to the correct
record.

I hit my head trying to figure this one out but gave up due to lack of time.
There is an example in quickstart which I copy below.
Here the list is bound to a hash table and some tricky logic applied to the
state field . Namely a function is called like this
aspropDownList runat="server" SelectedIndex='<%# GetStateIndex(Container.DataItem("state"))
%>' id="edit_State">

I did not have time to develop this for a dataset. I had also posted this
problem earlier on the newsgroup but have no answer so far.
Hence I am posting these pointers on the group and hope someone comes up
with the answer and shares it with us

Hersh Bhasin


*****************************************************************8

Here is the example from quickstart . This is

Section : Server Side Data Access
Example : DataGrid8.aspx



BoundColumns aren't the only controls we can set in the DataGrid's Columns
collection. We can also
specify a TemplateColumn, which gives us complete control over the contents
of the column. The template
is just arbitrary content, so we can render anything we like, including server
controls, inside the
DataGrid's columns. In this next sample we use the TemplateColumn control
to render the "state" column
as a DropDownList and the "contract" column as a checkbox HtmlControl. The
ASP.NET databinding syntax is
used to output the data field value within the template. Note that there
is a bit of tricky logic to
make the DropDownList and checkbox reflect the state of the data inside the
row


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQL" %>

<html>

<script language="VB" runat="server">

Dim MyConnection As SQLConnection
Public StateIndex As Hashtable

Sub Page_Load(Src As Object, E As EventArgs)

MyConnection = New SQLConnection("server=localhost;uid=sa;pwd=;database=pubs")

If Not (IsPostBack)
BindGrid()
End If

StateIndex = New Hashtable()
StateIndex("CA") = 0
StateIndex("IN") = 1
StateIndex("KS") = 2
StateIndex("MD") = 3
StateIndex("MI") = 4
StateIndex("OR") = 5
StateIndex("TN") = 6
StateIndex("UT") = 7
End Sub

Public Function GetStateIndex(StateName As String) As Integer

If StateIndex(stateName) <> Nothing
Return CInt(StateIndex(stateName))
Else
Return 0
End If
End Function

Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
BindGrid()
End Sub

Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

MyDataGrid.EditItemIndex = -1
BindGrid()
End Sub

Sub MyDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

Dim DS As DataSet
Dim MyCommand As SQLCommand

Dim UpdateCmd As String = "UPDATE Authors SET au_id = @Id, au_lname
= @LName, au_fname = @FName, phone = " _
& " @Phone, address = @Address, city = @City, state = @State,
zip = @Zip, contract = @Contract where au_id = @Id"

MyCommand = New SQLCommand(UpdateCmd, MyConnection)

MyCommand.Parameters.Add(New SQLParameter("@Id", SQLDataType.VarChar,
11))
MyCommand.Parameters.Add(New SQLParameter("@LName", SQLDataType.VarChar,
40))
MyCommand.Parameters.Add(New SQLParameter("@FName", SQLDataType.VarChar,
20))
MyCommand.Parameters.Add(New SQLParameter("@Phone", SQLDataType.Char,
12))
MyCommand.Parameters.Add(New SQLParameter("@Address", SQLDataType.VarChar,
40))
MyCommand.Parameters.Add(New SQLParameter("@City", SQLDataType.VarChar,
20))
MyCommand.Parameters.Add(New SQLParameter("@State", SQLDataType.Char,
2))
MyCommand.Parameters.Add(New SQLParameter("@Zip", SQLDataType.Char,
5))
MyCommand.Parameters.Add(New SQLParameter("@Contract", SQLDataType.VarChar,1))

MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))

Dim Cols As String() = {"LName","FName","Phone","Address","City","Zip"}

Dim I As Integer
For I = 0 To 5

Dim CurrentTextBox As TextBox
CurrentTextBox = E.Item.FindControl("edit_" & Cols(I))
Dim ColValue As String = CurrentTextBox.Text

' Check for null values in required fields
If I<3 And ColValue = ""

Message.InnerHtml = "ERROR: Null values not allowed for Name
or Phone"
Message.Style("color") = "red"
Return
End If

MyCommand.Parameters("@" & Cols(I)).Value = ColValue
Next

Dim StateDropDownList As DropDownList
StateDropDownList = E.Item.FindControl("edit_State")
MyCommand.Parameters("@State").Value = StateDropDownList.SelectedItem.ToString()

Dim ContractCheckBox As CheckBox
ContractCheckBox = E.Item.FindControl("edit_Contract")

If ContractCheckBox.Checked = true
MyCommand.Parameters("@Contract").Value = "1"
Else
MyCommand.Parameters("@Contract").Value = "0"
End If

MyCommand.ActiveConnection.Open()

Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd
MyDataGrid.EditItemIndex = -1
Catch Exp As SQLException
If Exp.Number = 2627
Message.InnerHtml = "ERROR: A record already exists with
the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please
ensure the fields are correctly filled out"
End If
Message.Style("color") = "red"
End Try

MyCommand.ActiveConnection.Close()

BindGrid()
End Sub

Sub BindGrid()

Dim DS As DataSet
Dim MyCommand As SQLDataSetCommand
MyCommand = new SQLDataSetCommand("select * from Authors", MyConnection)

DS = new DataSet()
MyCommand.FillDataSet(DS, "Authors")

MyDataGrid.DataSource=DS.Tables("Authors").DefaultView
MyDataGrid.DataBind()
End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

<h3><font face="Verdana">Updating a Row of Data w/ Templated Column</font></h3>

<span id="Message" MaintainState="false" style="font: arial 11pt;" runat="server"/><p>

<ASPataGrid id="MyDataGrid" runat="server"
Width="800"
BackColor="#ccccff"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
OnEditCommand="MyDataGrid_Edit"
OnCancelCommand="MyDataGrid_Cancel"
OnUpdateCommand="MyDataGrid_Update"
DataKeyField="au_id"
AutoGenerateColumns="false"
>

<property name="Columns">
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update"
ItemStyle-Wrap="false"/>
<asp:BoundColumn HeaderText="au_id" SortField="au_id" ReadOnly="True"
DataField="au_id" ItemStyle-Wrap="false"/>
<asp:TemplateColumn HeaderText="au_lname" SortField="au_lname">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"au_lname") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_LName" Text='<%# DataBinder.Eval(Container.DataItem,
"au_lname") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="au_fname" SortField="au_fname">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"au_fname") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_FName" Text='<%# DataBinder.Eval(Container.DataItem,
"au_fname") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="phone" SortField="phone">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"phone") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_Phone" Text='<%# DataBinder.Eval(Container.DataItem,
"phone") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="address" SortField="address">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"address") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_Address" Text='<%# DataBinder.Eval(Container.DataItem,
"address") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="city" SortField="city">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"city") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_City" Text='<%# DataBinder.Eval(Container.DataItem,
"city") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="state" SortField="state">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"state") %>'/>
</template>
<template name="EditItemTemplate">
<aspropDownList runat="server" SelectedIndex='<%# GetStateIndex(Container.DataItem("state"))
%>' id="edit_State">
<asp:ListItem>CA</asp:ListItem>
<asp:ListItem>IN</asp:ListItem>
<asp:ListItem>KS</asp:ListItem>
<asp:ListItem>MD</asp:ListItem>
<asp:ListItem>MI</asp:ListItem>
<asp:ListItem>OR</asp:ListItem>
<asp:ListItem>TN</asp:ListItem>
<asp:ListItem>UT</asp:ListItem>
</aspropDownList>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="zip" SortField="zip">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"zip") %>'/>
</template>
<template name="EditItemTemplate">
<asp:TextBox runat="server" id="edit_Zip" Text='<%# DataBinder.Eval(Container.DataItem,
"zip") %>'/>
</template>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="contract" SortField="contract">
<template name="ItemTemplate">
<asp:Label runat="server" Text='<%# DataBinder.Eval(Container.DataItem,
"contract", "{0}") %>'/>
</template>
<template name="EditItemTemplate">
<asp:CheckBox runat="server" id="edit_Contract" Checked='<%#
DataBinder.Eval(Container.DataItem, "contract") %>'/>
</template>
</asp:TemplateColumn>
</property>

</ASPataGrid>

</form>

</body>
</html>




"Hersh" <hershb@hotmail.com> wrote:
>
>
>Specify a dropdownlist instead of a text box in the edit template. Bind
it
>to a ds at page load.
>
><aspropDownList DataTextField = "code_display"
> DataValueField = "code_value" id="acode_category" runat="server" />
>
>This gives the list. However it does not maintain state
>
>"David Livingston" <david.livingston@marchFIRST.com> wrote:
>>I have a page which displays a datagrid bound to a database table. The
>>datagrid also contains in-line editing functionality. As users click the
>>edit button and the records displays the EditItemTemplate, I want to display
>>a dropdownlist for one of the fields. This dropdownlist will contain data
>>stored in another table (list of offices for example).
>>
>>I can't figure out how do bind the dropdownlist in the EditItemTemplate
>of
>>the datagrid. All the samples I've seen are using hardcoded data which
>is
>>not useful. Can anyone help?
>>
>>Here is my code:
>>=================================================================
>><td width="660" valign="top" align="left">
>>
>><asp:datagrid id="MyDataGrid" runat="server"
>>
>>BackColor="#ffffff"
>>
>>BorderColor="black"
>>
>>ShowFooter="false"
>>
>>CellPadding="3"
>>
>>CellSpacing="0"
>>
>>Font-Name="Arial"
>>
>>Font-Size="10pt"
>>
>>HeaderStyle-backcolor="#000000"
>>
>>Headerstyle-forecolor="#ffffff"
>>
>>Headerstyle-font-bold="true"
>>
>>Headerstyle-font-Size="8pt"
>>
>>OnEditCommand="MyDataGrid_Edit"
>>
>>OnCancelCommand="MyDataGrid_Cancel"
>>
>>OnUpdateCommand="MyDataGrid_Update"
>>
>>OnDeleteCommand="MyDataGrid_Delete"
>>
>>Datakeyfield="revenue_id"
>>
>>AlternatingItemStyle-BackColor="#99CCFF"
>>
>>EditItemStyle-BackColor="#FFFFCC"
>>
>>EditItemStyle-Font-Size="8pt"
>>
>>AutoGenerateColumns="false">
>>
>>
>><property name="Columns">
>>
>><asp:BoundColumn HeaderText="Revenue ID" SortField="revenue_id"
>>ReadOnly="True" DataField="revenue_id" ItemStyle-Wrap="false" visible=False
>>/>
>>
>><asp:TemplateColumn HeaderText="Revenue Date" SortField="revenue_date">
>>
>><template name="ItemTemplate">
>>
>><asp:Label Width="80" runat="server" Text='<%#
>>DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}")%>' />
>>
>></template>
>>
>><template name="EditItemTemplate">
>>
>><nobr>
>>
>><asp:TextBox Width="80" runat="server" id="edit_RevenueDate" Text='<%#
>>DataBinder.Eval(Container.DataItem, "revenue_date", "{0:d}") %>'/>
>>
>></template>
>>
>></asp:TemplateColumn>
>>
>><asp:TemplateColumn HeaderText="Client Name" SortField="client_id">
>>
>><template name="ItemTemplate">
>>
>><asp:Label Width="180" runat="server" Text='<%#
>>DataBinder.Eval(Container.DataItem, "client_name") %>' />
>>
>></template>
>>
>><template name="EditItemTemplate">
>>
>><aspropDownList id="DropDownList2" runat="server"
>>
>>DataTextField="client_name"
>>
>>DataValueField="client_id"
>>
>>height="10"
>>
>>width="200"
>>
>>/>
>>
>></template>
>>
>></asp:TemplateColumn>
>>
>><asp:TemplateColumn HeaderText="Revenue" SortField="revenue">
>>
>><template name="ItemTemplate">
>>
>><asp:Label Width="100px" runat="server" Text='<%#
>>DataBinder.Eval(Container.DataItem, "revenue", "{0:C}")%>' />
>>
>></template>
>>
>><template name="EditItemTemplate">
>>
>><nobr>
>>
>><asp:TextBox Width="100px" runat="server" id="edit_Revenue" Text='<%#
>>DataBinder.Eval(Container.DataItem, "revenue", "{0:C}") %>'/>
>>
>></template>
>>
>></asp:TemplateColumn>
>>
>>
>><asp:EditCommandColumn EditText="Edit" CancelText="Cancel"
>>UpdateText="Update" ItemStyle-Wrap="false" />
>>
>><asp:ButtonColumn Text="Delete" CommandName="Delete" />
>>
>></property>
>>
>>
>></asp:datagrid>
>>
>><span id="MessageUpdate" MaintainState="false" style="font: arial 11pt;"
>>runat="server"/>
>>
>></td>
>>
>>============================================================================
>>=========
>>
>>
>>Thanks in Advance!
>>
>>David Livingston
>>
>>
>