Click here to Skip to main content
14,638,959 members
Rate this:
Please Sign up or sign in to vote.

Thanks in advance....

I have a 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.


    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
                'Only Delete is DatRow is equal To the Current File Name
                If DatRow.Item(2) = CurrentFileName Then
                End If
            End If
    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 + _

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 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.

Kschuler 26-Apr-13 17:13pm
How are you loading the NewDataSet? Do you manually create the tables and the columns in them? If so, can you get the schema from the CurrentDataSet before you load data into the NewDataSet? That way you'd know that all of your relationships will match. See MSDN:
StephanGumpert 27-Apr-13 8:32am
Thanks for the reply but my problem is more on how I fill the column with data which normally would be filled by the SQL Database on the server side.

Or are you saying that this can be done by this?
Kschuler 29-Apr-13 8:50am
Yes. Either use .GetSchema or do a fill on the datatable before you add records but make sure that you don't pull any records by using WHERE 1=2. That is another way to get just the schema of a table.
StephanGumpert 30-Apr-13 4:03am
Using your Idea works a treat to get the DataSet and all accosiated properties etc.

My problem still exsits but.

Say I have 5 Columns in my Table.
The Last 4 are just pure data.
The First is my primary Key with the property of "Do Not Allow Null" set. This column data is AutoGenerated on the SQL database.

My problem is I cant add a row to the Dataset.Table with out that First column value. (it errors complainaing about NULL Values)

But how do I know what to put in that column so as to not produce duplicate data in that column. (remembering that this column is the Primary key Column)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100