Trasaction and dataset

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.



"flavio" wrote:

> 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
>
>
>
>

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" <Dean@discussions.microsoft.com> ha scritto nel messaggio
news:5ECB0EF3-42CC-4CD8-89B0-B9C11AD7B290@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>>
>>