Dataset merging and saving to database

Talarin3 · March 16th, 2006, 11:54 AM

Hey all,

I'm fairly new to ADO.NET and VB.NET and am having some problems with the following code. Basically, Test1 is my master table and Test2 has a lot of the same data as well as some updated data. I want to take only the updated and new data from Test2 and merge it with Test1. I figured dataset merging would be the best option in this case. The code doesn't produce any errors, but the database isn't getting updated. What am I missing?

Code:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module basGlobal

    Sub Main()
        Dim cnnPDA As New SqlConnection(GetPDAConnectionString())
        Dim ds1 As New DataSet(), ds2 As New DataSet()

        cnnPDA.Open()

        Dim strSQL As String = "Select * from Test1"
        Dim da As New SqlDataAdapter(strSQL, cnnPDA)

        da.Fill(ds1, "Test1")

        strSQL = "Select T2.* from Test2 T2 Left Join Test1 T1 on T2.Customer = T1.Customer where "
        strSQL += "(T1.Data1 <> T2.Data1 or T1.Data2 <> T2.Data2 or T1.Data3 <> T2.Data3)"
        da.SelectCommand.CommandText = strSQL

        da.Fill(ds2, "Test1")

        ds1.Merge(ds2)
        da.Update(ds1, "Test1")

        cnnPDA.Close()
    End Sub

End Module

Thanks for your help.

Tal

Talarin3 · March 17th, 2006, 09:34 AM

Well, after much searching I have made some progress on this problem, but a new problem has arisen. Here's the new code.

Code:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module basGlobal

    Sub Main()
        Dim cnnPDA As New SqlConnection(GetPDAConnectionString())
        Dim ds1 As New DataSet(), ds2 As New DataSet()

        cnnPDA.Open()

        Dim strSQL As String = "Select * from Test1"
        Dim da As New SqlDataAdapter(strSQL, cnnPDA)
        Dim cb As New SqlCommandBuilder(da)
        da.AcceptChangesDuringFill = False

        da.Fill(ds1, "Test1")

        Dim pk(1) As DataColumn
        pk(0) = ds1.Tables("Test1").Columns("Customer")
        ds1.Tables("Test1").PrimaryKey = pk

        strSQL = "Select T2.* from Test2 T2 Left Join Test1 T1 on T2.Customer = T1.Customer where "
        strSQL += "(T1.Data1 <> T2.Data1 or T1.Data2 <> T2.Data2 or T1.Data3 <> T2.Data3)"
        Dim da1 As New SqlDataAdapter(strSQL, cnnPDA)

        da1.Fill(ds2, "Test1")

        ds1.Merge(ds2)

        da.Update(ds1, "Test1")
        ds1.AcceptChanges()

        cnnPDA.Close()
    End Sub

End Module

This will attempt to update the database now. Apparently the key was the commandbuilder and setting the AcceptChangesDuringFile = false. But instead of updating the records in the database, it's trying to insert the records. Here's the error I'm getting:

Quote:

quote:Violation of PRIMARY KEY constraint 'PK_Test1'. Cannot insert duplicate key in object 'Test1'.

Why would it be trying to insert records instead of updating the current records? How can I fix this?

Thanks for your time.

Tal

Ki11Roy · March 23rd, 2006, 07:04 PM

I think this will help...
http://www.c-sharpcorner.com/FAQ/Mul...esViewData.asp

Talarin · March 24th, 2006, 12:38 AM

Thanks for the link, Ki11Roy. I think I've got that part down ok. Merging the tables doesn't seem to be the issue, the issue seems to be in trying to update the original database with the merged data. I'm really struggling with this and can't seem to find the right syntax to make it work.

Thanks,
Tal

rumbafum · April 19th, 2006, 05:55 AM

When you use da.AcceptChangesDuringFill = False your dataset rows have the State Added. When you merge the state is not altered. When you do an update and because the rows are added its always tryin to insert.

Talarin3 · April 19th, 2006, 07:24 AM

Thanks rumbafum, your explanation certainly makes sense. I'm now back to my original problem though, if I comment that line out and run the program, it doesn't update the table. It runs without error, but the Test1 table is unchanged. Stepping through the program, it is correctly merging the data and it performs the update command, but when I view the table, the data is unchanged. Any ideas anyone?

Thanks,
Tal

rumbafum · April 19th, 2006, 08:55 AM

Fill makes all rows to become unchanged with the AcceptChangesDuringFill true... When you do merge to another dataset the rows maintain their state. You need to change something in the merged DataSet for the rows to become modified. I think theres no way to do this with an instruction. If you set a value for a row it becomes modified, You can set with the same old value

Talarin3 · April 19th, 2006, 09:02 AM

Ugh, I'm beginning to think that using merged datasets isn't the direction I want to be going. Thanks for your help, rumbafum!

Tal

rumbafum · April 19th, 2006, 11:30 AM

No Prob!