Results 1 to 10 of 10

Thread: DataTable Adding Pirmary Key Values when adding a new row

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    DataTable Adding Pirmary Key Values when adding a new row

    I have a Primary Key that is an integer

    I have a DataTable that has many rows from which I have deleted some.

    When adding a new row I query the DataTable for the Highest Number and Assign that Number + 1 to the primary key column of the new row.

    When execute the Update command to the Data Adapter I get an error indicating possible Duplicate values in my Primary Column

    I suspect that the primary key value that I am adding also belongs to one of my deleted rows.

    How can I add a unique primary key value

    Thanks

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: DataTable Adding Pirmary Key Values when adding a new row

    Rather than generating the number yourself, set the field to be an Identity field (or called AutoNumber in Access based databases). The details of how you do that vary based on the database system you are using.

    Using an Identity field will automatically deal with various issues (some of which you probably wouldn't think of), including making sure that the new value is valid to use.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: DataTable Adding Pirmary Key Values when adding a new row

    You should do as si suggests. When you use a data adapter to Fill a DataTable, set its MissingSchemaAction property to AddWithKey. That will configure the DataTable to generate temporary keys automatically. When you use the data adapter to Update the database with the changes from the DataTable, the database will generate final keys for all the new rows. Depending on what database you're using and how you have configured your data adapter and DataTable, those final keys may be pushed back into your DataTable and replace the temporary keys.

    If you won't be using the DataTable any further after saving then getting the final keys isn't necessary. If you will use the data further then it is important to keep the keys current. One example of where it is critical is when you're saving hierarchical data. Let's say that you have a parent table and a child table. The parent DataTable will generate temporary primary keys and those values will be used as foreign keys in the child DataTable. When you save the parent data, the final keys generated by the database may be different to the temporary keys in the parent DataTable. In that case, the final values must be pushed back into the parent DataTable and across the foreign key relation to the child DataTable so that, when you save the child data, it contains foreign key values that are valid in the database.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    Re: DataTable Adding Pirmary Key Values when adding a new row

    I had previously created some conversion code to update a Database to have a primary key, but I had failed to implement the "AutoIncrement" property

    I created a new conversion routine to run on Databases that have not been converted which is show below.



    Code:
        Public Sub DBConvert88A()
    
            On Error GoTo ErrorHandler
            Dim cn As ADODB.Connection
            Dim oldCat As ADOX.Catalog
            Dim newCat As ADOX.Catalog
            Dim oldTbl As ADOX.Table
            Dim newTbl As ADOX.Table
            Dim oldcol As ADOX.Column
            Dim newView As ADOX.View
            Dim oldView As ADOX.View
            Dim cmdview As ADODB.Command
            Dim sTblNames() As String
            Dim itbl As Integer = 0
    
    
        
            frmMain.ProgressBar1.Visible = True
            frmMain.ProgressBar1.Minimum = 1
            frmMain.ProgressBar1.Maximum = 220
            frmMain.ProgressBar1.Value = 1
            frmMain.ProgressBar1.Step = 1
            frmMain.fraProgress.Text = "Converting Database  88 "
            frmMain.fraProgress.BringToFront()
            frmMain.fraProgress.Show()
    
    
            ReDim sTblNames(200)
            oldCat = New ADOX.Catalog
            newCat = New ADOX.Catalog
            cn = New ADODB.Connection
    
    
            cn.Provider = "Microsoft.Jet.OLEDB.4.0"
            cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp_PumpBuilder\Temp_PumpBuilder_Database.mdb" '"Data Source=C:\temp\database1.mdb;User Id=admin;Password=;"
            cn.Open()
            newCat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp_PumpBuilder\Temp_PumpBuilder_Database1.mdb")
            oldCat.ActiveConnection = cn
    
            For Each oldTbl In oldCat.Tables
                Select Case oldTbl.Type
                    Case "TABLE"
                        newTbl = New ADOX.Table
                        With newTbl
                            .Name = oldTbl.Name
                            sTblNames(itbl) = oldTbl.Name : itbl += 1
                            For Each oldcol In oldTbl.Columns
                                .Columns.Append(oldcol.Name, oldcol.Type, oldcol.DefinedSize)
                            Next
                            .ParentCatalog = newCat
                            .Columns.Append("PBKey", ADOX.DataTypeEnum.adInteger)
                            With .Columns("PBKey")
                                .Properties("AutoIncrement").Value = True
                            End With
                            .Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "PBKey")
                        End With
                        newCat.Tables.Append(newTbl)
                    Case "VIEW"
                        '----------------------------------------------------------
                        'DO NOTHING APEND VIEW DOES ALL THAT IS NEEDED
                        '----------------------------------------------------------
                End Select
                frmMain.ProgressBar1.PerformStep()
            Next
    
            For Each oldView In oldCat.Views
                cmdview = New ADODB.Command
                With cmdview
                    .ActiveConnection = newCat.ActiveConnection
                    .CommandText = oldView.Command.CommandText
                End With
                newCat.Views.Append(oldView.Name, cmdview)
                cmdview = Nothing
            Next
    
    
            ReDim Preserve sTblNames(itbl - 1)
            oldCat.ActiveConnection.Close()
            newCat.ActiveConnection.Close()
    
            Dim ThisOldTable As ADOX.Table
            Dim ThisNewTable As ADOX.Table
            Dim rsOld As New ADODB.Recordset
            Dim rsNew As New ADODB.Recordset
            Dim cnPBOld As New ADODB.Connection
            Dim cnPBNew As New ADODB.Connection
            Dim sDatabaseNew As String
            Dim sDatabaseOld As String
            Dim iFld As Integer
            Dim sfldName As String
    
            sDatabaseOld = "C:\Temp_PumpBuilder\Temp_PumpBuilder_Database.mdb"
            cnPBOld.Open("Provider=microsoft.jet.OLEDB.4.0; Data Source=" & sDatabaseOld)
    
            sDatabaseNew = "C:\Temp_PumpBuilder\Temp_PumpBuilder_Database1.mdb"
            cnPBNew.Open("Provider=microsoft.jet.OLEDB.4.0; Data Source=" & sDatabaseNew)
    
            For itbl = 0 To sTblNames.GetUpperBound(0)
    
    
                rsOld.Open(sTblNames(itbl), cnPBOld, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                rsNew.Open(sTblNames(itbl), cnPBNew, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
                If rsOld.EOF = False Then
                    With rsOld
                        .MoveFirst()
                        Do Until .EOF
                            rsNew.AddNew()
                            For iFld = 0 To rsOld.Fields.Count - 1
                                sfldName = rsOld.Fields(iFld).Name
                                If IsDBNull(rsOld.Fields(sfldName).Value) Then
                                    Select Case rsNew.Fields(sfldName).Type
                                        Case adVarChar
                                            rsNew.Fields(sfldName).Value = ""
                                        Case Else
                                            rsNew.Fields(sfldName).Value = 0
                                    End Select
                                Else
                                    rsNew.Fields(sfldName).Value = rsOld.Fields(sfldName).Value
                                End If
                            Next
                            rsNew.Update()
                            .MoveNext()
                        Loop
                    End With
                End If
                rsOld.Close()
                rsNew.Close()
    
                frmMain.ProgressBar1.PerformStep()
            Next itbl
            cnPBNew.Close()
            cnPBOld.Close()
    
            frmMain.fraProgress.Hide()
    
            Kill(sDatabaseOld)
            My.Computer.FileSystem.RenameFile(sDatabaseNew, "Temp_PumpBuilder_Database.mdb")
            Exit Sub
    
    ErrorHandler:
    
            Dim sThisMethod As String
            Dim sThisModule As String
            Dim mThisMethod As System.Reflection.MethodInfo  '.GetCurrentMethod
            mThisMethod = System.Reflection.MethodInfo.GetCurrentMethod
            sThisModule = mThisMethod.ReflectedType.Name
            sThisMethod = mThisMethod.Name
            Dim st As New StackTrace(True)
            Dim sf As StackFrame = st.GetFrame(0)
            Dim ThisLineNo As Long = sf.GetFileLineNumber()
            If Err.Number < 20 Then
                Call CommonRoutines.ErrorReport(Err.Number, Err.Description, sThisModule, sThisMethod, "Sub")
                Exit Sub
            End If
            'Any other error
            Call CommonRoutines.ErrorReport(Err.Number, Err.Description, sThisModule, sThisMethod, "Sub")
        End Sub


    Then I modified my Load Process to apply the MissingSchemaAction.AddWithKey property


    Code:
    	Sub LoadPumpBuilderDatabase(MyDatabase As String)
    		On Error GoTo ErrorHandler
    		Dim b As Boolean
    
    
    		Dim Sql As String
    		PBcon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp_PumpBuilder\Temp_PumpBuilder_Database.mdb"
    		PBcon.Open()
    
    		'Get a list of the tables in the database
    		Dim objDataTable As DataTable = PBcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    		Dim arrListTables As New ArrayList
    		Dim i As Integer
    		For i = 0 To objDataTable.Rows.Count - 1
    			gsTableNames(i) = objDataTable.Rows(i)(2)
    		Next
    
    		For i = 0 To UBound(gsTableNames)
    			Sql = "Select * from " & gsTableNames(i)
    			gda(i) = New OleDb.OleDbDataAdapter(Sql, PBcon)
    			gcb(i) = New OleDbCommandBuilder(gda(i)) With {.QuotePrefix = "[", .QuoteSuffix = "]"}
    			gdt(i) = New DataTable
    			gda(i).MissingSchemaAction = MissingSchemaAction.AddWithKey
    			gda(i).Fill(gdt(i))
    			b = IsPrimaryKeyColumn(gdt(i), gdt(i).Columns("PBKey"))
    			If b = False Then MsgBox("Primary Key Failure")
    		Next i
    
    		PBcon.Close()
    
    		Exit Sub
    
    ErrorHandler:
    
    		Dim sThisMethod As String
    		Dim sThisModule As String
    		Dim mThisMethod As System.Reflection.MethodInfo
    		mThisMethod = System.Reflection.MethodInfo.GetCurrentMethod
    		sThisModule = mThisMethod.ReflectedType.Name
    		sThisMethod = mThisMethod.Name
    		Dim st As New StackTrace(True)
    		Dim sf As StackFrame = st.GetFrame(0)
    		Dim ThisLineNo As Long = sf.GetFileLineNumber()
    		If Err.Number < 20 Then
    			Call CommonRoutines.ErrorReport(Err.Number, Err.Description, sThisModule, sThisMethod, "Sub")
    			Exit Sub
    		End If
    		'Any other error
    		Call CommonRoutines.ErrorReport(Err.Number, Err.Description, sThisModule, sThisMethod, "Sub")
    
    	End Sub

    I hope this addresses the recommendations that have been provided

    This seems to have resolved my problem

    Many thanks
    Richard

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: DataTable Adding Pirmary Key Values when adding a new row

    to alter a Table and add PrimaryKey with index you can do that like this

    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim sSQL As String
            'your Database
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\Contacts.mdb")
            con.Open()
            'create the AutoIncrement named NewPBKey and add Index named PRIMARYKEY
            sSQL = "ALTER TABLE tblContacts Add NewPBKey AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY "
            ExecuteSQL(con, sSQL)
    
            con.Close()
        End Sub
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                        ByVal sSQL As String, _
                                        Optional ByRef ErrMessage As String = Nothing, _
                                        Optional ByVal TransAction As  _
                                        OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    Re: DataTable Adding Pirmary Key Values when adding a new row

    Thanks,
    I am not sure you can do this on a Database Table that is already populated, but this would fit in my first routine above where I create an empty clone of the database and its tables, add the key, and then back fill from the old database.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: DataTable Adding Pirmary Key Values when adding a new row

    it will add to an existing table with data
    the number will start with 1 .... to whatever rows you have

    if you need a FOREIGN KEY in another Table things will get more
    complicated
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    Re: DataTable Adding Pirmary Key Values when adding a new row

    OK, I like it

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Nov 2019
    Posts
    108

    Re: DataTable Adding Pirmary Key Values when adding a new row

    This almost works.

    It fails however on tables that already have a primary key (not of the same name)

    Any suggestions?

  10. #10
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: DataTable Adding Pirmary Key Values when adding a new row

    Quote Originally Posted by Richard Friedman View Post
    This almost works.

    It fails however on tables that already have a primary key (not of the same name)

    Any suggestions?
    is the existing Primary Key a Autoincrement Field?

    if so you have to do that in 2 Stages

    the existing PrimaryKey
    a) if there is a Index on the existing Primary Key you have to Drop that first
    b) change it from AutoIncrement to Integer

    now you can add the new Autoincrement like I should you in Post#5
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width