×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to get data from DataGridView to a Listbox?

How to get data from DataGridView to a Listbox?

How to get data from DataGridView to a Listbox?

(OP)
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.

Quote (VB Forum)

CODE --> VB.net

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

RE: How to get data from DataGridView to a Listbox?

Based on the link provided, you have been able to

Quote (Rick)

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

You should be able to

Quote (Rick)

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

RE: How to get data from DataGridView to a Listbox?

(OP)
I am just too new; I have read hundreds of examples and a tutorial. (http://vb.net-informations.com/datagridview/vb.net_datagridview_tutorial.htm)
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

RE: How to get data from DataGridView to a Listbox?

I don't know how your Excel looks like, so I've created my own. It has just 2 columns: Part and Model
   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 

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:

CODE

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:

CODE

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:
Part A <- Already selected
Part B
Part C

and in my ListBox I have:
Model A
Model B
Model C


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

RE: How to get data from DataGridView to a Listbox?

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

CODE --> VB.net

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

RE: How to get data from DataGridView to a Listbox?

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

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

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

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


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

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
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

RE: How to get data from DataGridView to a Listbox?

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

RE: How to get data from DataGridView to a Listbox?

(OP)
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

RE: How to get data from DataGridView to a Listbox?

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

ponder

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
That is correct.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

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.

CODE

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim strItem As String = ""
        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
                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 strPathExcel 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

RE: How to get data from DataGridView to a Listbox?

(OP)
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.

CODE --> VB.net

.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?

CODE --> VB.net

Public Function ExcelOleDb(ByVal strTextPath As String, ByVal sSQL As String) As System.Data.DataTable 'new code 11.27.23-https://www.vbforums.com/showthread.php?901690-Populate-a-Combobox-from-an-Excel-workbook-column
        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

RE: How to get data from DataGridView to a Listbox?

(OP)
I think there is another method:

CODE --> VB.net

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?:

CODE --> Unknown

"select * from [{0}${1}]", "Sheet1","A1:BZ") 
I haven't figured out how or if this applies.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

Quote (Rick)

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:
Dim datDT As System.Data.DataTable

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

RE: How to get data from DataGridView to a Listbox?

(OP)
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

RE: How to get data from DataGridView to a Listbox?

I guess you are getting in your ListBox:

Part - 00-294528
bore_system - 5x75
kidney_system - 3x50
datum_a_tip - 3
num_bores - 9
. . .


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

CODE

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 col.ToString <> "Part" 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

RE: How to get data from DataGridView to a Listbox?

(OP)
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

RE: How to get data from DataGridView to a Listbox?

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:

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
 
So your Select statement needs to ask for:
"Select * From [Sheet1$] where [Part] is null or [Part] = '" & ComboBox1.Text & "' order by [Part]"

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:
BoreSystem KidneySystem DatumATip Number of Bores ...
Just keep Part 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

RE: How to get data from DataGridView to a Listbox?

...or, and easy way out:
Instead of: Select * From ...

you may re-name your Fields with Aliases:

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

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

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
Thank you, I am looking into these methods.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

(OP)
Using method 1 of your two solutions.

CODE --> VB.net

"Select * From [Sheet1$] where [Part] is null or [Part] = '" & ComboBox1.Text & "' order by [Part]") 
I get odd results.
See images Capture1.PNG and Capture2.PNG
Its adding a lot of "=" signs.

Also attaching my Excel workbook. (DataGridView is not retrieving all the cell values from Row2, so many of Row2 variables are missing?).





Also adding a copy of my excel file (Reduced list to ten Part numbers).

Edit:
The Code used above is not repeating the same results.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

If you want to 'convert' / treat your Excel as a source of your Table, there are some guidlines that need to be fallow.

Your field names (headers) cannot start with a Number, that creates problems. Also, no special characters, like a hard-return, equal sign, etc. (Column AQ and BG), and Column BH has no header.

That would be a good start to fix these problems.

I would create some simple Excel file following the rules above and code against that first.

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
I did not know this; I am working with legacy Excel files.
I do know this has been done before, I have an EXE file that is doing what I am attempting to do, I just need to update things for new versions of OS and the CMM software. Unfortunately, the source code is unknown.

I will create a new Excel file as you described for testing.
Again, Thank you.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

If your goal is to use Excel file(s) and display their context in an application other than Excel, you do not necessary need to 'convert' your Excel into a DataTable.
To populate the combo with the Part, you may just get that from the Excel itself.
For any selected part, you can get all other columns from Excel, too. Without converting that into a DataTable.

More ways to 'skin the cat'. . .

I will have some simple, not very efficient code tomorrow...

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)

Quote (Andrzejek)

If your goal is to use Excel file(s) and display their context in an application other than Excel
That was my initial goal, but I was led to using DataGridView instead.
In VBA, its easy.

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

What I did is - start a new VB.NET Project (I have VB.NET 2015)
On the Form I've placed a combo box named cboPart (I did change its DropDownStyle to DropDownList) and a list box named lstDetail
I added a Reference (Project - Add Reference...) to Microsoft Excel ##.# Object Library



and this code:
(you need to provide your path to your Excel file)

CODE

Imports System.ComponentModel
Imports Microsoft.Office.Interop

Public Class Form1
    Dim xlApp As New Excel.Application
    Dim strExcelFile As String = "E:\Andrzej\VBNET\ReadExcelFile\Parameters.xls"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim intR As Integer

        With xlApp
            '.Visible = True
            .Workbooks.Open(strExcelFile)
            With .Sheets(1)
                intR = 3
                Do While .cells(intR, 1).value <> ""
                    cboPart.Items.Add(.cells(intR, 1).value)
                    intR += 1
                Loop
            End With
        End With
        With cboPart
            If .Items.Count > 0 Then
                .SelectedIndex = 0
            End If
        End With
    End Sub

    Private Sub cboPart_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboPart.SelectedIndexChanged
        Dim intR As Integer
        Dim intC As Integer

        lstDetail.Items.Clear()

        With xlApp
            With .Sheets(1)
                intR = 3
                intC = 1

                Do While CStr(.cells(intR, intC).value) <> ""
                    If CStr(.cells(intR, 1).value) = cboPart.Text Then
                        intC = 2
                        Do While CStr(.cells(intR, intC).value) <> ""
                            lstDetail.Items.Add(CStr(.cells(2, intC).value) & " -- " & CStr(.cells(intR, intC).value))
                            intC += 1
                        Loop

                    End If
                    intR += 1
                Loop

            End With
        End With
    End Sub

    Private Sub Form1_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
        xlApp.Quit()
    End Sub
End Class 

This is what I have when I run it:

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
I am getting an error at:

CODE --> VB.net

Do While .cells(intR, 1).value <> "" 
Format error, it displayed "" (nothing displayed).

Quote:

System.InvalidCastException: 'Conversion from string "" to type 'Double' is not valid.'
Inner Exception
FormatException: Input string was not in a correct format.

I changed the double quotes with "-1", this now shows the first part number but again an error.

Quote:

System.InvalidCastException: 'Conversion from string "00-294528" to type 'Double' is not valid.'
Inner Exception
FormatException: Input string was not in a correct format.

I am using VS 2022.

Is it too much to ask to see your project so I can open it?

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

(OP)
This appears to have solved the error.

CODE --> vb.net

Do While .cells(intR, 1).value <> Nothing 

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

You may also try:

CODE

Do While CStr(.cells(intR, 1).value) <> "" 

But, did you get it going and got what you hoped ponder

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

(OP)
I did get the code going and I not only got what I hoped, but it seems so much simplified now.

Thank you again!

Rick Stanich
CMM Programming and Consulting

RE: How to get data from DataGridView to a Listbox?

(OP)
Do we have a message system on this forum?
I'd like to send you a small gift card, just to say thank you.

Rick Stanich
CMM Programming and Consulting, LLC

RE: How to get data from DataGridView to a Listbox?

Great! thumbsup2

Another way would be to read Excel file once, copy all data into 2D (String) array, close Excel, and do all display from the 2D array. But that's for another day. . . smile

---- Andy

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

RE: How to get data from DataGridView to a Listbox?

No 'message system' on this forum (whatever that is...).
All what you can do is give a little purple star, which you did. smile

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close