CODEWITHSUNDEEP
vb.nettransactions
govinda sinha
govinda sinha

Reputation: 29

transaction in ms access and vb.net

I am trying the below code but not getting executed. 

Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click
    Using con As New OleDbConnection(connectionString)
        Dim tra As OleDbTransaction = Nothing

        Try
            con.Open()
            cmd.Transaction = tra
            tra = con.BeginTransaction
            Dim sqlstr As String = "insert into category(cname,comment)  values('" + txtcategory.Text + "','" + txtcomment.Text + "')"
            cmd = New OleDb.OleDbCommand(sqlstr, con)
            cmd.ExecuteNonQuery()

            Dim sql As String = "UPDATE tblInvoices SET avail = 1 WHERE (cname = txtcategory.Text)"
            cmd = New OleDb.OleDbCommand(sqlstr, con)
            cmd.ExecuteNonQuery()
            tra.Commit()

        Catch ex As Exception
            MsgBox(ex.Message)
            Try : tra.Rollback() : Catch : End Try

        End Try


    End Using
End Sub

I don't understand the transactions.

Upvotes: 0

Views: 2631

Answers (1)

Steve
Steve

Reputation: 216303

The command need to know about the existence of a Transaction. But you assign the Transaction instance before the opening the connection and then ask the connection to start the transaction.
In this way the command has a null reference for the transaction and not the good instance created by the connection. Also, when you create again the command, there is no transaction associated with it.
Better use the OleDbCommand constructor that takes a Transaction as third parameter

Private Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click
    Using con As New OleDbConnection(connectionString)
        Dim tra As OleDbTransaction = Nothing

        Try
            con.Open()
            tra = con.BeginTransaction
            Dim sqlstr As String = "insert into category(cname,comment)" &
                                   " values(@cat, @com)"
            cmd = New OleDb.OleDbCommand(sqlstr, con, tra)
            cmd.Parameters.Add("@cat", OleDbType.VarWChar).Value = txtcategory.Text
            cmd.Parameters.Add("@com", OleDbType.VarWChar).Value =  txtcomment.Text 
            cmd.ExecuteNonQuery()

            Dim sql As String = "UPDATE tblInvoices SET avail = 1 " & 
                                "WHERE cname = @cat"
            cmd = New OleDb.OleDbCommand(sqlstr, con, tra)
            cmd.Parameters.Add("@car", OleDbType.VarWChar).Value = txtcategory.Text
            cmd.ExecuteNonQuery()
            tra.Commit()

        Catch ex As Exception
            MsgBox(ex.Message)
            tra.Rollback() 
       End Try
    End Using
End

I have also changed your code to use a more safe approach to your queries. Instead of using a string concatenation use ALWAYS a parameterized query. In this way you are safe from Sql Injection, you don't have problems with parsing texts and your queries are more readable.

Upvotes: 2

Related Questions