How to append data to system.data.dataset.

I have a large group of Excel files that are my data source for a vb.net application.  I use OleDb to extract the relevant data from each spreadsheet (one row of data per spreadsheet).

My end product will be a table with each one of those data rows from above.

I need help figuring out how to dump the data into a single vb.net DataSet.  (I don't really understand details about TableMappings and DataSet.)

The code below works fine for single iteration, but I need to uncomment the "for loop" and get everything into the dataset and datagridview.  Can someone help?
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

DtSet = New System.Data.DataSet

#For Each xMarket in MarketArray
Dim xMarket = "Atlanta"
	MyConnection = New System.Data.OleDb.OleDbConnection _
		("provider=Microsoft.ACE.OLEDB.12.0;" & _
		" Data Source='" xMarket & ".xlsx" & "'; " & _
		" Extended Properties=" + Convert.ToChar(34).ToString() +
		"Excel 12.0;HDR=Yes" + Convert.ToChar(34).ToString())
	MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" & myTab & "$] where Week like '%" & xWeek & "%'", MyConnection)
	MyCommand.TableMappings.Add("Table", "TestTable")
	MyCommand.Fill(DtSet)
	DataGridView1.DataSource = DtSet.Tables(0)
#Next

Open in new window

motokentAsked:
Who is Participating?
 
Chandan_GowdaConnect With a Mentor Commented:
you can use dataset merge method. Please follow the below link
http://aspalliance.com/148
0
 
khan_webguruConnect With a Mentor Commented:
Hello Bro!

You can use this approach

 
Sub BindGrid()
     Dim myConnection as New SqlConnection (strConn)
 
     Dim DS1 As DataSet
     Dim DS1 As DataSet
     Dim MyCommand As SqlDataAdapter
 
     MyCommand = new SqlDataAdapter("exec s_get_table1", MyConnection)
     DS1 = new DataSet()
     MyCommand.Fill(DS1, "MyTable")
 
     MyCommand = new SqlDataAdapter("exec s_get_table2", MyConnection)
     DS2 = new DataSet()
     MyCommand.Fill(DS2, "MyTable")

     ds1.merge(ds2)
 
     MyDataGrid.DataSource=DS1.tables(0).DefaultView
     MyDataGrid.DataBind()
End Sub

Open in new window


Hope this will provide you some idea

Regards,

Asif Ahmed KHan
0
 
motokentAuthor Commented:
Thank you both.  (The solutions were the same.)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.