Update with SQL Data Adapter

[Matt Tapia]

I have two tables in my sql database with the same structure. One table has
5 records and the other has 1 record. I want to take the 5 records into 1
dataset, and the 1 record into another dataset, merge into the data into one
dataset (the first) and update the table with 5 records so now it has the
additional 1.

Everything works fine with code below with the merging, however when I run
the Update method with my sql adapter, the table does not get the additional
record...what is going on?

-----------------------------------------------------------

Try

Dim strConn As String

strConn = "data source=" & ServerName & _
"; initial catalog=" & Database & _
"; uid=" & UserName & ";pwd=" & Password

Dim conn As SQLConnection = New SQLConnection(strConn)

Dim strSQL as String

strSQL = "SELECT * FROM PDABookMarks"

conn.Open()

Dim cmdGetProfile As SqlDataAdapter = New SqlDataAdapter (strSQL, conn)

Dim profileCB as SqlCommandBuilder = New SqlCommandBuilder(cmdGetProfile)

cmdGetProfile.InsertCommand = profileCB.GetInsertCommand()

cmdGetProfile.UpdateCommand = profileCB.GetUpdateCommand()


Dim dsProfile As DataSet

dsProfile = New DataSet()

cmdGetProfile.Fill(dsProfile)

conn.Close()

strSQL = "SELECT * FROM PDABookMarks2"

conn.Open()

Dim cmdGetProfile2 As SqlDataAdapter = New SqlDataAdapter (strSQL, conn)

Dim myData As DataSet

myData = New DataSet()

cmdGetProfile2.Fill(myData)

dsProfile.Merge(myData)

cmdGetProfile.Update(dsProfile)

conn.Close()

Return (dsProfile)

Catch

Return (Nothing)

End Try

 

[Earl]

This would be easier and more efficient to simply run an INSERT INTO
procedure on the server.

 

[TJO]

Examine the states of each row in your merged dataset table to see what
their states are after you merge the data.
Also examine your datadatper's commands to make sure they are all populated.

 

[Matt Tapia]

Really, it seems like I would have to build the insert statements from the
dataset information and do several for each row in the dataset...how is that
easier?

 

[Matt Tapia]

what do you mean by the state of each row? what commands should be populated
for my sql adapter?

 

[Earl]

If all you are doing is moving data from database table to database table
then you have no need for the dataset at all.