How to use a dropdownlist in a DataGrid

Step 1: Display the Data in the Datagrid
Initially the Datagrid is populated with the records in the db
The field Discontinued is of data type bit in the db i.e 0/1.
To display it as yes/no in the Datagrid a helper function ShowVal(…) is called

Step 2: To Edit the Datagrid ,
When the edit button is clicked it should display field Discontinued as Yes/No

• To update the record the user should get a choice to Select Yes/No using dropdownlist
• By default the dropdownlist should be set to the value in the database
  • So the DataSource property of the dropdownlist is set to BindTheDiscontinued()
  • and OnPreRender property does the task of setting the value from the db to the dropdownlist

<asp:DataGrid id='DataGrid1' AutoGenerateColumns='False' DataKeyField='ProductID' OnUpdateCommand='DataGrid1_Update'
	OnEditCommand='DataGrid1_Edit' OnCancelCommand='DataGrid1_Cancel' runat='server'>
<Columns>
<asp:TemplateColumn HeaderText='Discontinued'>
	<ItemTemplate>
	<asp:Label ID='lblDiscontinued' Text=’<%#ShowVal(Convert.ToBoolean( DataBinder.Eval(Container.DataItem, 'Discontinued').ToString()) )%>’ Runat='server' />
	</ItemTemplate>

	<EditItemTemplate>
	<asp:Label runat='server' id='lblProductID' Visible='False' Text=’<%# DataBinder.Eval(Container.DataItem, 'ProductId') %>’/>
	<asp:Label ID='lblEditDiscontinued' Text=’<%#ShowVal(Convert.ToBoolean(DataBinder.Eval(Container.DataItem, 'Discontinued').ToString() ))%>’ Runat='server' />
	<asp:DropDownList id='ddlDiscontinued' DataSource='<%# BindTheDiscontinued() %>' OnPreRender='SetDropDownIndex' DataTextField='Discontinued' DataValueField='Discontinued' runat='server' />
	</EditItemTemplate>
</asp:TemplateColumn>

<asp:EditCommandColumn EditText='Edit' CancelText='Cancel' UpdateText='Update' ItemStyle-Width='100px' HeaderText='Commands' />
</Columns>
</asp:DataGrid>

Code Behind
VB.NET

Dim strDiscontinued As String
Dim obj As GetData
Dim strSql As String
Dim strConn As String
Dim ds As DataSet
Dim dr As SqlDataReader

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
’ Put user code to initialize the page here
strConn = 'server=localhost;uid=sa;pwd=;database=northwind'
If Not Page.IsPostBack Then
	BindGrid()
End If
End Sub ’Page_Load

’To Bind the DataGrid
Sub BindGrid()
	obj = New GetData
	strSql = 'Select productid, discontinued from Products'
	ds = obj.GetDataFromTable(strSql, strConn)
	DataGrid1.DataSource = ds
	DataGrid1.DataBind()
End Sub ’BindGrid

’To display Yes/No for True/False
Protected Function ShowVal(ByVal blnval As Boolean) As String
	If blnval = True Then
		Return 'Yes'
	Else
		Return 'No'
	End If
End Function ’ShowVal

’Bind the Data to the dropdownlist in the EditTemplate
Protected Function BindTheDiscontinued() As SqlDataReader
	obj = New GetData
	strSql = 'SELECT distinct ’Discontinued’ ='
	strSql += ' CASE '
	strSql += ' WHEN Discontinued = 1 Then ’Yes’'
	strSql += ' ELSE ’No’'
	strSql += ' END '
	strSql += ' From Products '
	dr = obj.GetSingleDataUsingReader(strSql, strConn)
	Return dr
End Function ’BindTheDiscontinued

’Set the Text of the Dropdownlist to the field value in Database
Protected Sub SetDropDownIndex(ByVal sender As [Object], ByVal e As System.EventArgs)
	Dim ed As DropDownList
	ed = CType(sender, DropDownList)
	ed.SelectedIndex = ed.Items.IndexOf(ed.Items.FindByText(strDiscontinued))
End Sub ’SetDropDownIndex

’For Edit Update Cancel
Public Sub DataGrid1_Edit(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	strDiscontinued = CType(e.Item.FindControl('lblDiscontinued'), Label).Text
	DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex)
	BindGrid()
End Sub ’DataGrid1_Edit

Public Sub DataGrid1_Update(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	Dim TempList As DropDownList
	Dim TempValue As [String]
	TempList = CType(e.Item.FindControl('ddlDiscontinued'), DropDownList)
	TempValue = TempList.SelectedItem.Value
	’Place update code here
	Response.Write(TempValue)
	DataGrid1.EditItemIndex = -1
	BindGrid()
End Sub ’DataGrid1_Update

Public Sub DataGrid1_Cancel(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	DataGrid1.EditItemIndex = -1
	BindGrid()
End Sub ’DataGrid1_Cancel

’Functions used in Class GetData.cs
Dim mycn As SqlConnection
Dim myda As SqlDataAdapter
Dim mycmd As SqlCommand
Dim ds As DataSet
Dim strConn As String
Dim myReader As SqlDataReader

Public Function GetDataFromTable(ByVal strSQL As String, ByVal strConnString As String) As DataSet
Try
	strConn = strConnString
	mycn = New SqlConnection(strConn)
	myda = New SqlDataAdapter(strSQL, mycn)
	ds = New DataSet
	myda.Fill(ds, 'Table')
	Return ds
Catch ex As Exception
	Throw New Exception(ex.Message.ToString())
Finally
	mycn.Close()
End Try
End Function ’GetDataFromTable

Public Function GetSingleDataUsingReader(ByVal strSQL As String, ByVal strConnString As String) As SqlDataReader
Try
	strConn = strConnString
	mycn = New SqlConnection(strConn)
	mycmd = New SqlCommand(strSQL, mycn)
	mycn.Open()
	myReader = mycmd.ExecuteReader(CommandBehavior.CloseConnection)
	Return myReader
Catch ex As Exception
	Throw New Exception(ex.Message.ToString())
Finally
	End Try ’mycn.Close ();
End Function ’GetSingleDataUsingReader 

C#

string strDiscontinued;
GetData obj;
string strSql;
string strConn;
DataSet ds;
SqlDataReader dr;
private void Page_Load(object sender, System.EventArgs e)
{
 // Put user code to initialize the page here
strConn ='server=localhost;uid=sa;pwd=;database=northwind';
if (!Page.IsPostBack )
{
BindGrid();
}
}


//To Bind the DataGrid
void BindGrid()
{
	obj=new GetData ();
	strSql = 'Select productid, discontinued from Products';
	ds=obj.GetDataFromTable (strSql ,strConn);
	DataGrid1.DataSource =ds;
	DataGrid1.DataBind (); 
}

//To display Yes/No for True/False
protected string ShowVal(bool blnval)
{
	if (blnval==true)
	{
		return 'Yes';
	} 
	else
	{
		return 'No';
	}
}

//Bind the Data to the dropdownlist in the EditTemplate
protected SqlDataReader BindTheDiscontinued()
{
	obj=new GetData ();
	strSql ='SELECT distinct ’Discontinued’ =' ;
	strSql+=' CASE ';
	strSql+=' WHEN Discontinued = 1 Then ’Yes’' ;
	strSql+=' ELSE ’No’' ;
	strSql+=' END ' ;
	strSql+=' From Products ';
	dr=obj.GetSingleDataUsingReader (strSql ,strConn);
	return dr;
}


//Set the Text of the Dropdownlist to the field value in Database

protected void SetDropDownIndex(Object sender ,System.EventArgs e   )
{
	DropDownList ed ; 
	ed = (DropDownList) sender;
	ed.SelectedIndex = ed.Items.IndexOf(ed.Items.FindByText(strDiscontinued));
}


//For Edit Update Cancel
public void DataGrid1_Edit(Object sender, DataGridCommandEventArgs e)
{
	strDiscontinued = ((Label )e.Item.FindControl('lblDiscontinued')).Text;
	DataGrid1.EditItemIndex = (int)e.Item.ItemIndex;    
	BindGrid(); 
}

public void DataGrid1_Update(Object sender, DataGridCommandEventArgs e)
{
	DropDownList TempList ; 
	String TempValue ; 
	TempList = (DropDownList) e.Item.FindControl('ddlDiscontinued');
	TempValue = TempList.SelectedItem.Value;
	 //Place update code here
	Response.Write (TempValue);
	DataGrid1.EditItemIndex = -1;
	BindGrid();
}
public void DataGrid1_Cancel(Object sender, DataGridCommandEventArgs e)
{
	DataGrid1.EditItemIndex = -1;
	BindGrid();
}



//Functions used in Class GetData.cs

SqlConnection mycn;
SqlDataAdapter myda;
SqlCommand mycmd;
DataSet ds;
String strConn;
SqlDataReader myReader;
public DataSet GetDataFromTable(string strSQL ,string strConnString)
{
try
{
	strConn=strConnString;
	mycn = new SqlConnection(strConn);
	myda = new SqlDataAdapter (strSQL, mycn);
	ds= new DataSet ();
	myda.Fill (ds,'Table');
	return ds;
}
catch(Exception ex)
{
	throw new Exception (ex.Message.ToString ());
}
finally
{
	mycn.Close ();
}
}

public SqlDataReader GetSingleDataUsingReader(string strSQL ,string strConnString)
{
try
{
	strConn=strConnString;
	mycn = new SqlConnection(strConn);
	mycmd = new SqlCommand  (strSQL, mycn);
	mycn.Open ();
	myReader=mycmd.ExecuteReader(CommandBehavior.CloseConnection ); 
	return myReader;
}
catch(Exception ex)
{
	throw new Exception (ex.Message.ToString ());
}
finally
{
	//mycn.Close ();
}
}