I'm probably doing something very stupid, because the problem is terribly simple, But I fail when trying to save a nicely filled small datatable (40 rows !) back to an SQL Server database.
The table is holding currency exchange rates, and has only 4 fields: a date field (datetime), a "From Currency" (varchar) field, a "To Currency" (varchar) field, and a Rate (float) field. The PK consists of the first 3 fields.
The contents of the table are derived from another table, so what I do is:
1 Delete all rows in that table
2 Load the empty table in a datatable in my already existing dataset
3 Create/add all new rows derived from the other table
4 Update the datatable adapter to insert the newly created rows in the DB table
Step 4 is where I get the ominous "Concurrency violation" error, and I really can't see why, as the source table has a completely similar structure and a primary key.
Here is the code:
Public Sub UpdateFXRates(frm As Form, sText As String)
Dim sTemp As String
Dim RateUSDCHF As Double
Dim dateOfRate As Date
Dim i As Integer
On Error GoTo EH
RateUSDCHF = <the current USD/CHF excahnge rate, value around 0.95>
' Delete all rows in DB table before creating the new rows
Dim cmd As New SqlCommand
' connSQL is a public variable of the module in which this sub is located
cmd = New SqlCommand("DELETE FROM [FX Rates]", connSQL)
' Load empty table in datatable and generate the rows
sTemp = "SELECT * FROM [FX Rates]"
adapterSQL.SelectCommand.CommandText = sTemp
adapterSQL.SelectCommand.Connection = connSQL
' dsMCR is the dataset, also defined as Public
adapterSQL.Fill(dsMCR, "FX Rates")
For i = 0 To dsMCR.Tables("Rates Daily").Rows.Count - 1
' Add new row
' Fill the data in row
dsMCR.Tables("FX Rates").Rows(i).Item("Date of Rate") = dsMCR.Tables("Rates Daily").Rows(i).Item("Date of Rate")
dsMCR.Tables("FX Rates").Rows(i).Item("From Currency") = "USD"
dsMCR.Tables("FX Rates").Rows(i).Item("To Currency") = dsMCR.Tables("Rates Daily").Rows(i).Item("From Currency")
dsMCR.Tables("FX Rates").Rows(i).Item("Rate") = dsMCR.Tables("Rates Daily").Rows(i).Item("Rate") / RateUSDCHF
' Store generated rows in DB table
Dim cmdBuilder As New SqlCommandBuilder(adapterSQL)
adapterSQL.Update(dsMCR, "FX Rates") <-------------- THAT'S where the ERROR happens
sText = i & " rates in FX Rates table loaded for " & Format(dateOfRate, "dd.mm.yyyy")
On Error Resume Next
Call HandleErrors(sAppName, "Error: " & Err.Description & " in UpdateFXRates", dsMCR, connSQL)
When I look at the data in the datatable before the adapter.Update, it looks OK (I just checked randomly a few rows), and, as the source table has just as many rows and the same PK on its first 3 fields, there should not be any problems with the data and therefore with the insert.
Is there an obvious flaw in my code (I'm pretty new to datasets) ? If not, how can I find out on which row there is a problem ?
Thanks for your help