3 operations depending on the first one

Hi,

I need to do 3 inserts into 3 different tables.

1 Is to link to a TABLE 1 insert values and get the scope identity

2. Use the scope identity and fill in the rest of the values of TABLE 2.

3. Make a comparison between a value in TABLE 1 and another in TABLE 3 and Update values into TABLE 3.


Step 2 and 3 depend on Step1 in a sense that if the insert into Table 1 is not successful then Step 2 and 3 must not exist.

I have read some info about transaction and rollback. Can experience programmer please tell me how to go about this in the most efficient and secure way.

Thanks.

I'd do this in a stored procedure rather than in code because the three operations you're performing require no 'code' intervention.

http://www.firstsql.com/tutor5.htm

thanks Menhak,

Will try but Im not an expert programmer as you are:D .

Wish I could post a reputation to you but I need to spread some mysefl before Im allowed for one. Anyways many thanks .

Might need some more help though to get this going!

hi,

Any link on how to code the vb.net and the SP please on transaction?

The basic is here...
Here am executing sp_help stored procedore.

vb Code:

Try
Imports System.Data.SqlClient
 
Module MainModule
 
    Sub Main()
        Dim connectionString As String = _
          "Connection string"
 
        Dim connection As SqlConnection = New SqlConnection(connectionString)
        connection.Open()
        Try
            Console.WriteLine(Now)
            Dim command As SqlCommand = New SqlCommand("sp_help", connection)
          'Here you want to mention that command Type is stored procedure
           command.CommandType = CommandType.StoredProcedure             
 
            'Execute the command 
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(Command)
 
            Dim table As DataTable = New DataTable
            adapter.Fill(table)
            Console.WriteLine(table.Rows.Count)
            Console.Read()
        Finally
            connection.Close()
        End Try
    End Sub
 
End Module
End Try

hope this will help

Transaction in vb.net

You already know how to call stored procedures. If you follow the link I gave on you BEGIN TRANSACTION statements, you only need to start working on your SQL.