Populating a Dropdownlist from a Databse

2 posts, 0 answers
  1. Dave
    Dave avatar
    1 posts
    Member since:
    Jun 2014

    Posted 12 Jun 2014 Link to this post

    Hi, I'm trying to use the autocomplete feature to populate a dropdownlist with permit numbers from a database.  I'm using the Winforms controls and Visual Studio.  Instead of permit numbers appearing in the list I keep getting the following string:  "System.Data.Common.DataRecordInternal"  Here is the code I'm using to pull the data:

     Private Sub txtProjectNumber_TextChanged(sender As Object, e As EventArgs) Handles txtProjectNumber.TextChanged
            If txtProjectNumber.Text.Length >= 7 Then
                Dim SQLConnect As New SQLite.SQLiteConnection
                Dim SQLCommand As New SQLite.SQLiteCommand
                Dim SQLReader As SQLite.SQLiteDataReader = Nothing
                Dim strSQL As String = ""

                SQLConnect.ConnectionString = DataAccess.sqliteConnString
                SQLCommand = SQLConnect.CreateCommand

                Dim autoCompleteProjectNumber As New AutoCompleteStringCollection
                strSQL = "select distinct JobSiteNo from Projects "
                strSQL = strSQL & "where  modulename NOT in ('clrkdata','licndata' ,'stckdata')  "
                strSQL = strSQL & "and placenameprefix = '" & CommonCalls.GetCurrentPNP & "' "
                strSQL = strSQL & "and JobSiteNo not like 'BHP%' "
                strSQL = strSQL & "and JobSiteNo like " & MakeSafeSQL(txtProjectNumber.Text, True) & " "
                strSQL = strSQL & "order by JobSiteNo"
                SQLCommand.CommandType = CommandType.Text
                SQLCommand.CommandText = strSQL

                SQLReader = SQLCommand.ExecuteReader()
                txtProjectNumber.DataSource = SQLReader
                txtProjectNumber.AutoCompleteMode = AutoCompleteMode.Suggest
                txtProjectNumber.AutoCompleteDataSource = autoCompleteProjectNumber
            End If
        End Sub

    Does anyone know what I'm doing wrong here?  I've checked bunch of forums but the fixes I've found don't work.

    Thanks in Advance!!

  2. George
    George avatar
    500 posts

    Posted 17 Jun 2014 Link to this post

    Hi Dave,

    Thank you for writing.

    You need to read the data from the reader, simply passing it as a data source will not work in this case. You can read about how to retrieve the data from the reader here: Retrieving Data Using a DataReader.

    I would also recommend you not to open SQL connection on the TextChanged event handler. You can either keep the connection open or pull the data into memory and take it from there. Otherwise you risk some performance penalties.

    Let me know if you have further questions.

    Check out Telerik Analytics, the service which allows developers to discover app usage patterns, analyze user data, log exceptions, solve problems and profile application performance at run time. Watch the videos and start improving your app based on facts, not hunches.
Back to Top