locked
Assigning Datasource to a List Box programmatically RRS feed

  • Question

  • User-1674044403 posted

    Hi! Friends,

    I have a list box control which is data bounded to the datasource through the Visual Studio designer. This datasource contains a query for a function.

    Now i am writing a search function which would cause the list box to be populated by a different query which means, now it will be bounded with a different datasource instead of the current datasource. So how can i use this new datasource which is to be created by the search function on the same list box. The main purpose is to use the DataValueField and DataTextField of the datasource created by the search function.

    Please friends help me with this i am using the code below to assign the new datasource to the list box hoping that it will over write the current datasource

    protected void SearchConsignee()
            {
               
                consigneeListBox.Items.Clear();
                SqlCommand comm = new SqlCommand(String.Format("Select Company_Name,Company_Id from Company where Id_Type='Consignee' and Company_Name like '{0}%'", searchTxtBox.Text));
                comm.Connection = con;

                try
                {
                    con.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    
                    
                    

                    if (dr.HasRows)
                    {
                       
                        while (dr.Read())
                        {
                            consigneeListBox.Items.Add(dr["Company_Name"].ToString());
                            consigneeListBox.DataSource = dr;
                            consigneeListBox.DataTextField = dr["Company_Name"].ToString();
                            consigneeListBox.DataValueField = dr["Company_Id"].ToString();
                            
                        }
                        
                    }
                    
                    dr.Close();
                    con.Close();
                }
                catch
                {
                }

                
            }

    Thursday, March 24, 2011 2:41 PM

Answers

  • User-1499637000 posted

    Hi Syed,

    • Don't assign the DataSource in the deigner, try to assign the page_load event.Once the user clicks the button then assign the deifferent datasource to the list box.
    • If possible take two different listbixes and try to achive the same functionality.

    Thnaks

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 25, 2011 10:16 PM

All replies

  • User312496708 posted
    If you have set the DataSourceID in the designer, Then you also need to set the DatasourceID property of the control to empty string in the code to use any other datasource.
    Thursday, March 24, 2011 2:51 PM
  • User-1674044403 posted

    Thanks for reply,

    So you mean to do something like this,

    consigneeListBox.DataSourceID = "";

    and then assign the DataSource as,

    consigneeListBox.DataSource = dr;

     

     

    Thursday, March 24, 2011 3:10 PM
  • User-1674044403 posted

    Vik,

    when i assigned the DataSourceID to empty string. I am getting this error "

    Invalid attempt to call FieldCount when reader is closed.

    "

    Thursday, March 24, 2011 3:22 PM
  • User-1674044403 posted

    Please guys i am stuck in this problem please help me with this

    Friday, March 25, 2011 11:05 AM
  • User-1499637000 posted

    Hi Syed,

    I didn't understood your requirement clearly, what I understood is first you want to dispaly the items in the listbox from the DataSource and on click of the button you want to filter and display the records from the different table.

    If you are trying for above scenario, here is the aspx page and code behind file.

    Aspx Page:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ListBoxSearch.aspx.cs"
        Inherits="SampleWeb.ListBoxSearch" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>ListBox Search Example</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <table>
                <tr>
                    <td>
                        <asp:TextBox ID="txtProd" runat="server"></asp:TextBox>
                    </td>
                    <td>
                        <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="btnSearch_Click" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:ListBox ID="productsListBox" runat="server"></asp:ListBox>
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
                    </td>
                </tr>
            </table>
            <asp:SqlDataSource ID="CategoryDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
                SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]"></asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    Code behind file:

    using System;
    using System.Data.SqlClient;
    using System.Configuration;
    
    namespace SampleWeb
    {
        public partial class ListBoxSearch : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    GetCategories();
                }
            }
    
            private void GetCategories()
            {
                productsListBox.DataSource = CategoryDataSource;
                productsListBox.DataTextField = "CategoryName";
                productsListBox.DataValueField = "CategoryID";
                productsListBox.DataBind();
                Label1.Text = "Categories loaded from Category table.";
            }
    
            protected void btnSearch_Click(object sender, EventArgs e)
            {
                SearchProducts();
            }
    
            private void SearchProducts()
            {
                productsListBox.Items.Clear();
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
                SqlCommand comm = new SqlCommand(String.Format("SELECT [ProductID], [ProductName] FROM [Alphabetical list of products] where [ProductName] like '{0}%'", txtProd.Text));
                comm.Connection = con;
    
                try
                {
                    con.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        productsListBox.DataSource = string.Empty;
                        productsListBox.DataTextField = string.Empty;
                        productsListBox.DataValueField = string.Empty;
    
                        productsListBox.DataTextField = "ProductName".ToString();
                        productsListBox.DataValueField = "ProductID".ToString();
    
                        while (dr.Read())
                            productsListBox.Items.Add(dr["ProductName"].ToString());
                      
                        Label1.Text = "Products loaded from products table.";
                    }
                    else
                        GetCategories();
    
                    dr.Close();
                    con.Close();
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }
    }

    Here first I am getting the records from the Category table and dispalying, once user enters a starting letter say 'C' or 'S', then I am searching the products table and displyaing the records.

    Hope the above solution may help to solve your problem, if not please post your Aspx and code behind files. I will try to help you out.

     

    Friday, March 25, 2011 12:38 PM
  • User-1674044403 posted

    Ok thanks for the reply. Actually looking at the code i think i can adapt that to my requirement.

    So I will try this out and let you know of the result. I am trying that now and let you know as soon as possible.

    Friday, March 25, 2011 12:51 PM
  • User-1674044403 posted

    Nagesh,

    I am not able to do by that way.

    So let me explain my requirement. I have a List Box which i have binded to DataSource and i have set the Datatextfield = CompanyName and DataValueField=CompanyId. This datasource populates the list box and displays the Company Name.

    So whenever i click an item in the list box i get the detail information in the text boxes which are besides the list box based on the DatavalueField.

    Now, what i am doing is that i am performing a search based on CompanyName which is DataTextField so that i can filter the list box with searched Company name. So for this i am changin my query to " Select Company_Name,Company_Id from Company where Id_Type='Consignee' and Company_Name like '{0}%'", searchTxtBox.Text "

    So when i get the searched CompanyName in the list box, i am clicking on that name item in the list box to get the detailed information in the textboxes. But i am not able to get the detail information in the text boxes. I think since i am using the same List Box and writing 2 queries on it the DataSource is not getting changed and also the DataTextField and DataValueField gets resetted.


    I am sending the code please check it

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <style type="text/css">
            .style1
            {
            }
            .style2
            {
                width: 41px;
            }
            .style5
            {
                width: 343px;
            }
            .style8
            {
                width: 105px;
            }
            .style9
            {
                width: 90px;
            }
            .style10
            {
                width: 97px;
            }
            .style11
            {
                width: 111px;
            }
        </style>
    </head>
    <body>
        <%--<script type="text/javascript">
        function alertBox() {
            if (document.getElementById('<%=cidtxtBox.ClientID %>').value == "") {
                alert('Update Failed');
                return false;
            }
            else {
                alert('Update Successful');
                return true;
            }
        }
    </script>--%>
     <form runat="server">
        <table style="width: 100%">
            <tr>
                <td rowspan="16" style="width: 107px">
                    <asp:ListBox ID="consigneeListBox" runat="server" 
                        DataSourceID="consigneeDataSource" DataTextField="Company_Name" 
                        DataValueField="Company_Id" Height="347px" Width="206px" 
                        AutoPostBack="True" onselectedindexchanged="consigneeListBox_SelectedIndexChanged" 
                        >
                    </asp:ListBox>
                    <asp:SqlDataSource ID="consigneeDataSource" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:NavigoConnectionString %>" 
                        
                        SelectCommand="SELECT Company_Id, Company_Name FROM Company WHERE (Id_Type = 'Consignee')">
                    </asp:SqlDataSource>
                   
                </td>
                <td class="style1" colspan="2">
                    <asp:Label ID="msg" runat="server" ForeColor="Red" 
                        Text="First Clear the contents by clicking Clear button" Visible="False"></asp:Label>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="cidlbl" runat="server" Text="Consignee ID:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="cidtxtBox" runat="server" style="margin-left: 0px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="cnlbl" runat="server" Text="Company Name:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="cntxtBox" runat="server" Width="165px"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="ctlbl" runat="server" Text="Company Type:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="cttxtbox" runat="server" ></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="add1lbl" runat="server" Text="Address 1:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="add1txtBox" runat="server" 
                        ></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="add2lbl" runat="server" Text="Address 2:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="add2txtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="citylbl" runat="server" Text="City:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="citytxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="statelbl" runat="server" Text="State:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="sttxtbox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="zipCodelbl" runat="server" Text="Zip Code:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="zipcodetxtbox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="countrylbl" runat="server" Text="Country:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="countrytxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="cclbl" runat="server" Text="Country Code:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="cctxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="phonelbl" runat="server" Text="Phone:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="phonetxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="faxlbl" runat="server" Text="Fax:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="faxtxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    <asp:Label ID="emaillbl" runat="server" Text="Email:"></asp:Label>
                </td>
                <td class="style11">
                    <asp:TextBox ID="emailtxtBox" runat="server"></asp:TextBox>
                </td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    &nbsp;</td>
                <td class="style11">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style8">
                    &nbsp;</td>
                <td class="style11">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
        </table>
        <table style="width: 100%">
            <tr>
                <td class="style5">
                    <asp:Label ID="searchlbl" runat="server" Text="Search:"></asp:Label>
                    <asp:TextBox ID="searchTxtBox" runat="server" Width="187px" 
                        ontextchanged="searchTxtBox_TextChanged"></asp:TextBox>
                &nbsp;&nbsp;
                    <asp:Button ID="searchBtn" runat="server" onclick="searchBtn_Click" 
                        Text="Search" />
                </td>
                <td class="style9">
                    <asp:Button ID="addnewBtn" runat="server" Text="Add New" Width="75px" 
                        onclick="addnewBtn_Click" />
                &nbsp;&nbsp;&nbsp;
                </td>
                <td class="style2">
                    <asp:Button ID="delBtn" runat="server" Text="Delete" Width="76px" 
                        onclick="delBtn_Click" style="margin-left: 1px" />
                </td>
                <td class="style10">
                    <asp:Button ID="updateBtn" runat="server" onclick="updateBtn_Click" 
                        Text="Update" Width="75px" 
                        style="z-index: 1; left: 485px; top: 448px; ; margin-left: 61px" />
                </td>
                <td>
                    <asp:Button ID="clearBtn" runat="server" Text="Clear/Refresh List" 
                        onclick="clearBtn_Click" 
                        style="z-index: 1; left: 601px; top: 448px; ; width: 128px; margin-left: 39px" />
                </td>
            </tr>
        </table>
        <br />
       </form>
    </body>
    </html>
    
    protected void Page_Load(object sender, EventArgs e) // On the Page Load the List Box is populating
    {
    cttxtbox.Text = "Consignee";
    }
     protected void consigneeListBox_SelectedIndexChanged(object sender, EventArgs e)
            {
    
                LoadDetails();
              // Function to displaying detailed information in the text boxes
      } protected void LoadDetails() { string id = consigneeListBox.SelectedValue; SqlCommand com = new SqlCommand(); SqlDataReader dr; com.CommandText = "select EIN,Company_Name,Address1,Address2,City,State,Zip_Code,Country,countryCode,Phone,Fax,Email from Company where Company_Id=@Company_Id"; com.Parameters.Add("@Company_Id", SqlDbType.Int).Value = id; com.Connection = con; if (id != "") { try { con.Open(); dr = com.ExecuteReader(); while (dr.Read()) { cidtxtBox.Text = dr.GetValue(0).ToString(); cntxtBox.Text = dr.GetValue(1).ToString(); add1txtBox.Text = dr.GetValue(2).ToString(); add2txtBox.Text = dr.GetValue(3).ToString(); citytxtBox.Text = dr.GetValue(4).ToString(); sttxtbox.Text = dr.GetValue(5).ToString(); zipcodetxtbox.Text = dr.GetValue(6).ToString(); countrytxtBox.Text = dr.GetValue(7).ToString(); cctxtBox.Text = dr.GetValue(8).ToString(); phonetxtBox.Text = dr.GetValue(9).ToString(); faxtxtBox.Text = dr.GetValue(10).ToString(); emailtxtBox.Text = dr.GetValue(11).ToString(); } dr.Close(); con.Close(); } catch { } } else { cidtxtBox.Text = ""; cntxtBox.Text = ""; add1txtBox.Text = ""; add2txtBox.Text = ""; citytxtBox.Text = ""; sttxtbox.Text = ""; zipcodetxtbox.Text = ""; countrytxtBox.Text = ""; cctxtBox.Text = ""; phonetxtBox.Text = ""; faxtxtBox.Text = ""; emailtxtBox.Text = ""; } }
    protected void SearchConsignee()
    // This function searches the entered Company Name in the search text box and filters the List Box
     { consigneeListBox.Items.Clear(); SqlCommand comm = new SqlCommand(String.Format("Select Company_Name,Company_Id from Company where Id_Type='Consignee' and Company_Name like '{0}%'", searchTxtBox.Text)); SqlDataReader dr; comm.Connection = con; try { con.Open(); dr = comm.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { consigneeListBox.Items.Add(dr["Company_Name"].ToString()); //consigneeListBox.DataSourceID = ""; //consigneeListBox.DataSource = comm.CommandText; //consigneeListBox.DataBind(); //consigneeListBox.DataTextField = dr["Company_Name"].ToString(); //consigneeListBox.DataValueField = dr["Company_Id"].ToString(); } } dr.Close(); con.Close(); } catch { } } protected void searchTxtBox_TextChanged(object sender, EventArgs e) { SearchConsignee(); // Now when i call the below event the SelectedValue field in the LoadDetails() becoming empty string
     consigneeListBox_SelectedIndexChanged(sender, e); } protected void searchBtn_Click(object sender, EventArgs e) { searchTxtBox_TextChanged(sender, e); }

    Friday, March 25, 2011 1:23 PM
  • User-1674044403 posted

    If you didnt understood, basically i just want to assign a new DataSource related to the Search query and set the Text Field and the Value filed to the new values for the List Box which is already assigned to a DataSource through the designer.

    Ask me if you didnt understood this

    Regards,

    Syed

    Friday, March 25, 2011 1:29 PM
  • User-1499637000 posted

    Hi Syed,

    • Don't assign the DataSource in the deigner, try to assign the page_load event.Once the user clicks the button then assign the deifferent datasource to the list box.
    • If possible take two different listbixes and try to achive the same functionality.

    Thnaks

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 25, 2011 10:16 PM