Dealing with Transactions in VS.NET 2005 1

[dashen]

I am writing a test program in ASP.NET that simulates something that my company is doing on one of their applications. The old application is written in Filemaker. I am going to rewrite it in ASP.NET and have the backend on SQL Server 2005. This program will then be used by many users (2000+), so I wanted to impliment transactions to take care of any redundancy or DE errors found. i.e. - If their is a DE error, then I just RollBack the Transaction. Is this feasible for that many users, and what is the error in my code. Thanks.

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnString As String = "Data Source=CSC-025027;Initial Catalog=Test;Integrated Security=True"

        Dim myConnection As New SqlConnection(myConnString)

        Dim mySqlCmd As New SqlCommand("", myConnection) '"sp_InsertTest", myConnection
        'mySqlCmd.Connection = myConnection
        mySqlCmd.Connection.Open()

        'Begin a Transaction on the specific connection.
        Dim myTrans As SqlTransaction = myConnection.BeginTransaction

        Try
            'Begin Try...Catch...Finally
            mySqlCmd.CommandText = "sp_InsertTest"
            mySqlCmd.CommandType = CommandType.StoredProcedure

            Dim myParam1 As SqlParameter
            myParam1 = mySqlCmd.Parameters.Add("@varSPN", SqlDbType.Int, 4)
            myParam1.Value = GridView1.Rows(0).Cells(0).Text

            'mySqlCmd.ExecuteNonQuery()

            '******************************************************
            '* Executing the query in the Try...Catch Method for  *
            '* transactions, causes errors.  Need to find out why *
            Dim myReader As SqlDataReader = mySqlCmd.ExecuteReader

            myReader.Read()
            lblID.Text = myReader.Item(0).ToString

            myReader.Close()
            '******************************************************

            'If no errors have occurred, then commit all of the changes to the database.
            myTrans.Commit()

        Catch
            'If any errors occur, then roll back the transaction.
            myTrans.Rollback()
            MsgBox("An error occurred with one of the database " & _
                "updates. None of the changes were saved to the " & _
                "database.")

        Finally
            'Close the database connection
            myConnection.Close()

        End Try

    End Sub

This was the error:
InvalidOperationException was unhandled by user code

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

 

[dashen]

I have looked up a few sites, and it seems that the Transaction object does not like Stored Procedures. Any way to get around this problem?

 

[dashen]

This is somethnig that I looked up.

"The bad news is that this is a bug in DeriveParameters. It does not check
to see if the command is enlisted in a transaction before requesting the
parameters from the server.

The good news is that you shouldn't be using DeriveParameters anyway. You
need to know the parameters for your stored procedures at design time
anyway, so just code them into your VB."

The parameters on my SP are dynamic, hence the parameters, so is there any way to get around this error?

 

[jbenson001]

Let the database handle the trancsactions. Put the transactoin handling in the SP.

Jim

 

[dashen]

Thanks jbenson001,
Yeah, I figured that I could do that as well just setting being trans and checking for @@error. But the problem is that I am going to have to do another stored procedure that will need to recursively enter "many" rows in another table tied to the "one" row in this transaction. That way if there is a problem in either of these SP, both will roll back. I added a begin transaction in the stored procedure for now, but I will comment it out as soon as I write out the rest of it.

I figured it out, though. I needed to set the commands transaction to the transaction as well.

'Important to set the transaction to the command.
        mySqlCmd.Transaction = myTrans

 

[jbenson001]

cool.. glad you figured it out. I will keep this in mind if I decide to try it this way..

Jim