Beldi Anouar Beldi Anouar - 6 months ago 16
Vb.net Question

Retrieving Autonumber Values before Commit transact

I have an issue with retrieving Autonumber Values before Commit tarnsact,
I have two tables :"Table1" which contains a column with Autonumber ,and Table2 .
this is my code :

Dim req As String = "insert into Table1 values(@val1,@val2)"
Using cmd As SqlCommand = New SqlCommand(req, con)
cmd.Parameters.Add(New SqlParameter("@val1", MyVal1))
cmd.Parameters.Add(New SqlParameter("@val2", MyVal2))
cmd.Transaction = transaction
nbre = cmd.ExecuteNonQuery()
End Using

req = "insert into Table2 values(@IdTable1, @Val)"
Using cmd As SqlCommand = New SqlCommand(req, con)
cmd.Parameters.Add(New SqlParameter("@IdTable1", ?????))
cmd.Parameters.Add(New SqlParameter("@Val",MyVal))
cmd.Transaction = transaction
cmd.ExecuteNonQuery()
End Using

transaction.Commit()
con.Close()
Return nbre


My question is how to find the "IdTable1" before
transaction.Commit()
?
Any help please

Answer

You can do all this in one trip to the database. This makes it easier to handle the ID value, as well as eliminating the need for the transaction:

'Need VS2015 for the multiline string literal. Otherwise use concatenation
Dim req As String = "
    INSERT INTO Table1 VALUES (@val1,@val2);
    SELECT @IdTable1 = scope_identity();
    INSERT INTO Table2 VALUES (@IdTable1, @Val);"

Using con As New SqlConnection("connection string here"), _
      cmd As New SqlCommand(req, con)
    'Guessing at parameter types here... it's often better to be explicit about this
    cmd.Parameters.Add("@val1", SqlDbtype.VarChar, 50).Value = MyVal1
    cmd.Parameters.Add("@val2", SqlDbType.NVarChar, 200).Value =  MyVal2
    cmd.Parameters.Add("@Val", SqlDbType.Int).Value = MyVal
    cmd.Parameters.Add("@IdTable1", SqlDbType.Int)
    cmd.Parameters("@IdTable1").Direction = ParameterDirection.Output 

    con.Open()
    cmd.ExecuteNonQuery()
    Return CInt(cmd.Parameters("@IdTable1").Value)
End Using