Ajax autocomplete method

Hi! Is there an example of doing the ajax autocomplete using sql as the back end. This is the method they have on there:publicstring[] GetCompletionList(string prefixText,int count)

{

if (count == 0)

{

count = 10;

}

Random random =newRandom();

List<string> items =newList<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();

}

Basically I'd like to change this method to be an sql server method. Thanks for any tips

This is a stripped down example that might help get you started:

 [WebMethod]
public string[] GetCustomerList(string prefixText,int count)
 {
 OleDbConnection conn =new OleDbConnection("ConnectionStringGoesHere");
 OleDbCommand cmd =new OleDbCommand();
 cmd.Connection = conn;
 cmd.CommandText =string.Format("select customer_name from customers where customer_name like '%{0}%'", prefixText);
 conn.Open();
 OleDbDataReader dr = cmd.ExecuteReader();
 List<string> result =new List<string>();
while (dr.Read())
 result.Add(dr[0].ToString());
 conn.Close();
return result.ToArray();
 }

Please note, it isn't really representative of following database best practices. It should point you in the right direction though.

Thank you! Works good!

Just make sure you tidy up the database access if you're going to use this in production. That code is vulnerable to SQL injection, which is fairly bad when it's hooked up to a web service.

Ajax auto complete extender

I am using ajax autocompleteextender. I have declared the service method in the user control itself instead of declaring it in aspx page. But the extender does not work .How can i do it? I want to declare it in ascx page,as i will be reusing the component.

my master page script
=====================
<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true" EnablePartialRendering="true"/asp:ScriptManager
my aspx page where i am inheriting masterpage
==============================================

<%@. Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="frmTestAuto.aspx.cs" Inherits="frmTestAuto" Title="Untitled Page" %>
<%@. Register src="http://pics.10026.com/?src=ctlAutoExtender.ascx" TagName="ctlAutoExtender" TagPrefix="uc1" %>
<%@. Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:ScriptManagerProxy ID="ScriptManagerProxy1" runat="server" /asp:ScriptManagerProxy>
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td>TEST AUTO PAGE</td>
</tr>
<tr>
<td>uc1:ctlAutoExtender ID="CtlAutoExtender1" runat="server" /</td>
</tr>
<tr>
<td>Footer</td>
</tr>
</table>
</asp:Content
my ascx page
=============
<%@. Control Language="C#" AutoEventWireup="true" CodeFile="ctlAutoExtender.ascx.cs" Inherits="ctlAutoExtender" %>
<%@. Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>
<asp:ScriptManagerProxy ID="ScriptManagerProxy1" runat="server"></asp:ScriptManagerProxy>
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td>
<asp:TextBox runat="server" ID="myTextBox" Width="300" />

<ajaxToolkit:AutoCompleteExtender runat="server" ID="autoComplete1" TargetControlID="myTextBox" Enabled="true"
ServiceMethod="GetCompletionList" ServicePath="ctlAutoExtender.ascx.cs" MinimumPrefixLength="3"
CompletionInterval="1000" EnableCaching="true"
CompletionSetCount="12"/>
</td>
</tr>
</table
my ascx.cs page
===============

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web.Script.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]

public partial class ctlAutoExtender : System.Web.UI.UserControl
{
protected void Page_Load(object sender, EventArgs e)
{

}

[WebMethod]
[ScriptMethod]
public static string[] GetCompletionList(string prefixText, int count)
{
string sql = String.Format("select clt_tVLongNm from mclient where clt_tVLongNm like @.companyname + '%'");

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

using (SqlConnection connection = new SqlConnection("server=192.168.2.57;uid=sa;pwd=d28rg6yp;database=fnocash_report"))

using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
command.Parameters.AddWithValue("@.companyname", prefixText);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
companyList.Add(reader.GetString(0));
}
}
}
return companyList.ToArray();
}
}

please help me out asap

Hi,

In ASP.NET, a request for a .ascx file will be?handled?by?System.Web.HttpForbiddenHandler?HttpHandler,?that's?to?say,?it's?refused.
So you can't use a method in the UserControl, I suggest trying to use a web service(.asmx) to achieve this.
Hope this helps.

Hi,

I am declaring the autocompleteextender in the user control. So, should I give service path for the extender as .asmx file in the extender?

eg:

My ascx page:

<%

@.ControlLanguage="C#"AutoEventWireup="true"CodeFile="ctlAutoExtender.ascx.cs"Inherits="ctlAutoExtender" %>

<%

@.RegisterAssembly="AjaxControlToolkit"Namespace="AjaxControlToolkit"TagPrefix="ajaxToolkit" %>

<

tablecellpadding="0"cellspacing="0"border="0"width="100%"><tr><td><asp:TextBoxrunat="server"ID="myTextBox"Width="300"/>

<ajaxToolkit:AutoCompleteExtenderrunat="server"ID="autoComplete1"TargetControlID="myTextBox"Enabled="true"ServiceMethod="GetCompletionList"ServicePath="App_Code/WebService.cs"MinimumPrefixLength="3"CompletionInterval="1000"EnableCaching="true"CompletionSetCount="12"/></td></tr>

</

table>

Yes,?like?this:??ServicePath="the relative path of the .asmx file"
Thanks, that solved my problem.

ajax and webservices...

I have created a dataset. Each method of the tableadapter shows the right data when I preview it at design time.
However, when I load the page I get the value in both dropdownlists: [method error 500]

If I step throught the code, the webmethod is not hit even though I put a breakpoint on it (or is that normal?)

Here's my code:

.ASPX

<asp:DropDownList ID="ddlCountry1" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlLocalCity" runat="server">
</asp:DropDownList>

<cc1:CascadingDropDown ID="CascadingDropDown1"
runat="server"
TargetControlID="ddlCountry1"
Category="CountryName"
prompttext="Select country"
ServicePath="LocalCities.asmx"
ServiceMethod="GetCountries">
</cc1:CascadingDropDown>
<cc1:CascadingDropDown ID="CascadingDropDown2"
runat="server"
TargetControlID="ddlLocalCity"
Category="CityName"
prompttext="Select city"
ServicePath="LocalCities.asmx"
ServiceMethod="GetCitiesForCountry"
LoadingText="Loading...">
</cc1:CascadingDropDown>

Local.vb

<WebMethod()> _
Public Function GetCountries(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim countryAdapter As CountryMainCitiesTableAdapters.CountriesTableAdapter = New CountryMainCitiesTableAdapters.CountriesTableAdapter
Dim countries As CountryMainCities.tblCountriesDataTable = countryAdapter.GetData
Dim values As New List(Of CascadingDropDownNameValue)
For Each dr As DataRow In countries
Dim CountryName As String = CType(dr("CountryName"), String)
Dim countryId As Integer = CType(dr("CountryID"), Integer)
values.Add(New CascadingDropDownNameValue(CountryName, countryId.ToString))
Next
Return values.ToArray
End Function

<WebMethod()> _
Public Function GetCitiesForCountry(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim countryId As Integer
If (Not kv.ContainsKey("CountryID") _
OrElse Not Int32.TryParse(kv("CountryID"), countryId)) Then
Return Nothing
End If
Dim cityAdapter As New DataTable

Dim adapter As CountryMainCitiesTableAdapters.CountryMainCitiesTableAdapter = New CountryMainCitiesTableAdapters.CountryMainCitiesTableAdapter
Dim cityTable As CountryMainCities.tblCountryMainCitiesDataTable = adapter.GetDataByCountryID(countryId)
Dim values As New List(Of CascadingDropDownNameValue)
For Each dr As DataRow In cityTable
values.Add(New CascadingDropDownNameValue(CType(dr("CityName"), String), dr("CityID").ToString))
Next
Return values.ToArray
End Function

Try to follow up the following suggestions to fix the famous Method Error 500 for reference.
1.Try to read this thread -http://forums.asp.net/1476672/ShowThread.aspx#1476672
2.Try to write a web method in behind code to call web service as the following
???Country:<asp:DropDownList ID="ddlCountry" runat="server">
???</asp:DropDownList>
???<AjaxControls:CascadingDropDown ID="CascadingDropDown1" runat="server" TargetControlID="ddlCountry" Category="Country" ????LoadingText="Loading....." ServiceMethod="GetCountries" BehaviorID="CascadingDropDown1" PromptText="- select country -">
???</AjaxControls:CascadingDropDown
???[WebMethod]
???[ScriptMethod]
???public CascadingDropDownNameValue[] GetCountries()
???{
???????WebService ws = new WebService();
???????return ws.GetCountries();
???}
Wish the above can help you.

The other thread did not help me a lot. But I changed some code, however I still receive the error [method 500]

and if I add the attribute
ServicePath="default.aspx.vb" to CascadingDropDown2 I receive the error [method 404]

The code-behind method GetCitiesForCountry is never hit...

DEFAULT.ASPX

<asp:DropDownList ID="ddlCountry1" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlLocalCity" runat="server">
</asp:DropDownList>

<cc1:CascadingDropDown ID="CascadingDropDown1"
runat="server"
TargetControlID="ddlCountry1"
Category="CountryID"
prompttext="Select country"
ServicePath="LocalCities.asmx"
ServiceMethod="GetCountries">
</cc1:CascadingDropDown>
<cc1:CascadingDropDown ID="CascadingDropDown2"
runat="server"
TargetControlID="ddlLocalCity"
ParentControlID="ddlCountry1"
Category="CityID"
prompttext="Select city"
ServicePath="default.aspx.vb"
ServiceMethod="GetCitiesForCountry"
LoadingText="Loading...">
</cc1:CascadingDropDown>

DEFAULT.ASPX.VB

<WebMethod(), Microsoft.Web.Script.Services.ScriptMethod()> _
Public Function GetCitiesForCountry(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
Dim CountryID As Integer
If (Not kv.ContainsKey("CountryID") _
OrElse Not Int32.TryParse(kv("CountryID"), CountryID)) Then
GlobalFunctions.ReportError("CountryID: ", "WHOOPS")
Return Nothing
End If
GlobalFunctions.ReportError("CountryID: ", CountryID.ToString)
Dim cityadapter As CountryMainCitiesTableAdapters.CountryMainCitiesTableAdapter = New CountryMainCitiesTableAdapters.CountryMainCitiesTableAdapter
Dim cities As CountryMainCities.tblCountryMainCitiesDataTable = cityadapter.GetDataByCountryID(CountryID)
Dim values As New List(Of CascadingDropDownNameValue)
For Each dr As DataRow In cities
GlobalFunctions.ReportError("CityID: ", dr("CityID").ToString)
values.Add(New CascadingDropDownNameValue(CType(dr("CityName"), String), dr("CityID").ToString))
Next
Return values.ToArray
End Function

AJAX and User controls which emit and execute Javascript

I have a user control which emits a bit of Javascript in its Render(HtmlTextWriter) method. When I use this control on a simple ASP.NET page with full page postbacks, it emits the Javascript which is then run by the browser.

However, when I naively drop this control into an UpdatePanel, when the panel gets updated the control renders the Javascript into the right place on the page, but the script is not run, so the control does not appear correct. Is it possible to achieve this somehow? (It ought to be, as we have an alternative AJAX package which does it without modification)

In order to achieve this you should not generate the script text in the Render method. Instead you should use ScriptManager.RegisterClientScriptBlock function. This will work for both postbacks as well as partial postbacks.

I've moved the script into a RegisterClientScriptBlock function, but it still appears not to be executed... here's my control's test OnPreRender() which does the registering, and I can see in the debugger it's called on both full and partial postbacks, but the Javascript alert() only fires off on full postbacks. Any ideas?

protectedoverridevoid OnPreRender(EventArgs e)

{

base.OnPreRender (e);if(Visible)

{

StringBuilder sb =newStringBuilder();StringWriter writer2 =newStringWriter(sb);

writer2.Write(

"<script type=\"text/javascript\">");

writer2.Write(

"//<![CDATA[");

writer2.Write(

"alert('hello world!');");writer2.Write("//]]>");

writer2.Write(

"</script>");

Page.ClientScript.RegisterClientScriptBlock(

this.GetType(),"__Map", sb.ToString());

}

}

Use ScriptManager.RegisterClientScript block not Page.ClientScript.registerClientScriptBlock
Sorry, my mistake. It works fine now... many thanks!

J.