Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
gridview1.DataSource = getdataset().Tables(0)
gridview1.DataBind()
End Sub
Private Function getdataset() As Data.DataSet
Dim connectionstring As String = "Data Source=localhost;Initial Catalog=northwind;User ID=sa;password="
Dim query As String = "select p.categoryid,p.productid, p.productname,c.categoryid,c.categoryname from products p,categories c where p.categoryid=c.categoryid and c.categoryid<3"
Dim myconnection As New SqlConnection(connectionstring)
Dim ad As New SqlDataAdapter(query, myconnection)
Dim ds As New Data.DataSet()
ad.Fill(ds)
Return ds
End Function
Protected Sub gridview1_rowdatabound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gridview1.RowDataBound
Dim mytable As New Data.DataTable()
Dim productidcolumn As New Data.DataColumn("productid")
Dim productnamecolumn As New Data.DataColumn("productname")
mytable.Columns.Add(productidcolumn)
mytable.Columns.Add(productnamecolumn)
Dim ds As New Data.DataSet()
ds = getdataset()
Dim categoryid As Integer = 0
Dim expression As String = String.Empty
If e.Row.RowType = DataControlRowType.DataRow Then
categoryid = Int32.Parse(e.Row.Cells(0).Text)
expression = "categoryid = " & categoryid
Dim ddl As DropDownList = CType(e.Row.FindControl("dropdownlist1"), DropDownList)
Dim rows As Data.DataRow() = ds.Tables(0).Select(expression)
Dim row As Data.DataRow
For Each row In rows
Dim newrow As Data.DataRow = mytable.NewRow()
newrow("productid") = row("productid")
newrow("productname") = row("productname")
mytable.Rows.Add(newrow)
Next row
ddl.DataSource = mytable
ddl.DataTextField = "productname"
ddl.DataValueField = "productid"
ddl.DataBind()
End If
End Sub