Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
Posted 26-Apr-13 5:35am
Comments
Kschuler at 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: http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.fillschema(v=vs.71).aspx
StephanGumpert at 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 at 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 at 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)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,192
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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