Urgent...AutoComplete Extender not working

Posted by Dvmkrishna under ASP.NET AJAX on 11/6/2012 | Points: 10 | Views : 4158 | Status : [Member] | Replies : 5
Hi,
Auto Complete extender is not working properly in visual studio 2008.I am using the following code :-
<asp:AutoCompleteExtender ID="TextBox1_AutoCompleteExtender" runat="server" CompletionInterval="0"
CompletionListElementID="search" CompletionSetCount="12" EnableCaching="true"
MinimumPrefixLength="1" ServiceMethod="GetEmpID" ServicePath="~/WebService1.asmx"
ShowOnlyCurrentWordInCompletionListItem="true" TargetControlID="TextBox1" >
</asp:AutoCompleteExtender>

below is the webservice code:

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetEmpID(string prefixText)
{
SqlConnection con = new SqlConnection("server=sushma-pc;user id=sa;password=secure2011;database=dbase");
DataSet ds = new DataSet();
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Distinct EmpID from Tbl_EmpDetails where EmpID like @myParameter";
cmd.Parameters.AddWithValue("@myParameter", "%" + prefixText + "%");

try
{
con.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch
{
}
finally
{
con.Close();
}
dt = ds.Tables[0];

List<string> EmpID = new List<string>();
String dbValues;

foreach (DataRow row in dt.Rows)
{

dbValues = row["EmpID"].ToString();
dbValues = dbValues.ToLower();
EmpID.Add(dbValues);
}

return EmpID.ToArray();
}
}


I am not getting any kind of error though the list is not coming in the textbox.
Kindly share your ideas regarding the same




Responses

Posted by: Nadh123 on: 11/6/2012 [Member] Starter | Points: 25

Up
0
Down
public string[] GetEmpID(string prefixText,string contextKey)
try it

Mahendra
91-9908699686

Dvmkrishna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dvmkrishna on: 11/6/2012 [Member] Starter | Points: 25

Up
0
Down
hi nadh
thanks for response.but iam not getting anything .could u please give me some code

Dvmkrishna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nkkppp on: 11/6/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Krishna,

You are using a select query and used cmd.ExecuteNonQuery() method. It is used with Insert,Update and delete and it only returns no of rows affected as int value.You can use cmd.ExecuteNonQuery() method when you are working with storedprocedure with a select statement.

So modify cmd.ExecuteNonQuery(); to cmd.ExecuteReader();

I have made few changes to your code.


public static List<string> GetEmpID(string prefixText){


using(SqlConnection con = new SqlConnection("server=sushma-pc;user id=sa;password=secure2011;database=dbase"))
{

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "select Distinct EmpID from Tbl_EmpDetails where EmpID like @myParameter+'%' ";
cmd.Parameters.AddWithValue("@myParameter", prefixText );
con.Open();
SqlDataReader sdr =cmd.ExecuteReader();
List<string> EmpID = new List<string>();
while (sdr.Read())
{
EmpID.Add(sdr["EmpID"].ToString());
}

return EmpID;
}
}


Always use using(SqlConnection con = new SqlConnection("server=sushma-pc;user id=sa;password=secure2011;database=dbase")) so that it automatically disposes all the resources after exiting the loop.

Let me know if it is still not working.

Regards,
Prathap.



Dvmkrishna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dvmkrishna on: 11/6/2012 [Member] Starter | Points: 25

Up
0
Down
Hi prathap,
Thanks for your response.i tried your code its not working in web services .actually my code is executing in web services but its not working in main execution only. i am getting all the values in execution of web services.so,could you please try our code and give u r suggestions.

Regards,
krishna.


Dvmkrishna, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nkkppp on: 11/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Is your problem resolved ?

---Prathap.

Dvmkrishna, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response