Autocomplete is not work when getting data from a database table

 I downloaded the ajax autocomplete c# sample and added the following method, it is just not working. When I run this web service method, it works fine. Can anyone take a look at the following code? Thank you.

 [WebMethod]
    public string[] GetCPTCode(string PrefixText)
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter myAdapter;
        DataTable myTable = new DataTable();
        conn = new SqlConnection();
        cmd = new SqlCommand("select code + '   ' +code_description as 'CPT Code' from tbl_2_CPTCode where code like '" + PrefixText + "%'", conn);
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["CGDBFMSConnectionString"].ConnectionString;
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        conn.Open();
        myAdapter = new SqlDataAdapter();
        myAdapter.SelectCommand = cmd;
        myAdapter.Fill(myTable);

        int i = myTable.Rows.Count;
        string[] code = new string[i];

        int k = 0;
        foreach (DataRow dr in myTable.Rows)
        {

            code.SetValue(dr[0].ToString(), k);
            k++;
        }
        cmd.Dispose();
        conn.Dispose();
        return code;
    }

0
eqi
11/7/2007 4:47:22 PM
asp.net.ajax-discussion 13973 articles. 1 followers. Follow

8 Replies
710 Views

Similar Articles

[PageSpeed] 12

hello.

did you annotate the web service with the scriptattribute? if so, what is fiddler showing you?


--
Regards,
Luis Abreu
email: labreu_at_gmail.com
EN blog:http://msmvps.com/blogs/luisabreu
0
Luis
11/7/2007 7:05:17 PM

I am just adding a new method, when I called the method, the fiddler message is as following:

 {"Message":"Invalid web service call, missing value for parameter: \u0027PrefixText\u0027.","StackTrace":" at System.Web.Script.Services.WebServiceMethodData.CallMethod(Object target, IDictionary`2 parameters)\r\n at System.Web.Script.Services.WebServiceMethodData.CallMethodFromRawParams(Object target, IDictionary`2 parameters)\r\n at System.Web.Script.Services.RestHandler.InvokeMethod(HttpContext context, WebServiceMethodData methodData, IDictionary`2 rawParams)\r\n at System.Web.Script.Services.RestHandler.ExecuteWebServiceCall(HttpContext context, WebServiceMethodData methodData)","ExceptionType":"System.InvalidOperationException"}

 Here are the aspx code and the complete web service code, The error message is result by swithing the method name from "GetCompletionList" to "GetCPTCode"

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server">

<Services>

<asp:ServiceReference Path="AutoComplete.asmx" />

</Services>

</asp:ScriptManager>

<div>

<asp:TextBox runat="server" ID="myTextBox" Width="300" autocomplete="off" />

<ajaxToolkit:AutoCompleteExtender

runat="server"

ID="autoComplete1"

TargetControlID="myTextBox"

ServicePath="AutoComplete.asmx"

ServiceMethod="GetCPTCode"

MinimumPrefixLength="2"

CompletionInterval="1000"

EnableCaching="true"

CompletionSetCount="12" />

<br />

</div>

</form>

</body>

</html>

using System.Data.SqlClient;

using System;

using System.Collections.Generic;

using System.Web.Services;

using System.Data;

using System.Configuration;

using System.Web.Script.Services;

 

 

[
WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService]

public class AutoComplete : WebService

{

public AutoComplete()

{

}

[
WebMethod]public string[] GetCompletionList(string prefixText, int count)

{

if (count == 0)

{

count = 10;

}

Random random = new Random();

List<string> items = new List<string>(count);

for (int i = 0; i < count; i++)

{

char c1 = (char)random.Next(65, 90);

char c2 = (char)random.Next(97, 122);

char c3 = (char)random.Next(97, 122);

items.Add(prefixText + c1 + c2 + c3);

}

return items.ToArray();

}

[WebMethod]
    public string[] GetCPTCode(string PrefixText)
    {
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter myAdapter;
        DataTable myTable = new DataTable();
        conn = new SqlConnection();
        cmd = new SqlCommand("select code + '   ' +code_description as 'CPT Code' from tbl_2_CPTCode where code like '" + PrefixText + "%'", conn);
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["CGDBFMSConnectionString"].ConnectionString;
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        conn.Open();
        myAdapter = new SqlDataAdapter();
        myAdapter.SelectCommand = cmd;
        myAdapter.Fill(myTable);

        int i = myTable.Rows.Count;
        string[] code = new string[i];

        int k = 0;
        foreach (DataRow dr in myTable.Rows)
        {

            code.SetValue(dr[0].ToString(), k);
            k++;
        }
        cmd.Dispose();
        conn.Dispose();
        return code;
    }

}

0
eqi
11/7/2007 7:24:46 PM

hello.

hum...shouldn't you be calling the getcompletionlist method instead of the getcptcode method?


--
Regards,
Luis Abreu
email: labreu_at_gmail.com
EN blog:http://msmvps.com/blogs/luisabreu
0
Luis
11/7/2007 9:11:15 PM

To use the data returned by GetCPTCode is what I am trying to do, I have no luck so far.

0
eqi
11/8/2007 1:18:30 PM

hello.

but that method has the wron signature for being used with the autocompleteextender. you need 2 parameters, like the method i've mentioned (notice that in the extender configuration you're saying that it should call he getctpcode method. if that is the case, then you need to add another parameter to it, like the other method i've said does).


--
Regards,
Luis Abreu
email: labreu_at_gmail.com
EN blog:http://msmvps.com/blogs/luisabreu
0
Luis
11/8/2007 1:40:07 PM

Luis,

Thank you so much for the quick response. In deed I thought about that senario and did create the following method with the two parameters. The result was the same.

[WebMethod]

public string[] GetCPTCode(string PrefixText, int count)

{

SqlConnection conn;

SqlCommand cmd;

SqlDataAdapter myAdapter;

DataTable myTable = new DataTable();

conn = new SqlConnection();

cmd = new SqlCommand("select top " + Convert.ToString(count) + " code + ' ' +code_description as 'CPT Code' from tbl_2_CPTCode where code like '" + PrefixText + "%'", conn);

conn.ConnectionString = ConfigurationManager.ConnectionStrings["HPDBFMSConnectionString"].ConnectionString;cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

conn.Open();

myAdapter =
new SqlDataAdapter();

myAdapter.SelectCommand = cmd;

myAdapter.Fill(myTable);

int i = myTable.Rows.Count;

 

 

string[] code = new string[i];

int k = 0;

foreach (DataRow dr in myTable.Rows)

{

code.SetValue(dr[0].ToString(), k);

k++;

}

cmd.Dispose();

conn.Dispose();

return code;

}

0
eqi
11/8/2007 1:57:27 PM

hello again.

in this case, parameter names are case sensitive. it should be prefixText (notice the p)


--
Regards,
Luis Abreu
email: labreu_at_gmail.com
EN blog:http://msmvps.com/blogs/luisabreu
0
Luis
11/8/2007 2:16:34 PM

You are right. It is really case sensitive. It is working now. I did not realize that prefixText is hard coded into the autocomplete extender. Thank you again.

0
eqi
11/8/2007 2:28:20 PM
Reply:

Similar Artilces:

How to get one database table data in to an input form to put info in another table.
The table data to pull from is place on the same page as the input form, it is also the same number used as the URL Parameter, so if I coiuld pull it from there it would be easier, what code do I need to do this in my input form? You will need two simple scripts set up. The first will bind the data to your form controls when the page loads. The second will insert the data from the form into the database. The syntax will vary a little depending on your database type and the language you want to use to create the code. Check out this tutorial for how to do all it is you need to know re...

Ajax AutoComplete with database, not working
Hello, I am interested in implementing a search box which gives suggestions as you type.  So, I downloaded the AjaxControlToolKit source code, I compiled the solution in VS 2005, and it is OK. Then I modified the [WebMethod]    public string[] GetCompletionList(string prefixText, int count) in an attempt to get the string array from a User table in my database. I don't quite understand this WebMethod.  for example, what is prefixText? My modified code is as follows.  1 [WebMethod] 2 public string[] GetCompletionList(string prefixTex...

Troubleshoot Web Service; Ajax AutoComplete events do not appear to not fire; Ajax web service appears to not work. .NET
Folks, I had the situation that Ajax AutoComplete events in the web service did not fire after I copied a project out of a folder, renamed it and moved it to a server. The fix was hard to find but easy to solve. I had some web service modules in an ASMX file that provided AutoComplete entries -- i.e., after the user keys in 2 or 3 characters, the list of possible entries with those leading characters is displayed for the user to select. To solve the problem, look at the web service's directive, not the code-behind or the ASMX designer.  Do a right-mouse-click...

Urgent: i m working for a web page (C#) that use for importing excel sheet data to database tables.....i wanna validation for each column data......
i m working for a web page (C#) that use for importing excel sheet data to database tables.....i wanna validation for each column data...... want suggstion/links/code....plz help me! I didn't understand your question properly but If you want validation on Excel sheet than add text box and write the validation code in the VBA for the click event. Hi,  you haven't specified what kinda database you are using and kinda validations you want to perform, still here are some of the links which do the work for you, check them out those might be helpful. http://www.vbdotnetheav...

Getting Javascript To Work with Data from Database
 I am passing a variable in the url to retrieve different jobs from my database.  So if ?id=1, then it gets the first job from the database.  I need to get the info out of the database and into my .aspx code to make it work with an image gallery javascript I have.  So I need something to fill in the following..... <div id="NameOfImageGalleryScript"><a href="Data From 'picURL' column" title="Data From 'refType' column"><img src="Data From 'picURLThumb' column" alt="Data From 'refCo...

Y ADO.NET does not return a simple ReadOnly Query data as a DataTable though we need most of the times a Single Table of Data from DataBase ? Similar to DataAdapter returning a DataSet ! Or can we Typ
Hi All, After we moved from JSP and started developing a web application in ASP.NET, the DataAdapter is used for all sort of operations like insert/update/delete for a table. But, most of the time we need to query the data from db for a Table/View and the returned data will be a Single Table of data. Though ADO.NET has a facility of keeping such a data in DataTable, y does ADO.NET have not provided a way to return a DataTable. Similar to DataAdapter returning a DataSet. Its like a mix up of DataReader and DataAdapter with Read Only component returning DataTable. Or, please let me know...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

DataTable gets data from multiple database tables
Hi all , As mentioned in the topic , how about do i put datas retreived from multiple database tables and put it into the datatable . Is it possible to do this in a loop as well ? As i am pretty new to this , some sample codes will be much appreciated . Thank you all in advance :) Hi~ On "how about i put datas retreived from multiple database tables and put it into the datatable", yes not bad idea~ On "how do i put datas retreived from multiple database tables and put it into the datatable", if those tables have the same schema, yes, DBAdapter.Fill(DataTable) will add new rows t...

Real Time get data from database with ajax
 currently I am working on some task that need getting data from server and display it into gannt chart. its need to be update in every minutes. So i think must using ajax that let the data updated without postback and refresh the page.I m using asp.net 2.0 and ajax version 1. Moreover, I cant search any info on generate gantt chart. I have no idea what method is most efficient. there is a big chunk of data.Wish someone can help me here...Thanks.  Basic: use an Update Panel, triggered by a timer Advanced: use a javascript timer, and in the timer event, call the ajax method which...

HOW TO get AJAX to Work with an Exisiting .NET Application
FYI... HOW TO get AJAX WORKING with an EXISTING .NET APPLICATION I was trying to implement AJAX within an existing application and found I need ADD SOME things and REMOVE ONE thing in web.config.   Just my experience.  There may be a more straight-forward way to do this for "existing" .NET applications.  If so, please comment.  But this DID WORK for me. STEP 1:  FIRST WHAT "CANNOT BE" in web.config... which may solve some problems immediately -->  For me, even though everything else was good in web.config, AJAX 1.0 UpdatePanel ...

Calling data from database and declaring variable to insert the data back into another table in database
Hello, I have called data from a database which is displayed on the screen (displays a particular item - then if the product is liked they click a buy button) then on the postback I declare the variables (as below) to be added back to a different table in my database, I have a problem in that it doesn't like the way I have declared the variable in bold (below is some of the code).  The variable is initially displayed from the database as decPrice and added to another table as decCartPrice on postback.  I get this error BC30469: Reference to a non-shared member requir...

Need to get whole data from data grid view into data table
Hi I have one gridview. which have some textboxes and some are data boud columns. I want to fetch whole the data in the gridview into one data set or datatable.And then insert that dataset or datatable into one other database table. Is there any way to do that. Please reply as soon as possible at max_ashu@yahoo.com and ashish.gautam@rbs.com  regards Ashish Gautam Hi,Can you check this article helps you http://www.aspdotnetcodes.com/GridView_Insert_Edit_Update_Delete.aspx. becoz they took data input from gridview control and insert into the database then display it again. p...

Old AJAX in .NET 1.1 compatibility with .NET framework 2.0. Ajax does not work always ....................
Hello,I have converted the web site from .NET 1.1 to .NET 2.0. Some of the pages in the web site use AJAX which was developed in .NET 1.1Ajax does not work smoothly in .NET 2.0. Do I have to use ATLAS or is there any fix I can use to make the AJAX work ?Please let me know. Many thanks in advance. Nick..........

iam getting error while inserting data from datatable to table in database
 hi.........    iam trying to insert data from datatable in to database table .if the rows of datatable are having null values the insertion is being stopped pls help me regarding this here is my code.     foreach (DataRow row in dt1.Rows)        {                                   con.Open();            st...

Web resources about - Autocomplete is not work when getting data from a database table - asp.net.ajax-discussion

Autocomplete - Wikipedia, the free encyclopedia
Autocomplete , or word completion , is a feature provided by many web browsers , e-mail programs , search engine interfaces, source code editors ...

Autocomplete Me - EPIC FAIL Funny Videos and Epic Fail Funny Pictures
Click for more epic fails, videos, and wins. (Share this post and make someone ROFL!)

Autocomplete Data - Facebook-Entwickler
The value specified for the type parameter is the category of autocomplete data to retrieve, e.g. to retrieve locales, you can specify type=adlocale ...

OneKeyboard - colorful and fast custom autocomplete keyboard on the App Store on iTunes
Get OneKeyboard - colorful and fast custom autocomplete keyboard on the App Store. See screenshots and ratings, and read customer reviews.

Distraction of the Day: Someone Turned Google Autocomplete Into a Family Feud-Style Game
Say goodbye to productivity, because this game just took over your workday/life. Whenever you start typing a search into Google, the search engine ...

Selling kidneys to buy drones: the economy according to Google autocomplete
Autocomplete, the Google function that suggests full search questions based on a few words, has long served as a humorous (or harrowing) window ...

‘Google Feud’ combines popular game show with Google search autocomplete
Surely by now you've seen various jokes about Google's autocomplete feature, which suggests endings for your search terms based on what other ...

TweetDeck for Mac updated with username autocomplete, a new tweet composer, and more
... App Store . Check out 9to5Mac for more breaking coverage of Apps . What do you think? Discuss "TweetDeck for Mac updated with username autocomplete, ...

New Twitter Search Comes with Autocomplete and New Filters
Last Friday, Twitter announced what’s new on its search functionality: Search autocomplete and “People You Follow” search result. In addition ...

Google Autocomplete Is Letting The Terrorists Win
Be careful about what you search for on Google...

Resources last updated: 12/28/2015 9:23:06 AM