Update datagridview with numeric primary key. Solved

Posted by dragon on Thursday, 29 September 2011

The issue, typical in Firebird, consist in the fact that, after you insert a new record  with an autoincrement field in a datagridview, we have to exit from the application to be able to edit the new inserted record.

To solve this issue you have to proceed this way:
1) Proceeding for semplicity with "SQL Manager 2010 lite" software, name the autoincrement field as "Counter"
2) In the database of interest create a new table called for example "Counter" with only a field named "memcont", type Integer.
3) To insert a record use this routine:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ObjConnection As New FbConnection()
Dim connection As FbConnection = New
' If we assume an embedded server
FbConnection("servertype=1;username=sysdba;password=masterkey;database=" _

& My.Application.Info.DirectoryPath & "\mydb.gdb")
connection.Open()

' Starts a local transaction.
Dim fbTran As FbTransaction = connection.BeginTransaction()

' Enlist the command in the current transaction.
Dim command As FbCommand = connection.CreateCommand()
command.Transaction = fbTran

Try
command.CommandText = _
"insert into new_table1" _
& " (Clienti, spesa) values ('" & Me.TextBox1.Text & "','"

_
& Me.TextBox2.Text & "')"
command.ExecuteNonQuery()
command.CommandText = _
"delete from counter"
command.ExecuteNonQuery()
'There we make use of the generator GEN_ID function of Firebird with no increment
command.CommandText = _
"INSERT INTO counter(memcont)
VALUES(GEN_ID(new_table1_COUNTER_GEN, 0))"
command.ExecuteNonQuery()
fbTran.Commit()Catch ex As Exception
MsgBox("Insert a Integer number for field spesa")

fbTran.Rollback()Finally
ObjConnection.Close()
End Try

'There we create an instance of the new row in the datatable
Dim persRow As DataRow = Principale.DataSet1.Tables("counter").NewRow
If IsDBNull(persRow("memcont")) Then
persRow("memcont") = Principale.DataSet1.Tables("New_table1").Rows.Count
End If
Principale.DataSet1.Tables("counter").Rows.Add(persRow)
'There we create an instance of the row 0 of the "counter" datatable


Dim CustomersRow As DataRow = Principale.DataSet1.Tables("counter").Rows(0)
'There we create an instance of the new row of the "New_table1" datatable
Dim newCustomersRow As DataRow = Principale.DataSet1.Tables("new_table1").NewRow()
' The autoincremnt field value musts reflect the "memcont" value +1
newCustomersRow("counter") = CustomersRow("MEMCONT") + 1
' there we assign to the unique record in the field "memcont" of the datatable "counter" the
new value of the autoincrement field

CustomersRow("MEMCONT") = newCustomersRow("counter")
newCustomersRow("CLIENTI") = Me.TextBox1.Text
newCustomersRow("spesa") = Me.TextBox2.Text


Principale.DataSet1.Tables("new_table1").Rows.Add(newCustomersRow)

Me.Close()

End Sub

{ 0 comments... read them below or add one }

Post a Comment