ASP.Net (VB) - Populate drop down list using data from previous drop down list. [Member Not Found]

Question

Currently making a form with drop down list that changes data based on the drop down list before it. I'm using this example as reference and this is what I have made so far:

Design:

        <asp:Table ID="Table1" runat="server" Width="100%">

        <asp:TableRow runat="server">

            <asp:TableCell runat="server" Width="10%">
                <asp:Label ID="Label1" runat="server" Text="Line" Font-Size="x-Large"></asp:Label>
         
            </asp:TableCell>
                
            <asp:TableCell ID="TableCell11" runat="server" Width="3%">
                <asp:Label ID="label_txt" runat="server" Text=" :" Font-Size="x-Large"></asp:Label>
              
            </asp:TableCell>

            <asp:TableCell runat="server" Width = "40%">
                <asp:dropdownlist ID="line" runat="server"  BorderStyle="Solid" Font-Size="x-Large" BorderWidth="1px" Width = 80% ReadOnly="True"
                autopostback=true onselectedindexchanged= "line_selectedindexchanged"></asp:dropdownlist>

            </asp:TableCell>

            </asp:TableRow>

            <asp:TableRow runat="server">
            
                <asp:TableCell ID="TableCell1" runat="server" Width  = 10%>
                <asp:Label ID="Label2" runat="server" Text="Process" Font-Size="x-Large"></asp:Label>

            </asp:TableCell>

            <asp:TableCell ID="TableCell12" runat="server" Width = 3%>
                <asp:Label ID="Label8" runat="server" Text=" :" Font-Size="x-Large"></asp:Label>
             
            </asp:TableCell>

            <asp:TableCell ID="TableCell2" runat="server" Width = 40%>
                <asp:dropdownlist ID="process" runat="server"  BorderStyle="Solid" Font-Size="x-Large" BorderWidth="1px" Width = 80% ReadOnly="True" 
                autopostback=true onselectedindexchanged= "process_selectedindexchanged"></asp:dropdownlist>

            </asp:TableCell>
            
        </asp:TableRow>

        <asp:TableRow runat="server">              

            <asp:TableCell ID="TableCell5" runat="server" Width=10%>
                <asp:Label ID="Label4" runat="server" Text="Equipment" Font-Size="x-Large"></asp:Label>

            </asp:TableCell>

            <asp:TableCell ID="TableCell14" runat="server" Width=3%>
                <asp:Label ID="Label10" runat="server" Text=" :" Font-Size="x-Large"></asp:Label> 
             
            </asp:TableCell>

            <asp:TableCell ID="TableCell6" runat="server" Width=40%>
                <asp:dropdownlist ID="equipment" runat="server"  BorderStyle="Solid" Font-Size="x-Large" BorderWidth="1px" Width=80% ReadOnly="True"
                 autopostback=true onselectedindexchanged= "equipment_selectedindexchanged"></asp:dropdownlist>

            </asp:TableCell>

            </asp:tablerow>

            <asp:TableRow ID="TableRow3" runat="server">

            <asp:TableCell ID="TableCell3" runat="server" Width =  10%>
                <asp:Label ID="Label3" runat="server" Text="Step" Font-Size="x-Large"></asp:Label>

            </asp:TableCell>

            <asp:TableCell ID="TableCell13" runat="server" Width = 3%>
                <asp:Label ID="Label9" runat="server" Text=" :" Font-Size="x-Large"></asp:Label>  
           
            </asp:TableCell>

            <asp:TableCell ID="TableCell4" runat="server" Width = 40%>
                
                <asp:DropDownList ID="step" runat="server" BorderStyle="Solid" Font-Size="x-Large" BorderWidth="1px" Width = 80%
                autopostback=true >
                </asp:DropDownList> 
     
            </asp:TableCell>

        </asp:TableRow>  

    </asp:Table>

Code:

Imports System.Data.SqlClient

Public Class WebForm1
Inherits System.Web.UI.Page

Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("SQLIOTConnectionString").ConnectionString)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Session("EmpID") Is Nothing Then

        Response.Redirect("~/Login.aspx")

    Else

        If Not IsPostBack Then 'whenever hv dropdownlist, need to have this in load sub, or else won't get ddl data.

            CaseDate.Text = DateTime.Now.ToString("yyyy-MM-dd")

            CreateBy.Text = Session("EmpID").ToString

            ReportPIC.Text = Session("EmpID").ToString

            FillLine()

        End If

    End If

End Sub

Protected Sub line_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level1_ID As Integer = Convert.ToInt32(line.SelectedValue.ToString())
    FillProcess(level1_ID)

End Sub

Protected Sub process_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level2_ID As Integer = Convert.ToInt32(process.SelectedValue.ToString())
    FillEquipment(level2_ID)

End Sub

Protected Sub equipment_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level3_ID As Integer = Convert.ToInt32(equipment.SelectedValue.ToString())
    FillStep(level3_ID)

End Sub

Private Sub FillLine()

    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text

    cmd.CommandText = "select distinct level1_id, [LCODE1]+ ' | '+[LNAME1] as [LCODE1]" _
                      & " FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP]"

    Dim objDs As New DataSet()
    Dim dAdapter As New SqlDataAdapter()

    dAdapter.SelectCommand = cmd
    conn.Open()
    dAdapter.Fill(objDs)
    conn.Close()

    If objDs.Tables(0).Rows.Count > 0 Then

        line.DataSource = objDs.Tables(0)
        line.DataTextField = "LCODE1"
        line.DataValueField = "level1_id"
        line.DataBind()
        line.Items.Insert(0, "")

    End If


End Sub

Private Sub FillProcess(ByVal level1_ID As Integer)

    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text

    cmd.CommandText = "select distinct level2_id, [LCODE2]+ ' | '+[LNAME2] as [LCODE2]" _
                      & " FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level1_id = @level1_id"

    cmd.Parameters.AddWithValue("@level1_id", level1_ID)

    Dim objDs As New DataSet()
    Dim dAdapter As New SqlDataAdapter()
    dAdapter.SelectCommand = cmd
    conn.Open()
    dAdapter.Fill(objDs)
    conn.Close()

    If objDs.Tables(0).Rows.Count > 0 Then

        process.DataSource = objDs.Tables(0)
        process.DataTextField = "LCODE2"
        process.DataValueField = "level2_id"
        process.DataBind()
        process.Items.Insert(0, "")

    End If

End Sub

Private Sub FillEquipment(ByVal level2_ID As Integer)

    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text

    cmd.CommandText = "select distinct level3_id, [LCODE3]+ ' | '+[LNAME3] as [LCODE3]" _
                      & " FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level2_id = @level2_id"

    cmd.Parameters.AddWithValue("@level2_id", level2_ID)

    Dim objDs As New DataSet()
    Dim dAdapter As New SqlDataAdapter()
    dAdapter.SelectCommand = cmd
    conn.Open()
    dAdapter.Fill(objDs)
    conn.Close()

    If objDs.Tables(0).Rows.Count > 0 Then

        process.DataSource = objDs.Tables(0)
        process.DataTextField = "LCODE3"
        process.DataValueField = "level3_id"
        process.DataBind()
        process.Items.Insert(0, "")

    End If

End Sub

Private Sub FillStep(ByVal level3_ID As Integer)

    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text

    cmd.CommandText = "select distinct [LCODE4]+ ' | '+[LNAME4] as [LCODE4]" _
                      & " FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level3_id = @level3_id"

    cmd.Parameters.AddWithValue("@level3_id", level3_ID)

    Dim objDs As New DataSet()
    Dim dAdapter As New SqlDataAdapter()
    dAdapter.SelectCommand = cmd
    conn.Open()
    dAdapter.Fill(objDs)
    conn.Close()

    If objDs.Tables(0).Rows.Count > 0 Then

        process.DataSource = objDs.Tables(0)
        process.DataTextField = "LCODE4"
        'process.DataValueField = "level3_id"
        process.DataBind()
        process.Items.Insert(0, "")

    End If

End Sub

From what I understand in the example, once I select a value from the first drop down list, the next one before it will have value and so on. But during testing, after I select value from the 1st drop down list, I get an error

JavaScript runtime error: Member not found.

I have no idea how JavaScript works, nor did I even touch it... Can anyone help with this issue?

EDIT:

Using Mary's suggestion:

Protected Sub line_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level1_ID As Integer = Convert.ToInt32(line.SelectedValue.ToString())
    FillProcess(level1_ID)

End Sub

Protected Sub ddlprocess_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level2_ID As Integer = Convert.ToInt32(ddlprocess.SelectedValue.ToString())
    FillEquipment(level2_ID)

End Sub

Protected Sub equipment_selectedindexchanged(ByVal sender As Object, ByVal e As EventArgs)

    Dim level3_ID As Integer = Convert.ToInt32(equipment.SelectedValue.ToString())
    FillStep(level3_ID)

End Sub

Private Sub FillLine()

    Dim dt As New DataTable
    Dim strSql = "select distinct level1_id, [LCODE1]+ ' | '+[LNAME1] as [LCODE1]" _
                      & " FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP]"
    Using conn As New SqlConnection(ConStr),
        cmd As New SqlCommand(strSql, conn)            
        conn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    If dt.Rows.Count > 0 Then
        line.DataSource = dt
        line.DataTextField = "LCODE1"
        line.DataValueField = "level1_id"
        line.DataBind()
        line.Items.Insert(0, "")
    End If

End Sub

Private Sub FillProcess(ByVal level1_ID As Integer)

    Dim dt As New DataTable
    Dim strSql = "select distinct level2_id, [LCODE2]+ ' | '+[LNAME2] as [LCODE2]" _
                 & "FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level1_id = @level1_id"
    Using conn As New SqlConnection(ConStr),
        cmd As New SqlCommand(strSql, conn)
        cmd.Parameters.Add("@level1_id", SqlDbType.Int).Value = level1_ID
        conn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    If dt.Rows.Count > 0 Then
        ddlprocess.DataSource = dt
        ddlprocess.DataTextField = "LCODE2"
        ddlprocess.DataValueField = "level2_id"
        ddlprocess.DataBind()
        ddlprocess.Items.Insert(0, "")
    End If

End Sub

Private Sub FillEquipment(ByVal level2_ID As Integer)

    Dim dt As New DataTable
    Dim strSql = "select distinct level3_id, [LCODE3]+ ' | '+[LNAME3] as [LCODE3]" _
                 & "FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level2_id = @level2_id"
    Using conn As New SqlConnection(ConStr),
        cmd As New SqlCommand(strSql, conn)
        cmd.Parameters.Add("@level2_id", SqlDbType.Int).Value = level2_ID
        conn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    If dt.Rows.Count > 0 Then
        equipment.DataSource = dt
        equipment.DataTextField = "LCODE3"
        equipment.DataValueField = "level3_id"
        equipment.DataBind()
        equipment.Items.Insert(0, "")
    End If

End Sub

Private Sub FillStep(ByVal level3_ID As Integer)

    Dim dt As New DataTable
    Dim strSql = "select distinct level4_id, [LCODE4]+ ' | '+[LNAME4] as [LCODE4]" _
                 & "FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level3_id = @level3_id"
    Using conn As New SqlConnection(ConStr),
        cmd As New SqlCommand(strSql, conn)
        cmd.Parameters.Add("@level3_id", SqlDbType.Int).Value = level3_ID
        conn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    If dt.Rows.Count > 0 Then
        [step].DataSource = dt
        [step].DataTextField = "LCODE4"
        [step].DataValueField = "level3_id"
        [step].DataBind()
        [step].Items.Insert(0, "")
    End If

End Sub

But the same problem remains...

score 1 · Accepted Answer · answered Sep 15 '20 at 07:09

The only thing I could see that would cause the error was the name process. That could cause a conflict with the Process class in System.Diagnostics. I changed the name to ddlProcess.

I loaded a datatable instead of fooling with a DataSet or DataAdapter.

Connections need to be local to the method where they are used so they can be closed and disposed. They use unmanaged resources which are released in their .Dispose method. Using...End Using blocks handle this for you even if there is an error. I included the command in the using block.

In Sql Server the .Add method is preferred. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications Here is another https://andrevdm.blogspot.com/2010/12/parameterised-queriesdont-use.html

I was a bit surprised that drop down list allowed you to insert an item when it was already databound. Maybe delete that line if it is not necessary.

Private ConStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("SQLIOTConnectionString").ConnectionString

Private Sub FillProcess(ByVal level1_ID As Integer)
    Dim dt As New DataTable
    Dim strSql = "select distinct level2_id, [LCODE2]+ ' | '+[LNAME2] as [LCODE2]
                   FROM [SQLIOT].[dbo].[ZVIEW_MCM_LEVEL_LOOKUP] where level1_id = @level1_id"
    Using conn As New SqlConnection(ConStr),
        cmd As New SqlCommand(strSql, conn)
        cmd.Parameters.Add("@level1_id", SqlDbType.Int).Value = level1_ID
        conn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    If dt.Rows.Count > 0 Then
        ddlProcess.DataSource = dt
        ddlProcess.DataTextField = "LCODE2"
        ddlProcess.DataValueField = "level2_id"
        ddlProcess.DataBind()
        ddlProcess.Items.Insert(0, "")
    End If
End Sub

I tried to follow your example. Changed `process` to `ddlproces`, used `datatable` and `using` for all the dropdownlist, even removed thee code for the dummy item. But still same result. I'll edit in the current code in my question. — hjh93, Sep 15 '20 at 07:51
There doesn't seem to be a space between `[LCODE2]" _ & "FROM` In recent versions of Visual Studio a string can be continued from one line to another without `"_ &"` Maybe the problem is in the asp layout code. I don't see an error but I am not very familiar with that part. — Mary, Sep 15 '20 at 17:59
So latest development. I tried to repeat the code in a fresh project. Not other components from my 1st form. Just the table and the code itself. And it works fine. Have some hiccups but not an issue yet. Guess something else is messing up this code. I'll mark this as correct 1st and post another question once I figured out what the problem is. — hjh93, Sep 17 '20 at 07:25

ASP.Net (VB) - Populate drop down list using data from previous drop down list. [Member Not Found]

1 Answers1