Repopulate a recordset from a Dropdown

I've got a dropdownlist on my page that is loaded from a table with names and values.  What I want to do, is when a user selects an item from the drop down, I want the recordset to repopulate and show results in a related table = to that of the dropdown.  I'm pasting my code in as help in understanding the question.

From webform1.aspx.vb -----------------
ublic Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DataSet11 = New support.micro_gen.net.DataSet1
        Me.SqlDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
        Me.DataSet21 = New support.micro_gen.net.DataSet2
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DataSet21, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=""*****"";packet size=4096;user id=*********;data source=""mgse" & _
        "rver.micro-gen.net\websql"";persist security info=True;initial catalog=MicroGenSu" & _
        "pport;password=************"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tbl_cemetary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("PrimaryKey", "PrimaryKey"), New System.Data.Common.DataColumnMapping("Name", "Name"), New System.Data.Common.DataColumnMapping("Address", "Address"), New System.Data.Common.DataColumnMapping("Township", "Township"), New System.Data.Common.DataColumnMapping("County", "County"), New System.Data.Common.DataColumnMapping("State", "State"), New System.Data.Common.DataColumnMapping("ZipCode", "ZipCode"), New System.Data.Common.DataColumnMapping("GPSNorth", "GPSNorth"), New System.Data.Common.DataColumnMapping("GPSWest", "GPSWest"), New System.Data.Common.DataColumnMapping("GPSElevation", "GPSElevation"), New System.Data.Common.DataColumnMapping("CemURL", "CemURL")})})
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT PrimaryKey, Name, Address, Township, County, State, ZipCode, GPSNorth, GPS" & _
        "West, GPSElevation, CemURL FROM tbl_cemetary ORDER BY Name"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlDataAdapter2
        '
        Me.SqlDataAdapter2.SelectCommand = Me.SqlSelectCommand2
        Me.SqlDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tbl_cemdata", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("LastName", "LastName"), New System.Data.Common.DataColumnMapping("FirstName", "FirstName"), New System.Data.Common.DataColumnMapping("DoB", "DoB"), New System.Data.Common.DataColumnMapping("DoD", "DoD"), New System.Data.Common.DataColumnMapping("Comment", "Comment"), New System.Data.Common.DataColumnMapping("PrimaryKey", "PrimaryKey")})})
        '
        'SqlSelectCommand2
        '
        'This is where my Recordset should select the value of the dropdown list.
        Me.SqlSelectCommand2.CommandText = "SELECT LastName, FirstName, DoB, DoD, Comment, PrimaryKey FROM tbl_cemdata WHERE " & _
        "(PrimaryKey = 7)"

        Me.SqlSelectCommand2.Connection = Me.SqlConnection1
        '
        'DataSet21
        '
        Me.DataSet21.DataSetName = "DataSet2"
        Me.DataSet21.Locale = New System.Globalization.CultureInfo("en-US")
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DataSet21, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents DataSet11 As support.micro_gen.net.DataSet1
    Protected WithEvents DropDownList1 As System.Web.UI.WebControls.DropDownList
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDataAdapter2 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
    Protected WithEvents DataSet21 As support.micro_gen.net.DataSet2
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    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
        If Not IsPostBack Then
            SqlDataAdapter1.Fill(DataSet11)
            DropDownList1.DataBind()
        End If
        SqlDataAdapter2.Fill(DataSet21)
        DataGrid1.DataBind()

    End Sub

End Class
--------------------------------------------

From webform1.aspx -------------------------
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="support.micro_gen.net.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>WebForm1</title>
            <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
            <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
            <meta name="vs_defaultClientScript" content="JavaScript">
            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <asp:DropDownList id=DropDownList1 style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 16px" runat="server" Width="192px" DataSource="<%# DataSet11 %>" DataValueField="PrimaryKey" DataTextField="Name" AutoPostBack="True">
                  </asp:DropDownList>
                  <asp:DataGrid id=DataGrid1 style="Z-INDEX: 102; LEFT: 16px; POSITION: absolute; TOP: 64px" runat="server" Width="832px" DataSource="<%# DataSet21 %>" BorderColor="#CCCCCC" BorderStyle="None" BackColor="White" CellPadding="3" BorderWidth="1px" DataKeyField="PrimaryKey" AllowSorting="True" Font-Size="Small" Height="96px">
                        <FooterStyle ForeColor="#000066" BackColor="White"></FooterStyle>
                        <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#669999"></SelectedItemStyle>
                        <ItemStyle Font-Size="X-Small" ForeColor="#000066"></ItemStyle>
                        <HeaderStyle Font-Size="Smaller" Font-Bold="True" ForeColor="White" BackColor="#006699"></HeaderStyle>
                        <Columns>
                              <asp:BoundColumn DataField="LastName" HeaderText="Last Name"></asp:BoundColumn>
                              <asp:BoundColumn DataField="FirstName" HeaderText="First Name"></asp:BoundColumn>
                              <asp:BoundColumn DataField="DoB" HeaderText="Date of Birth"></asp:BoundColumn>
                              <asp:BoundColumn DataField="DoD" HeaderText="Date of Death"></asp:BoundColumn>
                              <asp:BoundColumn DataField="Comment" HeaderText="Comments"></asp:BoundColumn>
                        </Columns>
                        <PagerStyle HorizontalAlign="Left" ForeColor="#000066" BackColor="White" Mode="NumericPages"></PagerStyle>
                  </asp:DataGrid>
            </form>
      </body>
</HTML>
------------------------------------

I'm using Visual studio.net 7.1
LVL 1
freakyunoAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
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
        If Not IsPostBack Then
            SqlDataAdapter1.Fill(DataSet11)
            DropDownList1.DataBind()
       
        BindData()
End If
    End Sub


Function FilterDataSet(ByVal ds As DataSet, ByVal filter As String) As DataTable
        Dim newDS As DataSet = ds.Clone()
        Dim r As DataRow
        If filter<> "" Then
            Dim MyRows As DataRow() = ds.Tables(0).Select("YOURDBField='" & filter & "'")

            For Each r In MyRows
                ''Calling ImportRow preserves the existing DataRowState,
                ''along with other values in the row
                newDS.Tables(0).ImportRow(r)
            Next
            FilterDataSet = newDS.Tables(0)
        Else
            FilterDataSet = ds.Tables(0)
        End If


    End Function


 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
        If Not IsPostBack Then
            SqlDataAdapter1.Fill(DataSet11)
            DropDownList1.DataBind()
        End If
       

    End Sub

Sub BindData()
SqlDataAdapter2.Fill(DataSet21)
        DataGrid1.DataBind()
End Sub

Sub BindFilteredData(ByVal filter As String)
SqlDataAdapter2.Fill(DataSet21)
DataGrid1.DataSource=FilterDataSet(DataSet21, filter)
        DataGrid1.DataBind()
ENd If

Private Sub  DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlAtty.SelectedIndexChanged
        BindFilteredData( DropDownList1.SelectedItem.Value)
    End Sub
0
 
freakyunoAuthor Commented:
Thanks for the quick response YZlat.

I'm a little confused though.  You have to Page_Load subs.

Also, when I try to include the
Private Sub  DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlAtty.SelectedIndexChanged
        BindFilteredData( DropDownList1.SelectedItem.Value)
    End Sub

It tells me it's not valid outside a method.  I think I get the gist of how this is going to work, but could you follow up for me please?

0
 
YZlatCommented:
sorry, change it to:

Private Sub  DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        BindFilteredData( DropDownList1.SelectedItem.Value)
    End Sub
0
 
freakyunoAuthor Commented:
Got it Working.  Thanks.  A+
0
All Courses

From novice to tech pro — start learning today.