Trasaction and dataset

  • Thread starter Thread starter flavio
  • Start date Start date
F

flavio

hello i would like to know a information
i've a form made to insert a new record in my database
the ID is calculated as a result of max of existing record and than add by
one
(but in this ID there are other data is not a simple counter)

is possible to create a transaction that look a table from the calculation
of maxID to save of record ?


here is my code without trasaction:

Try
Dim r As System.Data.DataRowView
r = Me.RiparazioniBindingSource.Current
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = My.Application.db.Connection
'here i calulate the max of ID

cmd.CommandText = "SELECT MAX(IDRiparazione) from [Riparazioni]"
Dim MaxIDRiparazione As Integer
Try
MaxIDRiparazione = cmd.ExecuteScalar()
Catch ex As Exception
MaxIDRiparazione = 0
End Try

' here is the proc where i calculate the next

r("IDRiparazione") = Format(MaxIDRiparazione + 1, "00000000")

' here i save the data from dataset
Me.Validate()
Me.RiparazioniBindingSource.EndEdit()
Me.RiparazioniTableAdapter.Update(Me.DbDataSet.Riparazioni)

' other dataset to save data
Me.ClientiBindingSource.EndEdit()
Me.ClientiTableAdapter.Update(Me.DbDataSet1.Clienti)

' now i close the form
Me.Close()

' here is my message to say ALL OK
MsgBox("Riparazione salvata con successo", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox("Errore nel salvataggio della riparazione." & vbCrLf & ex.Message,
MsgBoxStyle.Critical)
End Try


excuse me by my previous post in italian
thanks in advance

FLAVIO
 
What database are you working with? Rather than having the whole thing in a
transaction, can you set the ID column in the database to be an IDENTITY
column? When you are doing the insert, you can then return the new ID value.
 
i'm using (for compatibility) mdb database
because the ID for global interchange is not a simple counter but is made by
some data + a local counter:
YYS00000C
where :
YY is th current year
S is the station identifier
000000 is local counter
C is a check

this is a small multiuser environement
and i don't know how to lock a table from the read and calculation of next
ID to the insert method.

thanks in advance
FLAVIO



Dean said:
What database are you working with? Rather than having the whole thing in
a
transaction, can you set the ID column in the database to be an IDENTITY
column? When you are doing the insert, you can then return the new ID
value.



flavio said:
hello i would like to know a information
i've a form made to insert a new record in my database
the ID is calculated as a result of max of existing record and than add
by
one
(but in this ID there are other data is not a simple counter)

is possible to create a transaction that look a table from the
calculation
of maxID to save of record ?


here is my code without trasaction:

Try
Dim r As System.Data.DataRowView
r = Me.RiparazioniBindingSource.Current
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = My.Application.db.Connection
'here i calulate the max of ID

cmd.CommandText = "SELECT MAX(IDRiparazione) from [Riparazioni]"
Dim MaxIDRiparazione As Integer
Try
MaxIDRiparazione = cmd.ExecuteScalar()
Catch ex As Exception
MaxIDRiparazione = 0
End Try

' here is the proc where i calculate the next

r("IDRiparazione") = Format(MaxIDRiparazione + 1, "00000000")

' here i save the data from dataset
Me.Validate()
Me.RiparazioniBindingSource.EndEdit()
Me.RiparazioniTableAdapter.Update(Me.DbDataSet.Riparazioni)

' other dataset to save data
Me.ClientiBindingSource.EndEdit()
Me.ClientiTableAdapter.Update(Me.DbDataSet1.Clienti)

' now i close the form
Me.Close()

' here is my message to say ALL OK
MsgBox("Riparazione salvata con successo", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox("Errore nel salvataggio della riparazione." & vbCrLf & ex.Message,
MsgBoxStyle.Critical)
End Try


excuse me by my previous post in italian
thanks in advance

FLAVIO
Click to expand...
Click to expand...
 
Back
Top