Hi,
Thanks in advance....
I have a VB.net Project using SQLDataAdapter, SQLDataConnection to apply data additions / Changes to a SQL Database on SQL 2008 R2 Database.
The first column in my table is the primary key and is AutoGenerated by the SQL.
MyCode:
Try
mDataAdapter = New SqlDataAdapter(SelectCommandString, mDatabaseConnection)
Dim CurrentDataSet As DataSet = New DataSet
mDataAdapter.Fill(CurrentDataSet, mModelDBTable)
Dim CB As SqlCommandBuilder = New SqlCommandBuilder(mDataAdapter)
mDataAdapter.InsertCommand = CB.GetInsertCommand(True)
mDataAdapter.UpdateCommand = CB.GetUpdateCommand(True)
mDataAdapter.DeleteCommand = CB.GetDeleteCommand(True)
If CurrentDataSet.Tables.Count > 0 And CurrentDataSet.Tables(0).Rows.Count > 0 Then
For Each DatRow As DataRow In CurrentDataSet.Tables(0).Rows
If NewDataSet.Tables(0).Rows.Contains(DatRow.Item(0).ToString) Then
Else
If DatRow.Item(2) = CurrentFileName Then
DatRow.Delete()
End If
End If
Next
CurrentDataSet.Merge(NewDataSet)
Else
CurrentDataSet.Merge(NewDataSet)
End If
mDataAdapter.Update(CurrentDataSet, mModelDBTable)
Catch ex As Exception
MsgBox("Error While SAVEDATATOFILE, " + vbCrLf + _
"Statement " + SelectCommandString + vbCrLf + _
"Reading Table: " + mModelDBTable + vbCrLf + _
"Default Message: " + vbCrLf + _
ex.Message)
Finally
DisConnectFromDB()
End Try
Problem I have is that the DataSet from the actual Database contains the primary key and the DataSet which I create from my data has no Primary key so hence the code:
mDataAdapter.UpdateCommand = CB.GetUpdateCommand(True)
mDataAdapter.DeleteCommand = CB.GetDeleteCommand(True)
Doesn't work the DataTable.Merge requires Primary Keys to merge.
How can I add the Primary key to the fields with in the "NewDataSet" so that the merge method works correctly.
From what I understand a DataSet from an SQL DB is disconnected.
The First Column is called "ROWGUID" and has a vb.net System.Type of Byte.
Everything I read leads to "@@Indenity" ..... Adding with parameter... but I am not 100% sure as I am new to SQL DB.
Any help would be greatly appreciated. (EVEN if in c# or exiting articles)
Again thanks for the help.
Stephan