how to dropdown related data in textbox based on a another textbox value

Question

In my Project, There are one or many Address_code are assigned to the particular Customer_Name. I have a one textbox Which hold Customer_Name. When I select particular Customer_Name which is populated using AutoCompleteExtender. Then I want to display Address_code related to that Customer_Name in the next Textbox.

Here is the code for select Customer, Which Works fine..

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> SearchCustomers(string prefixText, int count)
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = dbConnection.fnConnectionString();
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = " SELECT CustomerCode,CustomerName FROM tblCustomer where " +
            "CustomerName like @SearchText + '%'";
            cmd.Parameters.AddWithValue("@SearchText", prefixText);
            cmd.Connection = conn;
            conn.Open();
            List<string> customers = new List<string>();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    String Code = sdr["CustomerCode"].ToString();
                    String Name = sdr["CustomerName"].ToString();
                    Name = Name + " ("+Code + ")";
                    customers.Add(Name);

                }
            }
            conn.Close();
            return customers;
        }
    }
}

Here is code to display Addresscode in the another Textbox..Which is not working..

 [System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> SearchAddress(string prefixText, int count)
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = dbConnection.fnConnectionString();
        using (SqlCommand cmd = new SqlCommand())
        {

            cmd.CommandText = "select Addresscode from BName_Addresscode where Addresscode like '" + prefixText + "%' ";
            cmd.Parameters.AddWithValue("@SearchText", prefixText);
            cmd.Connection = conn;
            conn.Open();
            List<string> customers1 = new List<string>();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                   // String Code = sdr["City"].ToString();
                    String Name = sdr["Addresscode"].ToString();
                   // Name = Code + "(" + Name + ")";
                    customers1.Add(Name);

                }
            }
            conn.Close();
            return customers1;
        }
    }
}

I didn't get Addresscode which are related to that CustomerName — Kalpesh, Sep 05 '18 at 08:34
Have you tried changing your query to include the param as you have done in the first method? So `cmd.CommandText = "select Addresscode from BName_Addresscode where Addresscode like @SearchText + '%'";` — Izzy, Sep 05 '18 at 08:36

score 0 · Answer 1 · answered Sep 05 '18 at 08:37

0

The parameter actually doesn't do anything and your code is open for sql injection.

The first part was correct where you select from tblCustomer.

Change to the way you used before and it should work correctly

cmd.CommandText = "select Addresscode from BName_Addresscode where Addresscode like '@SearchText%' ";
cmd.Parameters.AddWithValue("@SearchText", prefixText);

answered Sep 05 '18 at 08:37

H.Mikhaeljan

813
12
22

@Kalpesh is the prefixText filled with anything? do you get any records in `String Name = sdr["Addresscode"].ToString();` ? – H.Mikhaeljan Sep 05 '18 at 09:37
@H-mikhaeljan Instead of displaying Addresscode for particular Customer, It shows all the Addresscode from database – Kalpesh Sep 06 '18 at 06:06
@Kalpesh if you use % it will give you results that start with the prefix and can have anything behind the prefix. If it must match the prefix exactly then `@SearchText` without the `%` – H.Mikhaeljan Sep 06 '18 at 08:47

how to dropdown related data in textbox based on a another textbox value

1 Answers1