[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Help with autocomplete extender with SQL DB

Ok, I give up. I've googled and googled and I cannot find a solution to this error: value of type '1-dimensional array of string' cannot be converted to 'string' which occurs at: Return suggestions.ToArray(). If I change to Return suggestions.ToString(), the web service doesn't work. Am I missing something?
<%@ WebService Language="VB" Class="autocomplete" %>
 
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Collections.Generic
Imports System.Text
 
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class autocomplete
    Inherits System.Web.Services.WebService
    
    <WebMethod()> _
    Public Shared Function GetTagList(ByVal prefixText As String) As String
        Dim conn As String = ConfigurationManager.ConnectionStrings("GTDB").ConnectionString
        Dim connection As New SqlConnection(conn)
 
        Dim suggestions As New List(Of String)
 
        Dim sql As New SqlCommand("Select_Tags", connection)
        sql.CommandType = CommandType.StoredProcedure
        sql.Parameters.AddWithValue("@prefixText", prefixText)
        
        connection.Open()
        
        Dim ReaderData As SqlClient.SqlDataReader
        ReaderData = sql.ExecuteReader()
        
        Do While ReaderData.Read
            suggestions.Add(ReaderData(0).ToString())
        Loop
        
        ReaderData.Close()
        connection.Close()
        
        Return suggestions.ToArray()
    End Function
End Class
 
 
And the ASPX page...
 
<asp:TextBox ID="TextBox1" runat="server" AutoCompleteType="None"></asp:TextBox>
            <ajax:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TextBox1"
                 ServiceMethod="GetTagList" ServicePath="~/autocomplete.asmx"
                 MinimumPrefixLength="2" CompletionInterval="1000"
                 EnableCaching="true" CompletionSetCount="12">
            </ajax:AutoCompleteExtender>

Open in new window

0
xizwyck
Asked:
xizwyck
  • 5
1 Solution
 
xizwyckAuthor Commented:
hello?
0
 
xizwyckAuthor Commented:
Is there a black-list or do ask really hard questions??? - Just trying to understand why no one has even tried to respond on this question or many others I've asked.

I made some minor changes... at least I'm not getting the error anymore but now, the autocomplete simply won't execute.

Note: I've tested the stored proc, tested the function... it just won't work for some reason.
<%@ WebService Language="VB" Class="autocomplete" %>
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
 
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class autocomplete
    Inherits System.Web.Services.WebService
    
    <WebMethod()> _
    Public Function GetTagList(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As String = ConfigurationManager.ConnectionStrings("GTDB").ConnectionString
        Dim connection As New SqlConnection(conn)
 
        Dim suggestions As New List(Of String)
 
        Dim sql As New SqlCommand("Select_Tags", connection)
        sql.CommandType = CommandType.StoredProcedure
        sql.Parameters.AddWithValue("@prefixText", prefixText)
        
        connection.Open()
        
        Dim ReaderData As SqlClient.SqlDataReader
        ReaderData = sql.ExecuteReader()
 
        Do While ReaderData.Read
            suggestions.Add(ReaderData(0).ToString())
        Loop
        
        ReaderData.Close()
        connection.Close()
        
        Return suggestions.ToArray
    End Function
End Class

Open in new window

0
 
xizwyckAuthor Commented:
Making an even 200 points.
0
 
xizwyckAuthor Commented:
or so...
0
 
xizwyckAuthor Commented:
Nevermind, found my own solution. This works with a SQL database using a stored procedure.
Webservice:
<%@ WebService Language="VB" Class="autocomplete" %>
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
 
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class autocomplete
    Inherits System.Web.Services.WebService
    
    <WebMethod()> _
    Public Function GetTagList(ByVal prefixText As String, ByVal count As Integer) As String()
        Dim conn As String = ConfigurationManager.ConnectionStrings("GTDB").ConnectionString
        Dim connection As New SqlConnection(conn)
 
        Dim suggestions As New List(Of String)
 
        Dim sql As New SqlCommand("Select_Tags", connection)
        sql.CommandType = CommandType.StoredProcedure
        sql.Parameters.AddWithValue("@prefixText", prefixText)
        
        connection.Open()
        
        Dim ReaderData As SqlClient.SqlDataReader
        ReaderData = sql.ExecuteReader()
 
        Do While ReaderData.Read
            suggestions.Add(ReaderData(0).ToString())
        Loop
        
        ReaderData.Close()
        connection.Close()
        
        Return suggestions.ToArray
    End Function
End Class
 
<!----------------------------------->
 
ASPX Page (part)
 
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <ajax:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="TextBox1"
                 ServiceMethod="GetTagList" ServicePath="~/autocomplete.asmx"
                 MinimumPrefixLength="1" CompletionInterval="500"
                 EnableCaching="true" CompletionSetCount="10">
            </ajax:AutoCompleteExtender>

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now