Datasets and SQL Server

I'm currently working on a application in VB.NET 2003, with a SQL Server backend.

I was asked to make an import function that would allow a user to import an Excel worksheet into SQL Server. I'm aware you can do it from Excel directly to SQL Server, but the clients would like something that wouldn't involve the users of this application touching the SQL Server.

I'm using an OLEDB for the Excel connection, and a ODBC connection for the SQL Server connection.

Next, I have a button event that would open up a OpenFileDialog window, and a user can choose an Excel sheet. If the Excel sheet is valid, it would go on extracting the data into a dataset:

ds = New DataSet
ds.Clear()
strSQL = "Select HEAT, Practice, C, Mn, P, S, Si, Cu, Ni, Cr, Mo, V, " & _
"Cb, Ti, Al, B, N, Ca, Sn from [Ladle Chems$]"

sqlDA = New OleDbDataAdapter(strSQL, con)
sqlDA.Fill(ds)
ds1 = New DataSet

Once that dataset is filled with the excel data, I fill another dataset with the table that data will go into (this is a connection to the SQL Server Database)

strSQL = "Select HeatNumber, SteelPractice, C, Mn, P, S, Si, Cu, Ni, Cr, Mo, V, " & _
"Cb, Ti, Al, B, N, Ca, Sn from tblElements"

sqlDAODBC = New OdbcDataAdapter(strSQL, conn)
sqlDAODBC.TableMappings.Add("Table", "tblElements")
sqlDAODBC.Fill(ds1)

Now with both datasets filled, I will merge them. They both have the same number of columns, and the Primary key in the SQL Server database is indexed (that's why you won't see the Primary key)

ds1.Merge(ds, True)

I then put the data from that merge into a Datatable. So far, this is getting me the exact number of rows so far. The SQL Table gives me 48 rows, and the excel sheet gives me 654 rows.

Dim table As DataTable
table = ds1.Tables(0) 'This has 654 rows

Now i check to see if the table is Null or has nothing in it. Finally, I update that table and accept the changes.

If (Not table Is System.DBNull.Value) And Not table Is Nothing Then
sqlDAODBC.Update(table)
ds1.Tables(0).AcceptChanges()
End If

After that procedure is done, i populate a datagrid (for testing purposes), with a SQL statement that counts the number of rows in the table (I should see 48+654= 702 rows), but I see 48 rows. What am I doing wrong

Answer this question

Datasets and SQL Server

XNA Rockstar
I've tried to change the rowstate. My method was to make a temporary Datatable, with all the neccesary columns, and put the Datarows from the Merged Dataset into a new Datarow. But i keep getting an error that the row belongs to a table. How do I detach or make the table property 'Nothing'
hiphop_fc
I will read a bit more thoroughly however please take out the ds.Tables(0).AcceptChanges() - it really is not required and can cause a few problems in the future if you decide to do another Update() on the dataAdapter after the AcceptChanges() command - the command marks the dataset/datatable as if there are no changes to be made, which then tells the dataAdapter that no rows/records are needed to be updated into SQL
Johan Nordin

Im also facing the same problem, while importing excel data into sql server. i m getting the excel data into oledb dataset(as the connection i use is oledb, to import from excel). then i am using dataadapter's update method to insert ds into sql database. My code is..

Dim conn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & t1.Text & "';Extended properties=Excel 8.0;")

Dim conn1 As New SqlConnection("server=VANDY0;uid=sa;pwd=sa;database=master;")

conn.Open()

str = "select * from [Sheet1$]"

da = New OleDbDataAdapter(str, conn)

ds = New System.Data.DataSet

da.Fill(ds)

DataGrid1.DataSource = ds

''uptill this it is wrking fine

''inserting ds into sql table login in databse name master

conn1.Open()

Dim drow As DataRow

drow = dt.NewRow

drow("usr") = ds

drow("pass") = ds

dt.Rows.Add(drow)

da.Update(ds, "login")

IT is giving me error that colummn name "usr" does not belong to table.... Pls help..

Thanks
Green Apple

SOTY_Programmer wrote:
Will do, I appreciate the help. I hope I explained the problem well enough.
enric vives

Im also facing the same problem, while importing excel data into sql server. i m getting the excel data into oledb dataset(as the connection i use is oledb, to import from excel). then i am using dataadapter's update method to insert ds into sql database. My code is..

Dim conn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source='" & t1.Text & "';Extended properties=Excel 8.0;")

Dim conn1 As New SqlConnection("server=VANDY0;uid=sa;pwd=sa;database=master;")

conn.Open()

str = "select * from [Sheet1$]"

da = New OleDbDataAdapter(str, conn)

ds = New System.Data.DataSet

da.Fill(ds)

DataGrid1.DataSource = ds

''uptill this it is wrking fine

''inserting ds into sql table login in databse name master

conn1.Open()

Dim drow As DataRow

drow = dt.NewRow

drow("usr") = ds

drow("pass") = ds

dt.Rows.Add(drow)

da.Update(ds, "login")

IT is giving me error that colummn name "usr" does not belong to table.... Pls help..

Thanks
Arindam Biswas
I tried this out and I was having difficulty. I'm using VS2003 (VB.NET), and I don't see the .SetAdded Method. I tried to look this up in the VS2003 Help, and couldn't find it. Sorry if I'm being a pain with this.

The approach that I took before you posted, was this:

Dim intCounter As Integer

Dim table As DataTable = New DataTable("test")

Dim tblColumn As DataColumn

tblColumn = New DataColumn("HeatNumber")

table.Columns.Add(tblColumn)

tblColumn = New DataColumn("SteelPractice")

table.Columns.Add(tblColumn)

tblColumn = New DataColumn("C")

table.Columns.Add(tblColumn)

For intCounter = 0 To ds1.Tables(0).Rows.Count - 1

tblRow = ds1.Tables("dataSet").Rows(intCounter)

table.Rows.Add(tblRow)

Debug.WriteLine(table.Rows(intCounter).RowState)

Next

But this didn't work either.

EDIT: I got it to work, I made another dataset and just added the rows from the dataset with the SQL Query into the newly created Dataset. I appreciate all the help, you put me in the right direction.
goonfro
Have you examined the each rowstate in table the rowstate needs to be Added if you want adapter.Update() to add the row.
Waiman Li
where & how did you define dt
gloriousgopi

not fully understand your approach. However, if I were to solve the problem, I would simply change the rowstate in ds:

foreach (DataRow dr in ds.Tables[0].Rows) {

dr.SetAdded();

}

Then merge it to ds1.
Mathew1972
hello -
you must write DATABIND() in end of your Code.
mammoo
I don't know if this answers your question, The merging that I do with the 2 datasets, it does work. I get the 700 some rows, and then I do the update, but the update doesn't work.
yavi
Will do, I appreciate the help. I hope I explained the problem well enough.
Markus Sch.
Alright, i just checked to see the rowstate, and it seems that the Rowstate is set to 'Unchanged'. I'm guessing I can change this rowstate to Added, and that should fix the problem.
Amos Soma
The problem is that you need to change the merged rows' rowstate to added in order for adapter to update the changes. (That's why I asked to examined the each row's rowstate in the merged table).
Datasets and SQL Server

Answer this question