Beldi Anouar Beldi Anouar - 1 year ago 74 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
End Using

Return nbre

My question is how to find the "IdTable1" before
Any help please

Answer Source

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 

    Return CInt(cmd.Parameters("@IdTable1").Value)
End Using
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download