?
Solved

Repopulate a recordset from a Dropdown

Posted on 2005-02-25
4
Medium Priority
?
295 Views
Last Modified: 2010-04-06
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
0
Comment
Question by:freakyuno
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 1500 total points
ID: 13402662
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
 
LVL 1

Author Comment

by:freakyuno
ID: 13402744
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
 
LVL 35

Expert Comment

by:YZlat
ID: 13404213
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
 
LVL 1

Author Comment

by:freakyuno
ID: 13404249
Got it Working.  Thanks.  A+
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question