Solved

Autocomplete listbox in VB.net webapp

Posted on 2012-04-05
11
569 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:r3nder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37813313
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
 
LVL 6

Author Comment

by:r3nder
ID: 37813690
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
 
LVL 6

Author Comment

by:r3nder
ID: 37813744
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 20

Accepted Solution

by:
BuggyCoder earned 500 total points
ID: 37813748
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
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37813772
0
 
LVL 6

Author Comment

by:r3nder
ID: 37814173
Is there any other way without ajax?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37814645
>Autocomplete listbox

Auto complete textbox makes sense but what you mean by autocomplete listbox?
0
 
LVL 6

Author Comment

by:r3nder
ID: 37814947
textbox - sorry
0
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 37814984
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
 
LVL 6

Author Comment

by:r3nder
ID: 37815047
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
 
LVL 6

Author Closing Comment

by:r3nder
ID: 37852036
this worked
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question