?
Solved

Can't seem to succeed to save datatable to database, VB.net (2013)

Posted on 2014-10-27
4
Medium Priority
?
363 Views
Last Modified: 2014-10-28
Hi

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
        connSQL.Open()
        cmd = New SqlCommand("DELETE FROM [FX Rates]", connSQL)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        connSQL.Close()

        ' 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
            dsMCR.Tables("FX Rates").NewRow()
            dsMCR.Tables("FX Rates").Rows.Add()
            dsMCR.Tables("FX Rates").AcceptChanges()
            ' 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
        Next
        ' 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")

XH:
        On Error Resume Next
        Exit Sub

EH:
        Call HandleErrors(sAppName, "Error: " & Err.Description & " in UpdateFXRates", dsMCR, connSQL)
        Resume XH

    End Sub

Open in new window


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
Bernard
0
Comment
Question by:bthouin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Expert Comment

by:ktaczala
ID: 40406373
It's bad practice to use your actual data as a primary key .  Add another field called dataID as integer have that auto increment and assign it as Primary Key. Then the DB will take care of that field.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40406387
@ktaczala
>>It's bad practice to use your actual data as a primary key <<
Maybe. But I'm accessing an existing DB with about 100 tables, and probably 80% use partial key defined in a similar way, so I have to deal with it the way it is.

And your post is not answering my 2 questions.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 40406783
When you add a new row, its RowState is marked as Added. When you call AcceptChanges, it is changed to Unchanged. Then, when you change the values in the row, it is marked as Modified.

When you run the Update, the DataAdapter sees the row as modified, and triggers an UPDATE command. The row does not already exist in the database, and you receive your error.

Remove AcceptChanges so that the row keeps its RowState property to Added, and I think it will work.
0
 
LVL 1

Author Comment

by:bthouin
ID: 40408283
Merci Jacques ! That was exactly the kind of explanations I needed. Works like a charm.

Actually, I used the recommended method:
- define a new row as dtNewRow = dataset.Tables("<table name>").NewRow()
- fill the data in this dtNewRow
- add the row when all fields are set, using the dtNewRow
- update the adapter for the table

(so no more AcceptChanges anyway)

and in the cases where I rebuild the table, delete first all rows in the physical DB table.

Thanks a ton.
Bernard
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question