3 operations depending on the first one

[angelica]

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.

[mendhak]

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

[angelica]

thanks Menhak,

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

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!

[angelica]

hi,

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

[vijy]

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

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

[vijy]

Transaction in vb.net

[mendhak]

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.