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

LVL 3
xizwyckAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.