-
Dec 9th, 2020, 05:00 PM
#1
Thread Starter
Lively Member
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
-
Dec 9th, 2020, 05:33 PM
#2
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.
-
Dec 9th, 2020, 07:19 PM
#3
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.
-
Dec 10th, 2020, 12:56 PM
#4
Thread Starter
Lively Member
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
-
Dec 11th, 2020, 04:36 AM
#5
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.
-
Dec 11th, 2020, 08:49 AM
#6
Thread Starter
Lively Member
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.
-
Dec 11th, 2020, 08:57 AM
#7
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.
-
Dec 11th, 2020, 09:01 AM
#8
Thread Starter
Lively Member
Re: DataTable Adding Pirmary Key Values when adding a new row
-
Dec 13th, 2020, 09:20 AM
#9
Thread Starter
Lively Member
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?
-
Dec 13th, 2020, 01:41 PM
#10
Re: DataTable Adding Pirmary Key Values when adding a new row
Originally Posted by Richard Friedman
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|