locked
Display a Value Associated with List Box Selected Item to a TextBox RRS feed

  • Question

  • Hi, I am relatively new to Visual Studio or visual basic, and I'm trying to create a kind of filter application. I'm trying to display a value associated with a listbox selected item in a textbox where the associated value will be fetched from an access database.

    My code is working but not exactly the way I want it to. You see, it only displays correctly for the first time I select an item in the listbox. For example, when I select "George", "Tooling" appears in the textbox. But when I select "Washington" next, the value in the textbox does not change.

    I hope you can help me. Or if there's another way around it. Thank you.

    Sample Table from Database: 
    Firstname Lastname
    George Tooling
    Washington Seeker

    And Here's my code so far:

    Class MainWindow
        Dim con As New OleDb.OleDbConnection
        Dim dbProvider As String
        Dim dbsource As String
        Dim dbfolder As String
        Dim db As String
        Dim fulldbpath As String
        Dim SampleDataset As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        Dim SelectedLB As String

    //Adding Listbox Items
    Private Sub Listbox_Initialized(sender As Object, e As EventArgs) Handles Listbox.Initialized
            Listbox.Items.Add("George")
            Listbox.Items.Add("Washington")
    End Sub

    //Code for when an item in the listbox is selected   
    Private Sub Listbox_SelectionChanged(sender As Object, e As SelectionChangedEventArgs) Handles Listbox.SelectionChanged
    //Selected item stored in a variable
    SelectedLB = Listbox.SelectedItem()

    //establishing connection to the database
            dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    db = "/SampleDB.mdb"
            dbfolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
            fulldbpath = dbfolder & db
            dbsource = "Data Source = " & fulldbpath
            con.ConnectionString = dbProvider & dbsource
            con.Open()

    //Query based on selected item       
            sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    da = New OleDb.OleDbDataAdapter(sql,con)
            da.Fill(SampleDataset,"SampleTable")
            con.Close()

    //Sending a single value from the Filtered Table to textbox 
            TextBox.Text = SampleDataset.Tables("SampleTable").Rows(0).Item(1)
    End Sub


    Wednesday, November 25, 2015 4:14 PM

Answers

  • What is actually happening is that data adapter is filling the same table instance each time without clearing the table first.  So on the second call, the new item is added but it is in Rows(1), not Rows(0).  So it would just be a matter of clearing the table before each fill:

    sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    da = New OleDb.OleDbDataAdapter(sql,con)
    SampleDataset.Tables.Item("SampleTable").Clear()
    da.Fill(SampleDataset,"SampleTable")
    con.Close()

    That said, I don't think the dataset and table adapter are necessary.  The way the code is being executed it would be more efficient to just use an OleDbCommand object and execute a scalar query to get back the single result value (if any).

     

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Proposed as answer by Mr. Monkeyboy Wednesday, November 25, 2015 6:37 PM
    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:19 PM
  • Thank you sir for the suggestion. I'll look into this and try it. 
    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:15 PM
  • What is actually happening is that data adapter is filling the same table instance each time without clearing the table first.  So on the second call, the new item is added but it is in Rows(1), not Rows(0).  So it would just be a matter of clearing the table before each fill:

    sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    da = New OleDb.OleDbDataAdapter(sql,con)
    SampleDataset.Tables.Item("SampleTable").Clear()
    da.Fill(SampleDataset,"SampleTable")
    con.Close()

    That said, I don't think the dataset and table adapter are necessary.  The way the code is being executed it would be more efficient to just use an OleDbCommand object and execute a scalar query to get back the single result value (if any).

     

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Does SampleDataset.Tables.Item("SampleTable") exist prior to first run of code? If not will error occur trying to clear non-existant item?

    La vida loca

    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:37 PM
  • ...

    Does SampleDataset.Tables.Item("SampleTable") exist prior to first run of code? If not will error occur trying to clear non-existant item?

    La vida loca

    The way the code is written, no it does not exist and yes there will be an exception on the fist pass. Good catch. It would be necessary to either add the table initially when the dataset is created or check to see if the table exists before clearing it.

    In VS2015 we can just add a ? to the . before clear.

    SampleDataset.Tables.Item("SampleTable")?.Clear()


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:45 PM
    Wednesday, November 25, 2015 6:40 PM

All replies

  • If da.Fill fills SampleDatasets "SampleTable" then does SampleDatasets "SampleTable" then have new entries added to it everytime it is filled?

    I think you are adding new tables constantly with the same name somehow since no tables are ever removed maybe. But I'm not certain about that.

    I know the sample code at this link DataTableCollection.Remove Method (DataTable) has a loop for removing tables. Loop code is below.

    On the other hand this "TextBox.Text = SampleDataset.Tables("SampleTable").Rows(0).Item(1)" always indexes Rows(0) and maybe what you are doing is adding a row to the SampleDataset table "SampleTable" which would mean that the new name is in Rows(1).

    I'm guessing if you clear all Tables from SampleDataset, if it contains any, prior to loading SampleDataset again the issue will go away.

    Also you should avoid using controls class names fro the actual name of a control. Which is why when controls are dragged onto a Form their names increment by 1 as each new one is dragged onto a Form. You use TextBox and ListBox which are both class names of each control which isn't really a good idea.

           ' remove all tables
           ' check if table can be removed and then
           ' remove it, cannot use a foreach when
           ' removing items from a collection
           Do While (dataSet.Tables.Count > 0)
               Dim table As DataTable = dataSet.Tables(0)
               If (dataSet.Tables.CanRemove(table)) Then
                   dataSet.Tables.Remove(table)
               End If
           Loop
    


    La vida loca

    Wednesday, November 25, 2015 5:40 PM
  • Thank you sir for the suggestion. I'll look into this and try it. 
    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:15 PM
  • What is actually happening is that data adapter is filling the same table instance each time without clearing the table first.  So on the second call, the new item is added but it is in Rows(1), not Rows(0).  So it would just be a matter of clearing the table before each fill:

    sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    da = New OleDb.OleDbDataAdapter(sql,con)
    SampleDataset.Tables.Item("SampleTable").Clear()
    da.Fill(SampleDataset,"SampleTable")
    con.Close()

    That said, I don't think the dataset and table adapter are necessary.  The way the code is being executed it would be more efficient to just use an OleDbCommand object and execute a scalar query to get back the single result value (if any).

     

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Proposed as answer by Mr. Monkeyboy Wednesday, November 25, 2015 6:37 PM
    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:19 PM
  • What is actually happening is that data adapter is filling the same table instance each time without clearing the table first.  So on the second call, the new item is added but it is in Rows(1), not Rows(0).  So it would just be a matter of clearing the table before each fill:

    sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    da = New OleDb.OleDbDataAdapter(sql,con)
    SampleDataset.Tables.Item("SampleTable").Clear()
    da.Fill(SampleDataset,"SampleTable")
    con.Close()

    That said, I don't think the dataset and table adapter are necessary.  The way the code is being executed it would be more efficient to just use an OleDbCommand object and execute a scalar query to get back the single result value (if any).

     

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Does SampleDataset.Tables.Item("SampleTable") exist prior to first run of code? If not will error occur trying to clear non-existant item?

    La vida loca

    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:46 PM
    Wednesday, November 25, 2015 6:37 PM
  • ...

    Does SampleDataset.Tables.Item("SampleTable") exist prior to first run of code? If not will error occur trying to clear non-existant item?

    La vida loca

    The way the code is written, no it does not exist and yes there will be an exception on the fist pass. Good catch. It would be necessary to either add the table initially when the dataset is created or check to see if the table exists before clearing it.

    In VS2015 we can just add a ? to the . before clear.

    SampleDataset.Tables.Item("SampleTable")?.Clear()


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Fourth2018 Wednesday, November 25, 2015 6:45 PM
    Wednesday, November 25, 2015 6:40 PM
  • Hi,

    Thank you for the explanation.

    So I tried including the clear dataset table code but an error is appearing every time I try to click on the list box. Error: "An unhandled exception of type System.NullReferenceException occurred." -- which is probably referring to SampleDataset

    I'm not sure why this is showing since I declared SampleDataset as a New DataSet up above.   
    Also, I'm rather interested on how I could make this more efficient. You mentioned OleDbCommand object but I'm not sure yet how to utilize them. I was wondering if you could teach me a little. Thanks!

    Wednesday, November 25, 2015 6:43 PM
  • That totally worked! Thanks a lot!
    Wednesday, November 25, 2015 6:46 PM
  • Hi,

    Thank you for the explanation.

    So I tried including the clear dataset table code but an error is appearing every time I try to click on the list box. Error: "An unhandled exception of type System.NullReferenceException occurred." -- which is probably referring to SampleDataset

    I'm not sure why this is showing since I declared SampleDataset as a New DataSet up above.   
    Also, I'm rather interested on how I could make this more efficient. You mentioned OleDbCommand object but I'm not sure yet how to utilize them. I was wondering if you could teach me a little. Thanks!

    Sorry about the error; but it looks like you figured it out from the other posts.

    As for the Command object, you could change your code to:

    Dim sql = "SELECT * FROM SampleTable WHERE (Firstname='" & SelectedLB & "')"
    Using cmd As New OleDb.OleDbCommand(sql, con)
        Dim result = cmd.ExecuteScalar
        If result IsNot Nothing Then
            TextBox1.Text = result
        Else
            TextBox1.Text = "(not found)"
        End If
    End Using
    con.Close()

    Since you are only interested in the first column of the first row in the result, ExecuteScalar is the perfect method to use because that is exactly what it returns.

    The data adapters and data sets are good for working with data offline, caching data to perform multiple actions against it, and presenting data to the user via data binding.  But when you only need to work with one record (or a single field) at a time and aren't necessarily going to work with the entire table, then using Command objects to just get a record set to read through is generally more efficient.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Wednesday, November 25, 2015 10:18 PM
  • Tried this out and I'm getting the same results! Thanks for the tip! Since it's more efficient, I'm sticking to this method.
    • Edited by Fourth2018 Monday, November 30, 2015 5:26 PM
    Monday, November 30, 2015 5:26 PM