What exactly are you expecting happen? Are the records coming from the workbook new records that should be inserted or updates to the existing records, or a combination of both?
It's important that you understand a few things so you can see why this can't work as is. Firstly, your DataSet doesn't contain any records itself but rather it contains DataTables that contain DataRows, just as your database contains tables that contain rows. If you're only using one DataTable in the DataSet then the DataSet itself is pointless and you should simply create the DataTable yourself in the first place. Would you create an array to hold a single Integer or String?
Now, each DataRow in your DataTable's Rows collection has a RowState property and it's that property that determines what happens to that row when you call Update on a data adapter. When you first create a DataRow, it's RowState is Detached, meaning that it's not part of the DataTable yet. When you Add the row to the Rows collection, its RowState becomes Added meaning that it is ready to be inserted into the database using the InsertCommand of the data adapter.
When you call Fill on a data adapter, a DataRow is created and added to the DataTable for each record retrieved by the SelectCommand. As I said, these rows will all have a RowState of Added. This is generally not what you want because these are existing rows, not new rows. With that in mind, the Fill method will automatically call AcceptChanges on the DataTable by default once all the data has been loaded. Calling AcceptChanges on the table will cause AcceptChanges to be called on each row. What AcceptChanges does is set the RowState to Unchanged regardless of what it was before. That now indicates that the rows are all unchanged from their state in the database and will therefore be ignored when you call Update. That is why nothing happens when you call Update.
There's a bit more to know about RowState too. If the value is Unchanged and you edit the row then it becomes Modified and ready to be saved to the database by the UpdateCommand of the data adapter. If the value is Unchanged or Modified and you call Delete on the row, it gets set to Deleted and the row will be deleted by the DeleteCommand of the data adapter. If AcceptChanges is called, either explicitly or implicitly, all rows with a RowState of Added or Modified have their RowState set to Unchanged and any with a RowState of Deleted are removed altogether.
So, if you expect to make changes to your database, you can't be calling AcceptChanges, either explicitly or implicitly. When you call Fill on the first data adapter, you do want AccepChanges called because you want all the existing rows from the database to be marked Unchanged. As such, you can just leave that data adapter as it is. You do not want AcceptChanges called when you call Fill on the second data adapter though, because you want any changes it makes to be marked as changes, with the RowStates being Added for new rows and Modified for updated rows. To make that happen, set the AcceptChangesDuringFill property of the data adapter to False before calling Fill. You might also have to look at the FillLoadOption property. I'm not sure what it is by default but I believe that it will need to be Upsert to work if you're updating existing rows.
Step 1: Add SQL connection as datasource to project through the wizard.
Step 2: Let Visual Studio do all the work for you and create a typed dataset.
Step 3: Profit.
Dim ds As New MyCompanyDataSet
Dim ta As New MyCompanyDataSetTableAdapters.ClientsTableAdapter
Dim newrow As MyCompanyDataSet.ClientsRow = ds.Clients.NewClientsRow()
newrow.ClientId = 2326
newrow.ClientCode = "whatever"
newrow.CreditLimit = 0
newrow.ExEntityGUID = New Guid
ds.Clients.Rows.Add(newrow)
If ta.Update(ds.Clients) > 0 Then
ds.AcceptChanges()
End If
Add the data from your excel file and call the tableadapter's .Update, then IF the update is successful call .AcceptChanges.
Step 1: Add SQL connection as datasource to project through the wizard.
Step 2: Let Visual Studio do all the work for you and create a typed dataset.
Step 3: Profit.
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Clients"
Dim newrow As MyCompanyDataSet.ClientsRow = ds.Clients.NewClientsRow()
newrow.ClientId = 2323
newrow.ClientCode = "whatever"
newrow.CreditLimit = 0
newrow.ExEntityGUID = New Guid
ds.Clients.Rows.Add(newrow)
ds.AcceptChanges()
Add the data from your excel file and call .AcceptChanges.
Um, not quite. That AcceptChanges call will mean that then can't save that record to the database. You'd want top be calling Update on a table adapter there. Also, while it's a small thing, you should be setting things like DisplayMember and DataMember before setting the DataSource.
Also removed the dgv altogether because it's not the point anyways...
To add the rows from Excel to the typed dataset, you would select the rows you want and add them in a for each loop or a linq query as your typed rows.
To add the rows from Excel to the typed dataset, you would select the rows you want and add them in a for each loop or a linq query as your typed rows.
That's not necessarily the case. A typed DataTable is still a DataTable so can still be filled using a data adapter. You could still use an OleDbDataAdapter to Fill the DataTable with the data from the workbook. If any of those records contain existing IDs then the existing rows will be updated and their RowState set to Modified, ready to be updated when the table adapter's Update method is called. Likewise, any records from the workbook that are new will create DataRows with a RowState of Added, ready to be inserted into the database.
Ok Thanks for all the replies,...heres my code it compiles with no errors, but just does not update the hhwhrs dataset back in the sql server db.
I have fill the same dataset.Table("HHWHrs" from 2 different datadpters and upadte the sqlserver datadpter at the end !
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ds2 As DataSet = New DataSet()
Dim sqlcb As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim strFileName = ""
Dim filedb As New OpenFileDialog()
Try
filedb.Filter = "Excel Documents (*.xls;*.xlsx)|*.xls;*.xlsx|" & "All Files(*.*)|"
filedb.FilterIndex = 1
If (filedb.ShowDialog() = DialogResult.OK) Then
strFileName = filedb.FileName
Else
MsgBox("You did not select a file!")
End If
MsgBox(strFileName)
Dim connString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFileName & ";Extended Properties=Excel 8.0;"
Dim oleConn As OleDbConnection = New OleDbConnection(connString)
Dim cmd2 As OleDbCommand = New OleDbCommand("SELECT * FROM [WorkedHours$]", oleConn)
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter(cmd2)
oleda.Fill(ds, "HHWHrs")
DataGridView1.DataSource = ds.Tables("HHWHrs")
cmd = New SqlCommand("SELECT Top 1 PersNo,WeekEnding, BasicHrs,TimeQtr,Travel FROM TblWorkedHours", conn)
da = New SqlDataAdapter(cmd)
da.Fill(ds, "HHWHrs")
DataGridView1.DataSource = ds.Tables("HHWHrs")
MsgBox("Update Now")
conn.Open()
Me.Validate()
DataGridView1.EndEdit()
ds.Tables("HHWHrs").AcceptChanges()
da.Update(ds, "HHWHrs")
Catch
Finally
' Close connection
conn.Close()
' ds.Clear()
'ds2.Clear()
End Try
OK I've incorporated jmcilhinneys suggestions above, here is the new code that works but not how I expect it too.
I want to read in any number of new rows from an excel spreadsheet and append them into a sql server table, simples:crap:
So far I can open the excel spreadsheet read in the rows show them in the datagridview using an oleDataAdapter (oleda).
Then I add them one at a time to another dataset with an identical table( I know I can use just datatables but down the line I will be adding more tables to be updated, so want to start with the dataset!)
On my form I have two buttons and a datagridview the first button reads in the data to ds2 and display it and then copies them over to the first dataset which works fine but then tries to update the ds dataset with the the new rows and fails with an error on the datadfridview( a red exclamtion mark on the first row!).
Then if I click second button it updates the ds dataset adn rgw red error disappears and the new rows have been added to my sqlserver table like magic!
Amazingly button1 does everything correctly except the final update but then when I click on Button 2 it doess the update...but button2 it has all the same code as button 1!
Also I'm not happy with the way I have to use 1 row from my table I would prefer to use FillSchema but can't get it to work! any ideas?
Public Class Form1
Public connStr As String = "Server=NWA-118420M\SQLEXPRESS;Database=HCSSQLSVR1;Trusted_Connection=yes;"
Public ClientID, StaffID, AreaID As Integer
Public UName As String = System.Environment.UserName
Public AreaName As String = "Testing"
Dim ds As New DataSet
Dim da As New SqlDataAdapter
Dim conn As New SqlClient.SqlConnection(connStr)
Dim cmd As SqlCommand
'Dim dr As New DataRow
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds2 As DataSet = New DataSet()
Dim strFileName = ""
Dim filedb As New OpenFileDialog()
Dim sqlcb As SqlCommandBuilder = New SqlCommandBuilder(da)
Try
filedb.Filter = "Excel Documents (*.xls;*.xlsx)|*.xls;*.xlsx|" & "All Files(*.*)|"
filedb.FilterIndex = 1
filedb.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
If (filedb.ShowDialog() = DialogResult.OK) Then
strFileName = filedb.FileName
Else
MsgBox("You did not select a file!")
End If
MsgBox(strFileName)
Dim connString As String = "provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFileName & ";Extended Properties=Excel 8.0;"
Dim oleConn As OleDbConnection = New OleDbConnection(connString)
Dim cmd2 As OleDbCommand = New OleDbCommand("SELECT * FROM [WorkedHours$]", oleConn)
Dim oleda As OleDbDataAdapter = New OleDbDataAdapter(cmd2)
oleda.Fill(ds2, "HHWHrs")
DataGridView1.DataSource = ds2.Tables("HHWHrs")
MsgBox("OK")
cmd = New SqlCommand("SELECT Top 1 PersNo, WeekEnding, BasicHrs, TimeQtr, Travel FROM TblWorkedHours", conn)
da = New SqlDataAdapter(cmd)
da.FillLoadOption = LoadOption.Upsert
da.AcceptChangesDuringFill = False
da.Fill(ds, "HHWHrs")
DataGridView1.DataSource = ds.Tables("HHWHrs")
MsgBox("OK")
ds.Tables("HHWHrs").Rows.RemoveAt(0)
DataGridView1.DataSource = Nothing
Dim c As Integer = ds2.Tables("HHWHrs").Rows.Count
MsgBox(c)
DataGridView1.DataSource = ds.Tables("HHWHrs")
For x As Integer = 0 To c - 1
MsgBox(ds.Tables("HHWHrs").Rows.Count & " Records - Rows " & x)
ds.Tables("HHWHrs").ImportRow(ds2.Tables("HHWHrs").Rows(x))
ds.Tables("HHWHrs").Rows(x).SetAdded()
Next x
MsgBox("Update Now")
Me.Validate()
DataGridView1.EndEdit()
da.Update(ds, "HHWHrs")
Catch
Finally
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sqlcb As SqlCommandBuilder = New SqlCommandBuilder(da)
Me.Validate()
DataGridView1.EndEdit()
'ds.Tables("HHWHrs").AcceptChanges()
da.Update(ds, "HHWHrs")
End Sub
So, if I'm not mistaken, all you really want to do is read some rows from a Excel worksheet and insert them into a SQL Server database, correct? It's always a good idea to provide a clear explanation like that because we don't know what's in your head and if you waffle on then it just confuses the matter. Anyway, if that's what you want then it takes an awful lot less code than you are using.
Dim table As New DataTable
Dim databaseConnection As New SqlConnection("SQL Server connection string here")
Dim databaseAdapter As New SqlDataAdapter("SELECT * FROM SomeTable", databaseConnection)
Dim databaseBuilder As New SqlCommandBuilder(databaseAdapter)
'Make sure that the schema contains all the PK information.
databaseAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
'Create the schema in the DataTable.
databaseAdapter.FillSchema(table, SchemaType.Source)
'Get the new data from the worksheet.
Using worksheetConnection As New OleDbConnection("Excel connection string here"),
worksheetCommand As New OleDbCommand("Excel query here", worksheetConnection)
worksheetConnection.Open()
Using worksheetReader = worksheetCommand.ExecuteReader()
table.Load(worksheetReader)
End Using
End Using
databaseAdapter.Update(table)
That's all it takes; less if you take Herman's advice and create a typed DataSet. You just need to adjust that to provide appropriate connection strings and queries. You may have to alias the columns in the worksheet query if it does not have column headers that match the database column names.
Apologies, your right in my excitement I forgot to to include a line that said all I wanted to do was import a few rows for excel to my sql server table, Doh!
Anyways, I'm looking forward to trying out your code and will let you know how I get on, it looks great tho !
Can I ask which is the most efficient way, using a datatable/dataset update command as you have done or go for a paramterised INSERT sqlcommand?
The Update looks simpler and faster but is there any downside in terms of best practise ?
In actual fact, if you use the data adapter then you're using a parameterised INSERT command anyway. The InsertCommand of the data adapter is a SqlCommand and it contains an INSERT statement. When you call Update, the adapter basically loops through the Rows collection of the DataTable and executes its InsertCommand for each DataRow with a RowState of Added, it's UpdateCommand for each Modified row and its DeleteCommand for each Deleted row. There is some overhead associated with that looping and testing of RowStates but it's minor and it can be offset by the fact that you can configure the adapter to send changes to the database in batches rather than one at a time. Unless you have a large number of records though, it's not really going to matter either way. Using a data adapter is cleaner so that should be your first choice.
Also note that I have used a command builder here but you can also create the SqlCommand and it's parameters explicitly and assign it to the adapter's InsertCommand yourself.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.
