How to get data from DataGridView to a Listbox? 1

[Rick_Stanich]

Not wanting to type a long-drawn-out subject, I am new to VB.net, and it's been many many years since I was into VB6.
On Form1 I have a Combobox and a Listbox, Form2 is my DataGridView.
With help from others on forums, I have been able to populate the Combobox with a single column of data from the DataGridView.

https://www.vbforums.com/showthread.php?901690-Populate-a-Combobox-from-an-Excel-workbook-column

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Fill the ComboBox with Distinct Product names from Sheet
        ComboBox1.DataSource = ExcelOleDb("V:\CMM Data Files\Micura CMM PCM Programs (252)\Inline Blocks\Parameters.xls", "SELECT Distinct Part FROM [Dimensions$A:BZ]")
        ComboBox1.DisplayMember = "Part"
        ListBox2.Items.Clear()
        'Form2.Show() 'for testing

    End Sub

I am now trying to understand how to populate a Listbox with a Row of data from the DataGridView based on a selection from the Combobox.
The data going into the Listbox has to be in a single column. I have spent days reading and trying to accomplish this, but I think I am over my head this time.
Are there any tutorials/examples that discuss getting and placing data as I have described?
Not sure if not showing the DataGridView is normal or a hindrance, I prefer it to be hidden (Shown for example only)

All assistance is appreciated.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

Based on the link provided, you have been able to

populate the Combobox with a single column of data from the DataGridView [Excel file: Parameters.xls]

You should be able to

populate a Listbox with a Row of data from the DataGridView [same Excel file] based on a selection from the Combobox.

You would just need to modify the second parameter you send to ExcelOleDb Function
You don't need your Form2 with DataGridView to do so.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

I am just too new; I have read hundreds of examples and a tutorial. ([ignore]

http://vb.net-informations.com/datagridview/vb.net_datagridview_tutorial.htm

[/ignore])
Your instruction seems simple, yet I don't understand and haven't found documentation that would help me understand.
Can you point me in the direction of reading material that applies to this task?

(I'm just too old for this...LOL)

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

I don't know how your Excel looks like, so I've created my own. It has just 2 columns: Part and Model
[pre]
A B
1 Part Model
2 Part A Model A
3 Part A Model B
4 Part A Model C
5 Part B B model 1
6 Part B B model 2
7 Part B B model 3
8 Part C ABCD
9 Part C XYZ[/pre]

And I have placed a ComboBox1 and ListBox1 on the Form.
Using the code you already have, in Form1_Load I populated ComboBox1 from column A in my Excel with unique (distinct) Part values:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ComboBox1.DataSource = ExcelOleDb("E:\Andrzej\VBNET\WindowsApplication1\Book1.xlsx", "Select distinct [Part] From [Sheet1$]")
    ComboBox1.DisplayMember = "Part"
End Sub

and then I populated Model data into ListBox1 in ComboBox1_SelectedIndexChanged Event, so I get the Model data for any Part I select in the combo box:

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
    With ListBox1
        .DataSource = ExcelOleDb("E:\Andrzej\VBNET\WindowsApplication1\Book1.xlsx", "Select [Model] From [Sheet1$] where [Part] = '" & ComboBox1.Text & "'")
        .DisplayMember = "Model"
    End With
End Sub

When I run it, Combo box has these items:
[tt]Part A [green]<- Already selected[/green]
Part B
Part C[/tt]
and in my ListBox I have:
[tt]Model A
Model B
Model C[/tt]

When I select other Part in the Combo, appropriate Models appear in ListBox.
I hope that's what you are trying to achieve. . .

Keep in mind, you need to provide your own Path and Excel file name, and whatever columns' headers you have.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

This bit of code is what I was unable to figure out:

Select [Model] From [Sheet1$] where [Part] = '" & ComboBox1.Text & "'"

I don't recall reading much about this portion of the "ExcelOleDb".
Most information on forums is a bit cryptic when you don't understand the language and they are trying to help you learn.
Do you happen to know of any reading material on ExcelOleDb?

I am grateful for your time and effort!



Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

No, I don't know any reading material, I am mostly self-thought.
But this is a very good place to ask questions.

The [tt]ExcelOleDb[/tt] is just a name of a Function in your code. You may as well name it [tt]GrabMyDataFromExcel[/tt]

This piece of code is an SQL, a Select statement asking for a data. What I usually do is [red]hard-code[/red] what I want to get:
[tt]strSQL = "Select [Model] From [Sheet1$] where [Part] = '[red]Part A[/red]'"
Debug.Print strSQL[/tt] <- display what I get in strSQL

and if that works OK, I replace [red]Part A[/red] with whatever I select from the Combo:
[tt]strSQL = "Select [Model] From [Sheet1$] where [Part] = '[red]" & ComboBox1.Text & "[/red]'"
Debug.Print strSQL[/tt]

But the main question is - did it work for you? And is it what you wanted?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

The code works as you stated, I am now reading on how to get the entire row from "Part" in the Listbox transposed to a column.
I've been reading for a couple days now, and I found the Code Bank page.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

You want to 'get the entire row from "Part" in the Listbox transposed to a column.'
I don't know what that is.
Could you show me what you mean? An example would be nice.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

In Excel, I can get a Row (to used range) and place that in a Listbox/Combobox as a Column using "Transpose".
See image of the excel worksheet: Excel file Data in a row.PNG

At runtime of my VB project, I select the first entry (Part), 00-294528 in my Combobox, as you helped, it places that entry in the Listbox.
When I select the "Part" I want cells from column B: (I am currently using B:BZ as my range which may have blanks at the end, this varies) as the row of "Part" selected.
This is placed in the Listbox as a column of data instead of a row of data.
I hope I explained this properly.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

So, if you select [tt]00-294528[/tt] in the combo, you want to display in your listbox:
[tt]5x75
3x50
3
9
2.703
2.711
1.171
1.5061498
...[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

That is correct.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

Give that a try.
Created a one row table with the data, loop thru all columns and row and display column name and the value of that row of data in the listbox.

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim strItem As String = ""
        Dim datDT As System.Data.DataTable = ExcelOleDb([red]strPathExcel[/red], "[highlight #FCE94F]Select * From [Sheet1$][/highlight] where [Part] = '" & ComboBox1.Text & "'")
        Me.Cursor = Cursors.WaitCursor
        With ListBox1
            .Items.Clear()
            For Each col As DataColumn In datDT.Columns
                strItem = col.ToString
                For Each row As DataRow In datDT.Rows
                    strItem &= " - " & row.Item(col.ToString).ToString
                Next row
                .Items.Add(strItem)
            Next
        End With
        Me.Cursor = Cursors.Default
    End Sub

I have declared [tt]strPathExcel[/tt] and assigned the value to it in Form_Load event, so I have it in one place only and do not need to repeat it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

WOW.
Something I did not understand or know, it seems that the first row (header row?) is automatically included in the population? (If I expressed that right).
Removing the Header Row Property by adding:
HDR=YES to the following.

.ConnectionString &= "Extended Properties = ""Excel 12.0 XML;HDR=YES"";"

Doesn't seem to remove the Header Row? Which I believe would make row2 my new header row?

    Public Function ExcelOleDb(ByVal strTextPath As String, ByVal sSQL As String) As System.Data.DataTable 'new code 11.27.23-[URL unfurl="true"]https://www.vbforums.com/showthread.php?901690-Populate-a-Combobox-from-an-Excel-workbook-column[/URL]
        Dim con As New System.Data.OleDb.OleDbConnection
        Dim myCmd As New System.Data.OleDb.OleDbCommand
        Dim myadp As New System.Data.OleDb.OleDbDataAdapter
        Dim mydt As New System.Data.DataTable
        With con
            .ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
            .ConnectionString &= "data source=" & strTextPath & ";"
            .ConnectionString &= "Extended Properties = ""Excel 12.0 XML"";" 'original line
            '.ConnectionString &= "Extended Properties = ""Excel 12.0 XML;HDR=YES"";" 'not removing first row 12.04.23
        End With
        With myCmd
            .Connection = con
            .CommandType = CommandType.Text
            .CommandText = sSQL
        End With
        With myadp
            .SelectCommand = myCmd
            Try
                .Fill(mydt) 'My Data Table

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error called from: Public Function ExcelOleDb")
            End Try
        End With
        Return (mydt)
    End Function

Rick Stanich
CMM Programming and Consulting

 

[Rick_Stanich]

I think there is another method:

        Dim datDT As System.Data.DataTable = ExcelOleDb("V:\CMM Data Files\Micura CMM PCM Programs (252)\Inline Blocks\Parameters.xls", "Select * From [Dimensions$A:BZ] where [Part] = '" & ComboBox1.Text & "'")

By adding something similar to this?:

"select * from [{0}${1}]", "Sheet1","A1:BZ")

I haven't figured out how or if this applies.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

the first row (header row?) is automatically included in the population?

The first row (header row?) [from Excel] is automatically included in the population [of the data table in VB.NET]
That's why:
[tt]Dim datDT As System.Data.[red]DataTable[/red][/tt]

When you create a Data Table (out of any source), you will always have/need Field names (what you call a header row) because you need them to refer to the data in those Fields (columns).

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

I'm going to move forward; I appreciate all the Help/Code you have provided.
I'll worry about looking for help later if I can't resolve this on my own.

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

I guess you are getting in your ListBox:

[tt][blue]Part[/blue] - 00-294528
[blue]bore_system[/blue] - 5x75
[blue]kidney_system[/blue] - 3x50
[blue]datum_a_tip[/blue] - 3
[blue]num_bores[/blue] - 9
. . .[/tt]

but if you don't want to have [blue]Fields' names[/blue], just the values (and by-pass the Part value), you can do:

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim datDT As System.Data.DataTable = ExcelOleDb(strPathExcel, "Select * From [Sheet1$] where [Part] = '" & ComboBox1.Text & "'")
        Me.Cursor = Cursors.WaitCursor
        With ListBox1
            .Items.Clear()
            For Each col As DataColumn In datDT.Columns
                For Each row As DataRow In datDT.Rows
                    If [red]col.ToString <> "Part"[/red] Then
                        .Items.Add(row.Item(col.ToString).ToString)
                    End If
                Next row
            Next
        End With
        Me.Cursor = Cursors.Default
    End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Rick_Stanich]

What I am getting is: (Values from the first row).
Part - 00-294528
BoreSystem - 5x75
KidneySystem - 3x50
DatumATip - 3
Number of Bores - 9

What I need is what you posted: (Values from the 2nd row, those are my variables for a CMM language called PCM).
Part - 00-294528
bore_system - 5x75
kidney_system - 3x50
datum_a_tip - 3
num_bores - 9

Rick Stanich
CMM Programming and Consulting

 

[Andrzejek]

You have 2 headers in Excel, which are 'translated' into Fields' names and the second 'header' from Excel is the first record with data in the Table:

[pre]
Part BoreSystem KidneySystem DatumATip Number of Bores
bore_system kidney_system datum_a_tip num_bores
00-294528 5x75 3x50 3 9
00-314446 3x50 0.5x20 3 7
[/pre]
So your Select statement needs to ask for:
[tt]"Select * From [Sheet1$] [highlight #FCE94F]where [Part] is null or[/highlight] [Part] = '" & ComboBox1.Text & "' order by [Part]"[/tt]

Now you will have two records to process and display in the ListBox.

The easier 'fix' is to have the data in your Excel that could be made into a Table without and 'extra' records. In other words, delete the Excel row with:
[tt]BoreSystem KidneySystem DatumATip Number of Bores ...[/tt]
Just keep [tt]Part[/tt] in column A, you need a Field name for every field in the table

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

...or, and easy way out:
Instead of: [tt]Select [highlight #FCE94F]*[/highlight] From ...[/tt]

you may re-name your Fields with Aliases:

[tt]Select [BoreSystem] AS bore_system, [KidneySystem] AS kidney_system, [DatumATip] AS datum_a_tip, [Number of Bores] AS num_bores, ... from ...[/tt]

You may need to do:
[tt]Select [BoreSystem] AS [bore_system], [KidneySystem] AS [kidney_system], ...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson