Question Importing 5 x 1000 data field into DataGridView

[MikeUK]

Good evening,
I am trying to make part of a form program do the following
When I exit a text field, I want to import some datainto a datagridview control.
The columns are (1)Key, (2)Location, (3)Part Number, (4)Serial Number, (5)Description.
There are up to 1000 rows of data in anyone of the spreadsheets I am working from.

By using a datagridview, I can click the header and sort the data by either of the 5 columns, and when I select a data row, the Key number is reported back (for now, into a text box)
I will be using the key number to further manipulate the spreadsheet after I have solved this problem.

There are thousands of articles on DataGridView, and over the past 8 days, I think I have read them all. I have tried many downloadable routines, which haven't populated the DataGridView at all.

Please can someone show me a simple program which will do the above task, and will give me a base to start from

I am running MS Office Pro plus 64-bit on Windows 7 and Visual studio 2010 pro

 

[Paszt]

You mention spreadsheets and MS Office, so I'll assume you mean Excel spreadsheet (I hate assuming )

The first result when I plugged "vb.net import excel spreadsheet to datagridview" into google.com was this link: Import xls data into datagridview on form which has some promising looking code. (I tell you this so you can see my process for solving these questions). I tested it and it seems good.

I created an excel spreadsheet with the 5 columns you described (the first row should contain the column names, notice the HDR=Yes part of the connection string - HDR = Header) and a form in Visual Studio with a button named ImportButton, a textbox named SelectedKeyTextBox, and a DataGridView oddly named DataGridView1.

Private Sub ImportButton_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles ImportButton.Click
    Dim connectionStringTemplate As String = _
          "Provider=Microsoft.ACE.OLEDB.12.0;" + _
          "Data Source={0};" + _
          "Extended Properties=""Excel 12.0;HDR=Yes"""

    Dim connectionString As String = String.Format(connectionStringTemplate, _
                                                   "X:\DeleteThis.xlsx")

    Dim sqlSelect As String = "SELECT * FROM [Sheet1$];"

    ' Load the Excel worksheet into a DataTable
    Dim workbook As New DataSet()
    Dim excelAdapter As System.Data.Common.DataAdapter = _
      New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)
    excelAdapter.Fill(workbook)

    ' Data Bind
    DataGridView1.DataSource = workbook.Tables(0)
    SelectedKeyTextBox.DataBindings.Add("Text", DataGridView1.DataSource, "Key")
End Sub

By adding the dataBinding to the TextBox, when the user changes the selection of the DataGridView the text updates accordingly. I'm using Office 2010.

Hopefully that will get you started…

 

[MikeUK]

Paszt
Thanks very much for your help.
The last line of code

    SelectedKeyTextBox.DataBindings.Add("Text", DataGridView1.DataSource, "Key")

Worked a treat. It's taken me 9 days to find this solution.I can now get on with the rest of this project
Regards
MikeUK