Update a dataset on another database

[sbaljepa]

Hi,
I am relatively new to VB.Net and I have a question :
I have a table "PaperDetails" on one database (SQL Server) and I need to retrieve the data, manipulate it and save the data on another database to a table "PaperDetails" on another database )(SQL Server).
I have tried using the da.Update but that doesn't work. How do I append rows to the destination table.
Any help would be appreciated.
Thanks!

 

[jmcilhinney]

If you're getting the data from one table and then saving it to another then you're inserting the rows I assume. You can use one SqlDataAdapter but the SelectCommand and InsertCommand will have to use two different SqlConnection objects. Also, you need to set the AcceptChangesDuringFill property of the adapter to False so that the RowState of each row remains Added.

 

[sbaljepa]

Hi,
I am enclosing the code that I am still having trouble with. All I want to do is copy a subset of a table from the source to the dest db.
When I am to the statement :
da.SelectCommand.Connection = cn

I get the error : "Object reference not set to an instance of an object"

Also if I were to use InsertCommand, how do I build the Insert SQL?
Can't I get a DataSet from the Source Db and then how do I update the destination table. The table structures are the same on both the dbs and it has primary keys. Help !!


''''this is a subset of data from the Source DB
Dim strPaperDetSQL As String = "select pd.* from paperdetails pd " & _
"inner join project_details prd on pd.projectdetailid=prd.projectdetailid " & _
"inner join project p on prd.projectid=p.projectid " & _
"inner join version v on p.versionid= v.versionid " & _
"inner join event e on v.eventid = e.eventid " & _
"where seasonid = '" & strCurrSeason & "'"
Dim strSource_connString As String = ConfigurationSettings.AppSettings("Source_connectionString")
Dim strDest_connString As String = ConfigurationSettings.AppSettings("Dest_connectionString")
Try
Dim cn As New SqlConnection(strSource_connString)
Dim cnDest As New SqlConnection(strDest_connString)
Dim cmd As New SqlCommand(strPaperDetSQL)
Dim da As New SqlDataAdapter
Dim ds As DataSet
da.SelectCommand.Connection = cn

da.SelectCommand = cmd
da.AcceptChangesDuringFill = False
da.Fill(ds)
da.InsertCommand.Connection = cnDest
da.InsertCommand = cmd ''''''I am not sure of this part


Thanks in advance. Any suggestions would be great...

 

[jmcilhinney]

That's because your DataAdapter has no SelectCommand at that point. You don't assign an object to the SelectCommand property until the next line, so you would have to switch those around.

 

[sbaljepa]

Hi,
Thanks for your prompt reply. The qn I had was do I have to loop through each row on the source db and insert that into a new ds and then use the insertcommand to write the rows to the dest db?

How do I use the dataset to write to the dest db without looping thru? I tried to use the Update method but nothing is written. It says 0 records written. Some sample code would help. Thanks...