|
Subject:
|
Dataset merging and saving to database
|
|
Posted By:
|
Talarin3
|
Post Date:
|
3/16/2006 10:54:20 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?
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
|
|
Reply By:
|
Talarin3
|
Reply Date:
|
3/17/2006 8:34:01 AM
|
Well, after much searching I have made some progress on this problem, but a new problem has arisen. Here's the new 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:
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
|
|
Reply By:
|
Ki11Roy
|
Reply Date:
|
3/23/2006 6:04:29 PM
|
I think this will help...
http://www.c-sharpcorner.com/FAQ/MultiTablesViewData.asp
|
|
Reply By:
|
Talarin
|
Reply Date:
|
3/23/2006 11:38:46 PM
|
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
|
|
Reply By:
|
rumbafum
|
Reply Date:
|
4/19/2006 5:55:58 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.
|
|
Reply By:
|
Talarin3
|
Reply Date:
|
4/19/2006 7:24:11 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
|
|
Reply By:
|
rumbafum
|
Reply Date:
|
4/19/2006 8:55:35 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
|
|
Reply By:
|
Talarin3
|
Reply Date:
|
4/19/2006 9:02:53 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
|
|
Reply By:
|
rumbafum
|
Reply Date:
|
4/19/2006 11:30:37 AM
|
No Prob!
|
