Pass (Send) ASP.Net DropDownList selected value to SqlDatasource SelectCommand using C# and VB.Net

RayMilhon
 
on Jan 24, 2023 09:52 PM
463 Views

I have a sqldatasource in an ASP.NET Website.  I also have a combo box once the user selects an item in the combobox I want the sqldatasource to be changed to add a where clause.  Which works fine.  The issue I'm having is as soon as the user clicks on any other control on the webpage the sqldatasource reverts back to original.  I need it to change depending on what is selected in the combobox and not change again until the combobox is updated.

If DropDownList1.SelectedValue = 0 Then
    SqlDataSource1.SelectCommand = "SELECT [member], [MEMBID], [LANGUAGE], [PHONE], [HEALTHPLAN], [PRODUCTLINE],[PRIMARY_CARE_PHYSICIAN], [age] FROM [V_members_needing_assessment] where pcp = ORDER BY MEMBER"
Else
    SqlDataSource1.SelectCommand = "SELECT [member], [MEMBID], [LANGUAGE], [PHONE], [HEALTHPLAN], [PRODUCTLINE],[PRIMARY_CARE_PHYSICIAN], [age] FROM [V_members_needing_assessment] where pcp = '" & DropDownList1.SelectedValue & "' ORDER BY MEMBER"
End If

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
AjayV
 
on Jan 25, 2023 02:39 AM
on Jan 25, 2023 06:12 AM

Hi RayMilhon,

Please refer below sample.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ConnectionStrings:constr%>"
    SelectCommand="SELECT CustomerId, ContactName, Country FROM Customers"></asp:SqlDataSource>
<asp:DropDownList ID="ddlCountries" runat="server">
    <asp:ListItem Text="--Select Country--" Value="0"></asp:ListItem>
    <asp:ListItem Text="Germany" Value="1"></asp:ListItem>
    <asp:ListItem Text="Mexico" Value="2"></asp:ListItem>
    <asp:ListItem Text="USA" Value="3"></asp:ListItem>
    <asp:ListItem Text="UK" Value="4"></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="OnSearch" />
<hr />
<asp:TextBox ID="txtId" runat="server" /><br />
<asp:TextBox ID="txtName" runat="server" /><br />
<asp:Button ID="btnNext" runat="server" Text="Next" OnClick="OnNext" />
<asp:HiddenField ID="hfIndex" runat="server" Value="0" />

Namespace

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void OnSearch(object sender, EventArgs e)
{
    hfIndex.Value = "0";
    if (ddlCountries.SelectedIndex > 0)
    {
        SqlDataSource1.SelectCommand = string.Format("SELECT CustomerId, ContactName, Country FROM Customers WHERE Country = '{0}'", ddlCountries.SelectedItem.Text);
    }

    DataSourceSelectArguments args = new DataSourceSelectArguments();
    DataView view = (DataView)SqlDataSource1.Select(args);
    DataTable dt = view.ToTable();
    Session["Data"] = dt;
    txtId.Text = dt.Rows[Convert.ToInt32(hfIndex.Value)]["CustomerId"].ToString();
    txtName.Text = dt.Rows[Convert.ToInt32(hfIndex.Value)]["ContactName"].ToString();
    hfIndex.Value = (Convert.ToInt32(hfIndex.Value) + 1).ToString();
}

protected void OnNext(object sender, EventArgs e)
{
    DataTable dt = (DataTable)Session["Data"];
    txtId.Text = dt.Rows[Convert.ToInt32(hfIndex.Value)]["CustomerId"].ToString();
    txtName.Text = dt.Rows[Convert.ToInt32(hfIndex.Value)]["ContactName"].ToString();
    //txtCountry.Text = dt.Rows[Convert.ToInt32(hfIndex.Value)]["Country"].ToString();
    hfIndex.Value = (Convert.ToInt32(hfIndex.Value) + 1).ToString();
    if (Convert.ToInt32(hfIndex.Value) == dt.Rows.Count)
    {
        hfIndex.Value = "0";
    }
}

VB.Net

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    hfIndex.Value = "0"

    If ddlCountries.SelectedIndex > 0 Then
        SqlDataSource1.SelectCommand = String.Format("SELECT CustomerId, ContactName, Country FROM Customers WHERE Country = '{0}'", ddlCountries.SelectedItem.Text)
    End If

    Dim args As DataSourceSelectArguments = New DataSourceSelectArguments()
    Dim view As DataView = CType(SqlDataSource1.[Select](args), DataView)
    Dim dt As DataTable = view.ToTable()
    Session("Data") = dt
    txtId.Text = dt.Rows(Convert.ToInt32(hfIndex.Value))("CustomerId").ToString()
    txtName.Text = dt.Rows(Convert.ToInt32(hfIndex.Value))("ContactName").ToString()
    hfIndex.Value = (Convert.ToInt32(hfIndex.Value) + 1).ToString()
End Sub

Protected Sub OnNext(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = CType(Session("Data"), DataTable)
    txtId.Text = dt.Rows(Convert.ToInt32(hfIndex.Value))("CustomerId").ToString()
    txtName.Text = dt.Rows(Convert.ToInt32(hfIndex.Value))("ContactName").ToString()
    hfIndex.Value = (Convert.ToInt32(hfIndex.Value) + 1).ToString()

    If Convert.ToInt32(hfIndex.Value) = dt.Rows.Count Then
        hfIndex.Value = "0"
    End If
End Sub

 Screenshot