Autocomplete listbox in VB.net webapp

I am querying the database to get firstname last name and user id  and it keeps telling me lastname, firsname not in the table - this is when I am trying to concactinate the names

    Public Shared Function AutoPopulateUser() As List(Of String)
        Try
            Dim connString As String = "server=SERVER;initial catalog=DB;persist security info=False;user id=USER;password=PASS"
            Dim conn As New SqlConnection(connString)
            conn.Open()
            Dim strSQL As String = "SELECT [last_name],[first_name],[userid] FROM [DB].[TABLE].[TABLE]"
            Dim da As New SqlDataAdapter(strSQL, conn)
            Dim ds As New DataSet
            da.Fill(ds)
            'Return ds
            Dim r As DataRow
            Dim t = ds.Tables(0)
            For Each r In t.Rows
                Dim NAMES = (r("last_name" & " ," & "first_name"))
                Dim ID = (r("userid"))
                Return NAMES
            Next
            conn.Close()
        Catch ex As Exception

        End Try
    End Function

Open in new window


Also I cannot find any examples of implementing  the code on pageload but here is what I am  trying
       Try
            Dim mSource As New AutoCompleteType
            Dim suggestions As List(Of String) = AutoPopulateUser()
            Dim s As String
            Dim n As Integer
            For n = 0 To suggestions.Count - 1
                If suggestions(n).IndexOf("NAMES") = 0 Then
                    suggestions(n) = suggestions(n).Substring(3)
                End If
            Next
            mSource = Nothing
            'mSource.AddRange(suggestions.ToArray())
            'mSource.DisplayName.GetValues(List(Of String

            With TextBox1
                'MessageBox.Show(suggestions.ToString())
                .AutoCompleteType = mSource
                '.AutoCompleteMode.SuggestAppend()
                '.AutoCompleteSource = AutoCompleteSource.CustomSource
                .Visible = True
            End With
          
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

Open in new window


can  anyone help out - I would really appreciate it
LVL 6
r3nderAsked:
Who is Participating?
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.

BuggyCoderCommented:
do this in your code:-

           
List(Of String) lst=new List(Of String)()

For Each r In t.Rows
                Dim NAMES = (r.Field(Of String)("last_name") & " ," & r.Field(Of String)("first_name"))
                Dim ID = (r("userid"))
                lst.Add(NAMES)
            Next
return lst

Open in new window

0
r3nderAuthor Commented:
1st line says - lst  array bounds cannot appear inside type specifiers
and last line says - 'lst' is not declared. It may be inaccessible due to its protection level.

    Public Shared Function AutoPopulateUser() As List(Of String)(lst = New List(Of String)())
        Try
            Dim connString As String = "server=SERVER;initial catalog=DB;persist security info=False;user id=USER;password=PASS"
            Dim conn As New SqlConnection(connString)
            conn.Open()
            Dim strSQL As String = "SELECT [last_name],[first_name],[userid] FROM [DB].[TABLE].[TABLE]"
            Dim da As New SqlDataAdapter(strSQL, conn)
            Dim ds As New DataSet
            da.Fill(ds)
            'Return ds
            Dim r As DataRow
            Dim t = ds.Tables(0)
            For Each r In t.Rows
                Dim NAMES = (r.Field(Of String)("last_name") & " ," & r.Field(Of String)("first_name"))
                Dim ID = (r("userid"))
                lst.Add(NAMES)
            Next
            Return lst
            conn.Close()
        Catch ex As Exception

        End Try
    End Function

Open in new window

0
r3nderAuthor Commented:
Sorry I didnt understand the first line
    Public Shared Function AutoPopulateUser() As List(Of String)
        Try
            Dim connString As String = "server=SERVER;initial catalog=DB;persist security info=False;user id=USER;password=PASS"
            Dim conn As New SqlConnection(connString)
            conn.Open()
            Dim strSQL As String = "SELECT [last_name],[first_name],[userid] FROM [DB].[TABLE].[TABLE]"
            Dim da As New SqlDataAdapter(strSQL, conn)
            Dim ds As New DataSet
            da.Fill(ds)
            'Return ds
            Dim r As DataRow
            Dim t = ds.Tables(0)
            Dim lst = New List(Of String)
            For Each r In t.Rows
                Dim NAMES = (r.Field(Of String)("last_name") & " ," & r.Field(Of String)("first_name"))
                Dim ID = (r("userid"))
                lst.Add(NAMES)
            Next
            Return lst
            conn.Close()
        Catch ex As Exception

        End Try
    End Function

Open in new window


This works - thank you kind sir
Now what about the page load section?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

BuggyCoderCommented:
try this to delcare list:-

           
Dim lst As New List(Of String)()

            For Each r In t.Rows
                Dim NAMES = (r.Field(Of String)("last_name") & " ," & r.Field(Of String)("first_name"))
                Dim ID = (r("userid"))
                lst.Add(NAMES)
            Next

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
r3nderAuthor Commented:
Is there any other way without ajax?
0
CodeCruiserCommented:
>Autocomplete listbox

Auto complete textbox makes sense but what you mean by autocomplete listbox?
0
r3nderAuthor Commented:
textbox - sorry
0
BuggyCoderCommented:
why would you like to do it without ajax, its efficient, simpler and more over provides more user-friendlyness....

here is another good sample:-
http://www.vikramlakhotia.com/Adding_Auto_Suggest_Box_in_the_search_Box.aspx
0
r3nderAuthor Commented:
Thank you for the example but the data I have to query is in a database - not a text file. The environment we use does not allow (or should I say want) to use Ajax. Hense I have to build it like they want it built - any other suggestions?
Please under Buggy and Code I appreciate all your help.
R3nder
0
r3nderAuthor Commented:
this worked
0
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
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.