ASP.Net GridView DropDownList DataBinding using VB.Net

In the previous tutorial ASP.Net GridView Template with DropDownList we discussed about the HTML code for customizing the GridView Template Field columns to display the order_id and nested DropDownList control inside the second TemplateField column. Here we will complete the next step of DataBinding using the DataSource property of GridView control. We will use VB.Net for each loop over the rows of GridView control after databinding. VB.Net for each loop will allow using the FindControl method to get the reference of DropDownList control placed inside the ItemTemplate of GridView Control. FindControl method helps to pass the reference of encapsulated server control such as DropDownList control (that we will learn in this tutorial) to the object of the related control class.

VB.Net Code for ASP.Net GridView DataBinding

' string variable to store the connection string
' defined in appsettings section of web.config file.
Dim connStr As String = ConfigurationManager.AppSettings("ConnectionString").ToString()

' object created for SqlConnection Class.
Dim mySQLconnection As New SqlConnection(connStr)

' if condition that can be used to check the sql connection
' whether it is already open or not.
If mySQLconnection.State = ConnectionState.Closed Then
    mySQLconnection.Open()
End If

Dim mySqlCommand As New SqlCommand("select * from tbl_orders", mySQLconnection)
Dim mySqlAdapter As New SqlDataAdapter(mySqlCommand)
Dim myDataSet As New DataSet()
mySqlAdapter.Fill(myDataSet)

GridView1.DataSource = myDataSet
GridView1.DataBind()

''******* code for dropdownlist databinding *******''

' if condition that can be used to check the sql connection
' if it is open then close it.
If mySQLconnection.State = ConnectionState.Open Then
    mySQLconnection.Close()
End If

You can use the above code to bind the data stored in the SQL Database tbl_orders table. Source code of this example have complete VB.net code for database connectivity as well as functionality to display the data in GridView and selectedValue of DropDownList control placed in it.  

VB.net Code for ASP.Net GridView DropDownList DataBinding

mySqlCommand = New SqlCommand("select * from tbl_order_status", mySQLconnection)
mySqlAdapter = New SqlDataAdapter(mySqlCommand)
myDataSet = New DataSet()
mySqlAdapter.Fill(myDataSet)

Dim drdList As DropDownList

For Each rowItem As GridViewRow In GridView1.Rows
    drdList = CType(rowItem.Cells(1).FindControl("DropDownList1"), DropDownList)

    drdList.DataSource = myDataSet
    drdList.DataTextField = "order_status_name"
    drdList.DataValueField = "order_status_id"
    drdList.DataBind()
Next

Note: Place the above code under the ''******* code for dropdownlist databinding *******'' commented line of gridview databinding vb.net code discussed in the first step. 

In the above sample code, note that the objects of SqlCommand, SqlDataAdapter and DataSet has been reused for retrieving the data stored in tbl_order_status table. DataSet object has been filled outside the VB.Net for each loop over the GridView Rows coz data is to be retrieved once and reused each time to bind it with nested DropDownList control placed inside the GridViewRows. Just below the Fill method code of SQLDataAdapter you can see the object created for DropDownList to access the properties of this control. Inside VB.net code block of "for each loop" with first code line having FindControl method to find the DropDownList control, 1 is passed as value to the Cells collection object of TableCellCollection coz DropDownList was placed inside the second Template column of column GridView i.e. at 1st index. VB.net for each loop has been used to find the DropDownList in each GridViewRow generated after DataBinding.

In this tutorial we learnt how to bind the data with the dropdownlist control placed inside the ASP.net gridview control using vb.net code. Now continue to the next tutorial: Setting ASP.Net Gridview Dropdownlist SelectedValue using VB.net to learn how to set the selected value of dropdownlist nested inside the gridview control according to the value retrieved from the SQL database.