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]2public string[] GetCompletionList(string prefixText,int count)3 {4if (count == 0)5 {6 count = 10;7 }89if (prefixText.Equals("xyz"))10 {11return new string[0];12 }1314string connectionString ="localhost;Initial Catalog=myDatabase; User Id=myUserID; Password=topSecrect";15 SqlConnection sqlConnection =new SqlConnection(connectionString);16 sqlConnection.Open();17 SqlCommand sqlCommand =new SqlCommand("SELECT LASTNAME + ', ' + FIRSTNAME FROM USER AS FULLNAME ORDER BY LASTNAME", sqlConnection);18 SqlDataReader sdr = sqlCommand.ExecuteReader();1920 ArrayList fullNames =new ArrayList(count);2122while (sdr.Read())23 {24if (!sdr.IsDBNull(0))25 {26char c1 = (char)sdr.GetString(0)[0];27char c2 = (char)sdr.GetString(0)[1];28char c3 = (char)sdr.GetString(0)[2];29 fullNames.Add(prefixText + c1 + c2 + c3);30 }3132 }33return (string[])fullNames.ToArray(typeof(string));34 } Please note that I do not quite understand the part in the for loop as in the original source code shown in blue below. So I am only mimicking the code.
// ArrayList items = new ArrayList(count);
// Random random = new Random();
// 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);
// }
Also please note, that the compilation of the solution with the modified code passed, and I can check out http://localhost:7870/ToolkitTests/AutoComplete.aspx just fine, but when I type in the first textbox of this AutoComplete.aspx page, no suggestions show up. The second textbox still works as usual.
The Sql query and connection string are both fine. I ran it in some other applications, they work without a problem.
Any hint is highly appreciated. Thanks.
The prefixText field are the characters that have been typed and are being queried, the count is the max number of records to return then updated with the number of records returned.
The Random... etc code, was just a way for the demo application to generate random data without attaching a database.
jguadagno:
The prefixText field are the characters that have been typed and are being queried, the count is the max number of records to return then updated with the number of records returned.
The Random... etc code, was just a way for the demo application to generate random data without attaching a database.
OK, thanks. My guess about the prefText argument was right. But, what's wrong with my modified code, which attempts to get the suggestions from the database?
Is there a way to get the suggestion list from a database? No one knows?
You have the code in the GetCompletionList. Is it not working. Remember the prefixText are the letters that the text box control has so far. So if the is a Country drop down list as user type U, the prefixText should get a U and you have to return a list of your options that start with U.
OK, I found a sample code at
http://forums.asp.net/p/1126189/1772074.aspx#1772074
By J. Shen, In order to test the dynamic SQL, I created a regular web application (NOT web service!) I just copied the part of the aforementioned code which gets the suggestions from the DB and modified like so:
public string[] GetCompletionList(string prefixText, int count)
{
if (count == 0)
{
count = 10;
}
if (prefixText.Equals("xyz"))
{
return new string[0];
}
string connectionString = "Data Source=localhost;Initial Catalog=mydatabase; User Id=myUserID; Password=TopSecret";
SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand
("SELECT TOP @.nrows LASTNAME " +
"FROM v_USER " +
"WHERE LASTNAME LIKE @.term " +
"ORDER BY LASTNAME", sqlConnection);
sqlCommand.Parameters.AddWithValue("@.nrows", count);
sqlCommand.Parameters.AddWithValue("@.term", prefixText + "%");
List<string> suggestions = new List<string>();
sqlConnection.Open();
SqlDataReader sdr = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (sdr.Read())
{
suggestions.Add(sdr[0].ToString());
}
sqlConnection.Close();
sdr.Close();
return suggestions.ToArray();
}
As you see, except the SQL, it's the same as that of J. Shen's. I call this method in Page_Load like so:
protected void Page_Load(object sender, EventArgs e)
{
string[] s = GetCompletionList("Joh", 10);
Response.Write(s.Length);
}
When I run the Web application. I always get this error:
Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '@.nrows'.
I am using SQL Server 2000, and obviously, the SQL query syntax isn't accepted by Sql Server 2000. I cannot figure out what's wrong with it. I am new to dynamic sql.
Any hint? Thanks.
The problem is with TOP @.nrows ... You can not have a variable for that. You can build the string prior to
string sql ="SELECT TOP " + count.ToString() +"LASTNAME FROM v_USER ";string sql +=" WHERE LASTNAME LIKE @.term ORDER BY LASTNAME ";SqlCommand sqlCommand =new SqlCommand(sql, sqlConnection);
Thank you very much, Joseph. That was the problem. It's working now. The sample code given by J. Shen completely misled me. I did not cast any doubt on his code because I took it for granted that it was a working sample.
I am glad it worked. Please mark it as "Answered" so that others will know the solution.
Hi All,
I have been trying to replicate this post in VB.net and I "think" I am almost there. I am stuck on a couple of small pieces and I'm begging for help. I have been trying to get the AJAX autocomplete to work off of a SQL database for many hours before I ran into this post which seems promising. I feel I'm at my ends.
I'm having trouble with the following lines, I'm tried to figure out what the code was trying to do but this is what I could come up with and its not working:
----------------------------------
Dim suggestionsAs ListItemCollection =New ListItemCollection(OfString)
While (reader.Read())
suggestions.Add(reader[0].ToString())
EndWhile
Return suggestions.ToArray
----------------------------------
Thanks in advance, I truely appreciate it.
Tim
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Collections.Generic
Imports System.Data.Sql
Imports System.Data.SqlClient
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
PublicClass AutoComplete
Inherits System.Web.Services.WebService
<WebMethod()> _
PublicFunction GetCompletionList(ByVal prefixTextAsString,ByVal countAsInteger)AsString()
If (count = 0)Then
count = 10
EndIf
Dim connAs SqlConnection
Dim commAs SqlCommand
Dim readerAs SqlDataReader
Dim connectionstringAsString = ConfigurationManager.ConnectionStrings("Magic").ConnectionString
Dim sqlAsString ="SELECT TOP " + count.ToString() +"LASTNAME FROM v_USER "
sql +=" WHERE LASTNAME LIKE @.term ORDER BY LASTNAME "
conn =New SqlConnection(connectionstring)
comm =New SqlCommand(sql, conn)
comm.Parameters.AddWithValue("@.nrows", count)
comm.Parameters.AddWithValue("@.term", prefixText +"%")
Dim suggestionsAs ListItemCollection =New ListItemCollection(OfString)
conn.Open()
reader = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection
While (reader.Read())
suggestions.Add(reader[0].ToString())
EndWhile
conn.Close()
reader.Close()
Return suggestions.ToArray
EndFunction
EndClass
With comments
''' Creates a list array
Dim suggestionsAs ListItemCollection =New ListItemCollection(OfString)
''' Opens the connection
conn.Open()
''' Creates a data reader
reader = comm.ExecuteReader(System.Data.CommandBehavior.CloseConnection
''' Loops through the data reader
While (reader.Read())
''' Adds the first column to the suggestions array
''' note: in VB this should read
suggestions.Add(reader(0).ToString())
EndWhile
''' close the objects
conn.Close()
reader.Close()
''' return the suggestion
Return suggestions.ToArray
Almost there...
I have everything compiling correctly, Visual Studio 05 isn't giving me any error messages but when I type in the textbox nothing happens. It must be linked incorrectly. I know the SQL statement works correctly because I ran it in Query Analyzer, and I know the connection string works because I have other data pulling so I think the function GetCompletionList isn't being passed any parameters for it to return a result.
If someone could explain the mapping so I can go back and verify everything on my end that would be good. Currently I have the autocompleteextender controlID set to my textbox. Other than that, the AJAX website knows to use the AutoComplete.asmx which then points to the App_Code/Autocomplete.vb right? Or does this have to be set manually or something.
Idea's?
Thanks!
Tim
The autocompleteextender needs to be configured like this
<ajaxToolkit:AutoCompleteExtender runat="server" ID="autoComplete1" TargetControlID="myTextBox" ServiceMethod="GetCompletionList" ServicePath="AutoComplete.asmx" MinimumPrefixLength="2" CompletionInterval="1000" EnableCaching="true" CompletionSetCount="20" CompletionListCssClass="autocomplete_completionListElement" CompletionListItemCssClass="autocomplete_listItem" CompletionListHighlightedItemCssClass="autocomplete_highlightedListItem" DelimiterCharacters=";, :"> <Animations> <OnShow> ... </OnShow> <OnHide> ... </OnHide> </Animations></ajaxToolkit:AutoCompleteExtender>
Note, the ServiceMethod and ServicePath properties. They need to be pointed to whereever your AutoComplete is.
