Remove From My Forums

Implement AJAX AutoComplete from SQL database using VB

Question

User320184449 posted

I'm trying to tie the AJAX autocomplete control to a textbox and pull the results from an sql database using VB. Anyone know how to do this? I found plenty of examples in c but I need to do it in VB. Here's code for C I guess but I can't figure out how to convert it to VB. If you know of a better way to do this, please let me know. Thanks in advance..

[WebMethod]
public string[] GetCountryInfo(string prefixText)
{
 int count = 10; 
 string sql = "Select * from Country Where Country_Name like @prefixText";
 SqlDataAdapter da = new SqlDataAdapter(sql,”Your Connection String Comes Here”));
 da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText+ "%"; 
 DataTable dt = new DataTable(); 
 da.Fill(dt); 
 string[] items = new string[dt.Rows.Count];
 int i = 0; 
 foreach (DataRow dr in dt.Rows) 
 { 
  items.SetValue(dr["Country_Name"].ToString(),i); 
  i++; 
 } 
 return items; 
}

Wednesday, October 15, 2008 12:40 PM

Answers

User320184449 posted

Per a request, here's what I have:

==========================================

Default.aspx:

<asp:ScriptManager ID="ScriptManager1" runat="server">
    <Services>
        <asp:ServiceReference Path="AutoComplete.asmx"/>
    </Services>
    </asp:ScriptManager>

<asp:TextBox ID="txtSearch" runat="server" Width="120px"></asp:TextBox>

            <cc1:AutoCompleteExtender
                runat="server" 
                ID="autoComplete1" 
                TargetControlID="txtSearch"
                ServicePath="AutoComplete.asmx" 
                ServiceMethod="GetCompletionList"
                MinimumPrefixLength="2" 
                CompletionInterval="1000"
                EnableCaching="true"
                CompletionSetCount="12"
                CompletionListItemCssClass="xsmallfont" />

==========================================

AutoComplete.asmx:

 <%@ WebService Language="VB" CodeBehind="~/App_Code/AutoComplete.vb" Class="AutoComplete" %>

==========================================

App_Code/AutoComplete.vb:

Imports System
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.Script.Services
Imports System.Configuration

<WebService(Namespace:="http://www.mydomainname.com/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    <ScriptMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("myconnectionstring").ConnectionString)
        Dim cmd As New SqlCommand("SELECT DISTINCT TOP(@nrows) ModelName FROM mytablename WHERE mycolumnname like @term", cn)
        cmd.Parameters.AddWithValue("nrows", count)
        cmd.Parameters.AddWithValue("term", prefixText & "%")
        Dim suggestions As New List(Of String)()
        cn.Open()
        Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.Read()
                suggestions.Add(dr(0).ToString())
            End While
        End Using
        Return suggestions.ToArray()
    End Function

End Class

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, April 21, 2009 9:25 PM

All replies

0

Sign in to vote

User-837620913 posted

In the future, use this free online code translator.

    <WebMethod()>  _
    Public Function GetCountryInfo(ByVal prefixText As String) As String()
        Dim count As Integer = 10
        Dim sql As String = "Select * from Country Where Country_Name like @prefixText"
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, Your, Connection, String, Comes, Here)
        da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = (prefixText + "%")
        Dim dt As DataTable = New DataTable
        da.Fill(dt)
        Dim items() As String = New String((dt.Rows.Count) - 1) {}
        Dim i As Integer = 0
        For Each dr As DataRow In dt.Rows
            items.SetValue(dr("Country_Name").ToString, i)
            i = (i + 1)
        Next
        Return items
    End Function

Wednesday, October 15, 2008 12:44 PM
0

Sign in to vote

User320184449 posted

Thanks for the link man, that's easier than this SharpDevelop 3.0 I've been using. I'm getting a compilation error:

Compiler Error Message: BC30002: Type 'SqlDataAdapter' is not defined.

Anyone know if that translated correctly? If so, how do I set the sqlDataAdapter the right way?

Dim da As SqlDataAdapter = New SqlDataAdapter(sql, ??????)

Wednesday, October 15, 2008 5:01 PM
0

Sign in to vote

User320184449 posted

Got this figured out. Anyone needs it, email me and I'll post the code..

Thursday, October 16, 2008 4:52 PM

User320184449 posted

Per a request, here's what I have:

==========================================

Default.aspx:

<asp:ScriptManager ID="ScriptManager1" runat="server">
    <Services>
        <asp:ServiceReference Path="AutoComplete.asmx"/>
    </Services>
    </asp:ScriptManager>

<asp:TextBox ID="txtSearch" runat="server" Width="120px"></asp:TextBox>

            <cc1:AutoCompleteExtender
                runat="server" 
                ID="autoComplete1" 
                TargetControlID="txtSearch"
                ServicePath="AutoComplete.asmx" 
                ServiceMethod="GetCompletionList"
                MinimumPrefixLength="2" 
                CompletionInterval="1000"
                EnableCaching="true"
                CompletionSetCount="12"
                CompletionListItemCssClass="xsmallfont" />

==========================================

AutoComplete.asmx:

 <%@ WebService Language="VB" CodeBehind="~/App_Code/AutoComplete.vb" Class="AutoComplete" %>

==========================================

App_Code/AutoComplete.vb:

Imports System
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.Script.Services
Imports System.Configuration

<WebService(Namespace:="http://www.mydomainname.com/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
<System.Web.Script.Services.ScriptService()> _
Public Class AutoComplete
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    <ScriptMethod()> _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("myconnectionstring").ConnectionString)
        Dim cmd As New SqlCommand("SELECT DISTINCT TOP(@nrows) ModelName FROM mytablename WHERE mycolumnname like @term", cn)
        cmd.Parameters.AddWithValue("nrows", count)
        cmd.Parameters.AddWithValue("term", prefixText & "%")
        Dim suggestions As New List(Of String)()
        cn.Open()
        Using dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            While dr.Read()
                suggestions.Add(dr(0).ToString())
            End While
        End Using
        Return suggestions.ToArray()
    End Function

End Class

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Tuesday, April 21, 2009 9:25 PM

0

Sign in to vote

User619577703 posted

What are the <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link href="file:///C:%5CUsers%5Cjkruse%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml" rel="File-List"><link href="file:///C:%5CUsers%5Cjkruse%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx" rel="themeData"><link href="file:///C:%5CUsers%5Cjkruse%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml" rel="colorSchemeMapping"><style>  </style>prerequisites to getting this to work? I know nothing about AJAX, is that something I have to install/configure on my webserver? Are the above 3 pieces the only things I need to get this running?

Thanks,

Jonathan

Thursday, May 21, 2009 10:03 AM
0

Sign in to vote

User-2036699350 posted

Hey,

Just wanted to say thanks for this post, code works like a charm.

Cheers

Sunday, October 11, 2009 1:05 PM

Answered by:

Implement AJAX AutoComplete from SQL database using VB

Question

Answers

All replies