Read selected Excel data into DataGridView using VB.NET2008
-
I tried it for you and it works well: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\1.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception End Try End Sub ---------------------------------------------------------------------- For example Excel FullFileName: C:\1.xls In sheet1 add 3 columns (from A1 to A3): A1=Qty A2=100 A3=250 B1=Prod B2=A B3=B C1=ID C2=1 C2=2 I suggest you to do this simple example that works fine and after you get that this example works with no problem try to check where is your problem
Shay Noy
Well,I have try with this code as you suggest below: <code> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\TestPharmaExcel.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try And it promped me an error: No value given for one or more required parameter. What should I do to this? Anyone can give me some idea? Thanks for reading. Regards Drex
-
Well,I have try with this code as you suggest below: <code> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\TestPharmaExcel.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try And it promped me an error: No value given for one or more required parameter. What should I do to this? Anyone can give me some idea? Thanks for reading. Regards Drex
Iam using like the following :thumbsup: ExcelFile="C:\temp\My_test.xls" Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ExcelFile & "; Extended Properties=""Excel 8.0; HDR=Yes; IMEX=1""" Dim excelsheet As String = "[Sheet1$]" Dim sql As String = "SELECT * FROM " & excelsheet Try Using cn As New OleDb.OleDbConnection cn.ConnectionString = strConn Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn) cn.Open() da.Fill(items) cn.Close() End Using Catch ex As Exception MessageBox.Show(ex.Message) End Try
-
Iam using like the following :thumbsup: ExcelFile="C:\temp\My_test.xls" Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ExcelFile & "; Extended Properties=""Excel 8.0; HDR=Yes; IMEX=1""" Dim excelsheet As String = "[Sheet1$]" Dim sql As String = "SELECT * FROM " & excelsheet Try Using cn As New OleDb.OleDbConnection cn.ConnectionString = strConn Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, cn) cn.Open() da.Fill(items) cn.Close() End Using Catch ex As Exception MessageBox.Show(ex.Message) End Try
Sorry,I'm not going to read all the Sheet1 information into my datagirdview,because I doesn't need the additional data inside the sheet1. I would like to just retrieve the table data into my DataGridView. My 1st row header column for the table in my Excel sheet are A4:G4, the details of each row of data are from A5:G5. Can I know how to do this retrieving of data into my DataGridView? Hope someone can guide me better. Thank you~! Regards Drex
-
Well,I have try with this code as you suggest below: <code> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConn As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Try MyConn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\TestPharmaExcel.xls'; Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select Qty from [Sheet1$] ", MyConn) MyCommand.TableMappings.Add("Table", "TestTable") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConn.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try And it promped me an error: No value given for one or more required parameter. What should I do to this? Anyone can give me some idea? Thanks for reading. Regards Drex
At which line of code it gave you the error?
Shay Noy
modified on Sunday, July 12, 2009 6:55 AM
-
At which line of code it gave you the error?
Shay Noy
modified on Sunday, July 12, 2009 6:55 AM
Well..The Error appeared at this line>> MyCommand.Fill(DtSet) What might be the problem? Thanks ~!
-
Well..The Error appeared at this line>> MyCommand.Fill(DtSet) What might be the problem? Thanks ~!
I made a simulation by changing the name of the Qty column in my excel file and I got the same error message as you got. The problem is that you do not have a Qty column in your excel file. You have to change the "Select Qty" in the query to "Select YourNameField"
Shay Noy
-
I made a simulation by changing the name of the Qty column in my excel file and I got the same error message as you got. The problem is that you do not have a Qty column in your excel file. You have to change the "Select Qty" in the query to "Select YourNameField"
Shay Noy
I do have a Qty column located at A3 cell here at Sheet1 actually. But I don't know why can cause this problem. My data is start at A4 column,I cannot put my Qty column to A1 because there is other data in A1 field in my Excel. Do you have any idea how should solve this problem? Thanks Regards Drex
-
I do have a Qty column located at A3 cell here at Sheet1 actually. But I don't know why can cause this problem. My data is start at A4 column,I cannot put my Qty column to A1 because there is other data in A1 field in my Excel. Do you have any idea how should solve this problem? Thanks Regards Drex
Have you got data in A1, A2 lines? If yes you must leave those lines blank. Try it and reply
Shay Noy
-
Have you got data in A1, A2 lines? If yes you must leave those lines blank. Try it and reply
Shay Noy
No,it was blank field for A1 and A2 field here actually. My table start with A3 actually. Do you have any other way beside this? Is that Interop can help to solve this problem? Do you have sample for using interops? Thanks~! Regards Drex
-
No,it was blank field for A1 and A2 field here actually. My table start with A3 actually. Do you have any other way beside this? Is that Interop can help to solve this problem? Do you have sample for using interops? Thanks~! Regards Drex
I have another way, if you want, by linking the excel table to MS Access and then using access table to continue you project. But, let's try to understand what you problem is. If you want send me your excel file by email (helelark@gmail.com) and the button1 code too, I will check and re-send it to you. Just tell me if you send it too me by email so I will check my email
Shay Noy
-
I have another way, if you want, by linking the excel table to MS Access and then using access table to continue you project. But, let's try to understand what you problem is. If you want send me your excel file by email (helelark@gmail.com) and the button1 code too, I will check and re-send it to you. Just tell me if you send it too me by email so I will check my email
Shay Noy
I just email you my Excel file. Hope you can check it for me. Thank you~! Regards Drex
-
I just email you my Excel file. Hope you can check it for me. Thank you~! Regards Drex
Just remove the 2 first rows, A1 and A2 and it will works fine
Shay Noy
-
Just remove the 2 first rows, A1 and A2 and it will works fine
Shay Noy
Yes,but the problem is I must get it work with A4 as the row header. Because I need to grab the data from the OrderingFormat.xls file I have send to you. If I start with A1 as the row header,it won't works in my project. Do you have any other ideas? Thank you. Regards Drex
-
Yes,but the problem is I must get it work with A4 as the row header. Because I need to grab the data from the OrderingFormat.xls file I have send to you. If I start with A1 as the row header,it won't works in my project. Do you have any other ideas? Thank you. Regards Drex
Sorry, I saw the second file you sent me only after sending you this reply. I have checked in google and the solution is as following: select Qty from [Sheet1$A3:C6]... A3:first column,first row C6:last column,last row Good luck
Shay Noy
-
Sorry, I saw the second file you sent me only after sending you this reply. I have checked in google and the solution is as following: select Qty from [Sheet1$A3:C6]... A3:first column,first row C6:last column,last row Good luck
Shay Noy
Yes,I got the solution now. Thank you so much for your help on this issue Mr. Eric Hilel. I will solve the problem here. Thanks alot for your kind effort. Regards Drex
-
Yes,I got the solution now. Thank you so much for your help on this issue Mr. Eric Hilel. I will solve the problem here. Thanks alot for your kind effort. Regards Drex
You are welcome
Shay Noy