VB.NET Update, Insert, Delete Records in SQL Database

See more:

VB

SQL-Server-2008R2

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
                'Do Nothing it exists will be covered by the merge
            Else
                'Only Delete is DatRow is equal To the Current File Name
                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