Question How to access listbox item row?

[Peinecone]

I know this is a simple idea, but I am new to vb.net and using a database. My only experience with vb is VBA in excel. Here is what i am trying to do.

I have a listbox (lstName) that is populated by column 3 in my database. I have 3 textboxs that I want to display the other columns in the table when the user selects an item in the listbox. i know how to get the index of the listbox item, but how can I relate that to the row on the table. And then the textboxes with have the data from the other columns. Below is the code I use on Form_Load.

Public Class Form1

    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim dt As New DataSet
    Dim db As New OleDb.OleDbDataAdapter
    Dim sql As String


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MainDatabaseDataSet.SyscoM' table. You can move, or remove it, as needed.
        Me.SyscoMTableAdapter.Fill(Me.MainDatabaseDataSet.SyscoM)

        con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = D:\Ben's Documents\Work Things\Cost Manangment System\MainDatabase.accdb"
        con.Open()

        sql = "SELECT * FROM SyscoM"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "SyscoPrice")

        con.Close()

        MaxRows = ds.Tables("SyscoPrice").Rows.Count

        For inc As Integer = 0 To MaxRows - 1
            lstName.Items.Add(ds.Tables("SyscoPrice").Rows(inc).Item(3))


        Next inc
        lstName.Sorted = True
        inc = -1

    End Sub

 

[MattP]

Binding the listbox would be a good start rather than looping through the rows and adding them to the items collection. By using a BindingSource I'm able to cast the Current DataRowView as a DataRow to get at the contents.

Here's an example using SQL Server but you could easily modify it to use Access.

Public Class Form1

	Dim bs As New BindingSource

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles MyBase.Load

		Dim ds As New DataSet

		Using cn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Company;Integrated Security=True")
			Dim da As New SqlDataAdapter("SELECT * FROM employee", cn)
			da.Fill(ds, "TableData")
		End Using

		bs.DataSource = ds.Tables("TableData")
		bs.Sort = "emp_ssn DESC"
		ListBox1.DataSource = bs
		ListBox1.DisplayMember = "emp_first_name"
		ListBox1.ValueMember = "emp_ssn"

	End Sub

	Private Sub ListBox1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles ListBox1.DoubleClick

		Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
		MessageBox.Show(dr.Item("emp_first_name") & " " & dr.Item("emp_last_name"))

	End Sub

	Private Sub uxSort_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles uxSort.Click

		If bs.Sort = "emp_ssn DESC" Then
			bs.Sort = "emp_ssn ASC"
		Else
			bs.Sort = "emp_ssn DESC"
		End If

	End Sub
End Class

Edit: Added a button to change the sort direction of the data. Notice that the data in the ListBox is updated automatically and that regardless of the sort direction you're still getting the correct row.

 

[Peinecone]

Ok, that looks good. I will have to wait till I get home to try this out.
But then if I want a value in a textbox, the code in Private Sub ListBox1_DoubleClick would be ?
Textbox1.value = dr.Item("Column1")

 

[MattP]

Ok, that looks good. I will have to wait till I get home to try this out.
But then if I want a value in a textbox, the code in Private Sub ListBox1_DoubleClick would be ?
Textbox1.value = dr.Item("Column1")

You may want to use the SelectedIndexChanged depending on what you're actually trying to accomplish.

If you wanted to put a value in a textbox you'd set its Text property to the Item.

TextBox1.Text = dr.Item("Column1")

 

[Peinecone]

Matt, thanks for your help. I used your suggestion on the BindingSource and it did exactly what I needed.

One question: Why on the Form Load did you assign the "ListBox1.ValueMember = "emp_ssn""? What does the ValueMember do and why is it different from the DisplayMember?

 

[Peinecone]

I have another problem. When the user selects an item in the list box, the textboxs are populated by data from the other columns. This now works great. But I also have a combobox that is populated also. The combobox itself gets it's list from another table. I have managed to get the databinding so that I can populate the combobox at load with the correct text, but when the user selects the value in the listbox, the combobox only displays the ID of the value and not the name.
Here is what I have for code so far.

Public Class Form1

    Dim inc As Integer
    Dim MaxRows As Integer

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim dt As New DataSet
    Dim db As New OleDb.OleDbDataAdapter
    Dim bs As New BindingSource
    Dim bsCat As New BindingSource
    Dim daCat As New OleDb.OleDbDataAdapter
    Dim dsCat As New DataSet

    Dim sql As String


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = D:\Ben's Documents\Work Things\Cost Manangment System\MainDatabase.accdb"
        con.Open()

        sql = "SELECT * FROM SyscoM"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "SyscoPrice")

        con.Close()

        MaxRows = ds.Tables("SyscoPrice").Rows.Count
        inc = -1

        bs.DataSource = ds.Tables("SyscoPrice")
        bs.Sort = "ProductName"
        lstName.DataSource = bs
        lstName.DisplayMember = "ProductName"
        lstName.ValueMember = "ID"

        con.Open()
        sql = "SELECT * FROM PurchaseCategories"
        daCat = New OleDb.OleDbDataAdapter(sql, con)

        daCat.Fill(dsCat, "Category")

        con.Close()

        bsCat.DataSource = dsCat.Tables("Category")
        bsCat.Sort = "PurchaseCategory"
        cboCategory.DataSource = bsCat
        cboCategory.DisplayMember = "PurchaseCategory"
        cboCategory.ValueMember = "ID"




    End Sub

    Private Sub lstName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstName.SelectedIndexChanged

        Dim dr As DataRow = DirectCast(bs.Current, DataRowView).Row
        txtPC.Text = dr.Item("ProductCode")
        txtPrice.Text = dr.Item("Price")
        cboCategory.Text = dr.Item("Category")

    End Sub


End Class

 

[cjard]

I have another problem. When the user selects an item in the list box, the textboxs are populated by data from the other columns. This now works great. But I also have a combobox that is populated also. The combobox itself gets it's list from another table. I have managed to get the databinding so that I can populate the combobox at load with the correct text, but when the user selects the value in the listbox, the combobox only displays the ID of the value and not the name.

The combobox needs to be set up thus:

.DataSource = the other table
.DisplayMember = the name of the column in "the other table" that shows a friendly text
.ValueMember = the name of the column in "the other table" that holds the ID you want to store in the main table
SelectedValue binding = is bound to the main table, column full of IDs


I usually do this visually with a few clicks. Looking at your code it seems you have not performed the last step - telling the combo that its SelectedValue needs binding to the SyscoPrice table


If you do get tired of typing all this by hand, let us know - you can have Visual Studio do it in the same way that it does all your GUI layout code for you

 

[Peinecone]

The combobox needs to be set up thus:

.DataSource = the other table
.DisplayMember = the name of the column in "the other table" that shows a friendly text
.ValueMember = the name of the column in "the other table" that holds the ID you want to store in the main table
SelectedValue binding = is bound to the main table, column full of IDs

How would I code the selectedvalue binding?

I do know about the Visual Studio and the wizards, but I am trying to see how this works first. Thanks for the tip though.

Edit: I figured it out. I actually did it using the code, and then also with Visual Studio. All I needed to change in the code was

cboCategory.Text = dr.Item("Category")

to this

cboCategory.SelectedValue = dr.Item("Category")

Can someone tell me if it makes any difference using code or Visual Studio. I assume it is easier to debug using code.

 

[MattP]

Matt, thanks for your help. I used your suggestion on the BindingSource and it did exactly what I needed.

One question: Why on the Form Load did you assign the "ListBox1.ValueMember = "emp_ssn""? What does the ValueMember do and why is it different from the DisplayMember?

I'm assuming this question was answered in cjard's post.

In my case I did it because the ssn is a unique column in my test table that has FK relationships to other tables I may be using.

 

[cjard]

What does the ValueMember do and why is it different from the DisplayMember?

Display = what is shown
Value = what is stored

used for those situations where you want to show Texas, but store TX. You have a table:

ShowState, StoreState
Florida, FL
Texas, TX
Washington, WA

etc

 

[cjard]

How would I code the selectedvalue binding?

Not a clue. I get the IDE to do it for me, because I have a particularly strong aversion to the mind-numbind tedium of writing this sort of code by hand

I do know about the Visual Studio and the wizards, but I am trying to see how this works first. Thanks for the tip though.

Did you learn about how every system on a car works before you learned to drive? Probably not. Don't bog yourself down in the details, it's ultimately not constructive because you lose sight of the bigger picture. Leave the wizards to generate the tedious code; after all, you leave the compiler to generate the MSIL, and you leave the framework to generate the machine code that the CPU runs.. Oh, and most the people that I saw who took exception to the notion of using a wizard, would then happily use the Forms Designer to lay out their UI.. There's an inherent hypocrisy in it all through, because VS isnt doing anything magical when a form is laid out; it's just writing code in the background.. locations, anchors, properties, bindings etc etc

Can someone tell me if it makes any difference using code or Visual Studio. I assume it is easier to debug using code.

Given that visual studio writes the code, and stores it in the Form1.Designer.vb file (untick Just My Code in the debugging options, enabled Show All Files in the solution explorer and check it out) what it basically comes down to is:
who writes the code
where it is written

In one case it is VS/the .Designer file. In the other it is You/Whatever File. Sometimes VS generates massive amounts of good code; more than you or I could do in a week, and it works, and it does things in a proper way. That's why we use Visual Studio - essentially just getting a computer to write the boring code for us..It's not so much of an interesting learning exercise as skipping an unnecesary evil

If you untick Just My Code then you can debug the code that VS has written, but there's rarely a point to doing so; that code rarely has bugs because if it did, Microsoft would never hear the end of the complaints. I untick it as a matter of course so that I can single step and look at parameter values just before a query is run against a db for example, but I've never had need to edit any generated code*



* This is a fib; I edit the designer generated code when I want to do something like have a base class for tableadapters or datasets. Damn nuisance to remember to keep changing it though, I'll warrant