Ajax autocompleteextender calling webservice to search from 2 fields of database, its working now

Question

I m using ajax autocomplete which calls a web service to search name and code(name & code are fields of my datatable)

It works fine

this is my webmothod of webservice

   [WebMethod]
        public string[] GetSupplier(string prefixText)
        {
            con.Open();
            //int count = 10;
            string sql = "Select * from SupplierMaster where name like @prefixText  ";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 500).Value = prefixText + "%";
            DataTable dt = new DataTable();
            da.Fill(dt);
            string[] List = new string[100];
            int i = 0;

            foreach (DataRow dr in dt.Rows)
            {
                List.SetValue(dr["name"].ToString(), i);
                i++;
            }

         string sql1 = "Select * from SupplierMaster where codelike @prefixText  ";
            SqlDataAdapter da1 = new SqlDataAdapter(sql1, con);
            da1.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 500).Value = prefixText + "%";
            DataTable dt = new DataTable();
            da1.Fill(dt);
            string[] List = new string[100];
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                List.SetValue(dr["name"].ToString(), i);
                i++;
            }
            con.Close();
            return List;

        }

and this is autocomplete extender in .aspx file

  <asp:AutoCompleteExtender runat="server" ID="AutoCompleteExtender3" TargetControlID="txtsear" ServicePath="~/Search.asmx" ServiceMethod="GetSupplier"

 MinimumPrefixLength="1" CompletionSetCount="10" 
CompletionListCssClass="autocomplete_completionListElement"
 CompletionListItemCssClass="autocomplete_listItem" 
                                                                    CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem"
 </asp:AutoCompleteExtender>

Answer 1

I got the answer,

  [WebMethod]
        public string[] GetSupplier(string prefixText)
        {
            con.Open();
            //int count = 10;
            string sql = "Select * from SupplierMaster where name like @prefixText";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 500).Value = prefixText + "%";
            DataTable dt = new DataTable();
            da.Fill(dt);
            string[] List = new string[100];
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                List.SetValue(dr["name"].ToString(), i);
                i++;
            }

            string sql1 = "Select * from SupplierMaster where code like @prefixText";
            SqlDataAdapter da1 = new SqlDataAdapter(sql1, con);
            da1.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 500).Value = prefixText + "%";
            DataTable dt1 = new DataTable();
            da1.Fill(dt1);
            //string[] List = new string[dt.Rows.Count];
            //int i = 0;
            foreach (DataRow dr in dt1.Rows)
            {
                List.SetValue(dr["code"].ToString(), i);
                i++;
            }
            con.Close();
            return List;

        }

Answer 2

Here's how I do it, adapted for your table/fields:

using System.Collections.Generic;
// your class/namespace etc.

[WebMethod]
public List<string> GetSupplier(string prefixText)
{
    string SQL = "Select * from SupplierMaster where name like '%" + prefixText.Replace("'", "''") + "%' ";

    SqlConnection cnn = new SqlConnection(ConnectionStringGoesHere);
    SqlCommand cmd = new SqlCommand(SQL, cnn);
    cmd.CommandType = CommandType.Text;

    List<string> returnvalues = new List<string>();

    try
    {
        cnn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            returnvalues.Add(dr["name"].ToString());
        }
    }
    finally
    {
        cnn.Close();
    }

    return returnvalues;
}

I took the liberty of prefixing your search text AND suffixing it with a wildcard % sign; you might not want that.

Now, if you want to search BOTH fields, you can use:

    string SQL = "Select * from SupplierMaster where name like '%" + prefixText.Replace("'", "''") + "%' OR code like '%" + prefixText.Replace("'", "''") + "%' ";

edit

From your own answer, I see you're combining the code & name fields. I would prefer to do it with my shorter code above, but with a UNION query, like so:

string SQL = "Select name from SupplierMaster where name like '%" + prefixText.Replace("'", "''") + "%' UNION Select code from SupplierMaster where code like '%" + prefixText.Replace("'", "''") + "%' ";