CODEWITHSUNDEEP
asp.netsql-servervb.net
rtranchilla
rtranchilla

Reputation: 47

I am having trouble getting my listbox to populate with the output from a sql query

I can’t seem to figure out where I went wrong here. I have two list boxes the first pulls its data from a stored procedure on a sql server. the second list box is supposed to populate when the an item in the first list box is selected. the second list box’s stored procedure should be passed the text of the selected item when that item in the first list box has been clicked. the problem is that that second list box is not populating. I would appreciate any helpful feedback or possibly an easier way of getting done what I am trying to do.

ASP.NET:

<asp:ListBox ID="ListBox1" runat="server" DataSourceID="LOCATION" DataTextField="L_Name" DataValueField="L_Name" AutoPostBack="True"></asp:ListBox>

<asp:SqlDataSource ID="LOCATION" runat="server" ConnectionString="<%$ ConnectionStrings:SAMC_2ConnectionString %>" SelectCommand="L_Get" SelectCommandType="StoredProcedure"></asp:SqlDataSource>

<asp:ListBox ID="ListBox2" runat="server" Height="150px" Width="200px" AutoPostBack="True" DataTextField="C_Name" DataValueField="C_Name" />

<asp:SqlDataSource ID="CompByLocal" runat="server" ConnectionString="<%$ ConnectionStrings:SAMC_2ConnectionString %>" SelectCommand="L_Get_C" SelectCommandType="StoredProcedure">    

<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" DefaultValue="" Name="L_Name" PropertyName="SelectedValue" Type="String" />
<asp:Parameter Name="L_ID" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

VB.NET:

Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        Dim val As String = ListBox1.Items(ListBox1.SelectedIndex).ToString
        TextBox1.Text = val
        ListBox2.Items.Clear()
        ListBox2.DataSource = CompByLocal
        ListBox2.DataBind()
    End Sub

Upvotes: 2

Views: 1237

Answers (1)

Luke Baughan
Luke Baughan

Reputation: 4686

My advice would be ditch the SqlDataSources and do it all in the back end - you can then be a lot more precise about when things occur in my experience. Ive done my best to write the necessary code below however my native tongue is C# - Ive used an online converter so please forgive any minor syntax errors.

ASPX:

<asp:ListBox  ID="ListBox1" runat="server"  DataTextField="L_Name" DataValueField="L_Name" AutoPostBack="True"></asp:ListBox>

<asp:ListBox ID="ListBox2" runat="server" Height="150px" Width="200px" AutoPostBack="True" DataTextField="C_Name" DataValueField="C_Name" />

.VB

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 
if (IsPostback) return
Dim results As New DataTable()
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SAMC_2ConnectionString "))
    connection.Open()
    Using command As New SqlCommand("L_Get", connection)
        command.CommandType = CommandType.StoredProcedure
        results.Load(command.ExecuteReader())
    End Using
End Using
ListBox1.DataSource = results;
ListBox1.DataBind();
End Sub


Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) 
Dim results As New DataTable()
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SAMC_2ConnectionString "))
    connection.Open()
    Using command As New SqlCommand("L_Get_C", connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("L_ID",ListBox1.SelectedValue)
        results.Load(command.ExecuteReader())
    End Using
End Using
ListBox2.DataSource = results
ListBox2.DataBind()
End Sub

Upvotes: 3

Related Questions