Question How to SqlTransaction?

Mazhar

Member
Joined
Aug 27, 2016
Messages
15
Programming Experience
Beginner
Aslam o alikum
I m using vb.net 2010 with sql server 2014, at present I m inserting the data in three tables with following code
VB.NET:
Private Sub BtnSave_Click(sender As System.Object, e As System.EventArgs) Handles BtnSave.Click
        If PurchaseGrid.Rows.Item(0).Cells(0).Value = "" Or PurchaseGrid.Rows.Item(0).Cells(2).Value = "" Then
            MessageBox.Show("No Bill Details found", "Grid Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        PurchaseGrid.AllowUserToAddRows = False

        Try
            GetConnect()

            Using cmd As New SqlCommand("INSERT INTO PurchaseMaster (PurBillNo,PurBillDate,VendorID,PurAmount,Remarks) Values (@PurBillNo,@PurBillDate,@VendorID,@Puramount,@Remarks)", Conn)
                Conn.Open()
                'parameters 
                cmd.Parameters.AddWithValue("@PurBillNo", MyTxtBillNo.Text)
                cmd.Parameters.AddWithValue("@PurBillDate", CDate(TxtBillDate.Text))
                cmd.Parameters.AddWithValue("@VendorID", TxtVendorID.Text)
                cmd.Parameters.AddWithValue("@PurAmount", TxtNetTotal.Text)
                cmd.Parameters.AddWithValue("@Remarks", TxtRemarks.Text)

                cmd.ExecuteNonQuery()
            End Using

            For Each rw As DataGridViewRow In PurchaseGrid.Rows
                If rw.Cells(0).Value <> "" And rw.Cells(2).Value <> "" Then

                    Using cmd = New SqlCommand("INSERT INTO PurchaseDetail (PurBillNo,ProductID,PurDescription,PurQty,PurRate) values ('" & MyTxtBillNo.Text & "','" & rw.Cells(0).Value & "','" & rw.Cells(1).Value & "','" & rw.Cells(2).Value & "','" & rw.Cells(3).Value & "')", Conn)
                        cmd.ExecuteNonQuery()
                    End Using

                End If

            Next
            For Each lgrw As DataGridViewRow In PurchaseGrid.Rows

                Using cmd = New SqlCommand("INSERT INTO LogDetail (LogDate,Action,ProductID,LocationID,VendorID,VoucherNo,Trans,RATE) VALUES (@LogDate,@Action,@ProductID,@LocationID,@VendorID,@VoucherNo,@Trans,@RATE)", Conn)

                    cmd.Parameters.AddWithValue("@LogDate", TxtBillDate.Text)
                    cmd.Parameters.AddWithValue("@Action", "PS")
                    cmd.Parameters.AddWithValue("@ProductID", lgrw.Cells(0).Value)
                    cmd.Parameters.AddWithValue("@LocationID", "001")
                    cmd.Parameters.AddWithValue("@vendorID", TxtVendorID.Text)
                    cmd.Parameters.AddWithValue("@VoucherNO", MyTxtBillNo.Text)
                    cmd.Parameters.AddWithValue("@Trans", lgrw.Cells(2).Value)
                    cmd.Parameters.AddWithValue("@Rate", lgrw.Cells(3).Value)
                    cmd.ExecuteNonQuery()
                End Using
            Next

            MessageBox.Show("New Record has Added Successfuly", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("Error :" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

I want to insert the data in these tables by using SqlTransaction but have no idea, can some body let me know the vb.net code for the same.
Thanks in advance.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
myConnection.Open

Using myTransaction = myConnection.BeginTransaction()
    Try
        'Access the database here.

        myTransaction.Commit()
    Catch
        myTransaction.Rollback()
End Using
 

Mazhar

Member
Joined
Aug 27, 2016
Messages
15
Programming Experience
Beginner
myConnection.Open

Using myTransaction = myConnection.BeginTransaction()
    Try
        'Access the database here.

        myTransaction.Commit()
    Catch
        myTransaction.Rollback()
End Using


Dear Thank u very much for ur reply. I tried as per ur reply, this is my code how i tried.

VB.NET:
    Private Sub BtnSave_Click(sender As System.Object, e As System.EventArgs) Handles BtnSave.Click
        If PurchaseGrid.Rows.Item(0).Cells(0).Value = "" Or PurchaseGrid.Rows.Item(0).Cells(2).Value = "" Then
            MessageBox.Show("No Bill Details found", "Grid Data", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        PurchaseGrid.AllowUserToAddRows = False

        GetConnect()
        Conn.Open()
        Using Mytransaction = Conn.BeginTransaction()
            Try

                Using cmd As New SqlCommand("INSERT INTO PurchaseMaster (PurBillNo,PurBillDate,VendorID,PurAmount,Remarks) Values (@PurBillNo,@PurBillDate,@VendorID,@Puramount,@Remarks)", Conn)

                    cmd.Parameters.AddWithValue("@PurBillNo", MyTxtBillNo.Text)
                    cmd.Parameters.AddWithValue("@PurBillDate", CDate(TxtBillDate.Text))
                    cmd.Parameters.AddWithValue("@VendorID", TxtVendorID.Text)
                    cmd.Parameters.AddWithValue("@PurAmount", TxtNetTotal.Text)
                    cmd.Parameters.AddWithValue("@Remarks", TxtRemarks.Text)

                    cmd.ExecuteNonQuery()

                End Using

                For Each rw As DataGridViewRow In PurchaseGrid.Rows
                    If rw.Cells(0).Value <> "" And rw.Cells(2).Value <> "" Then

                        Using cmd = New SqlCommand("INSERT INTO PurchaseDetail (PurBillNo,ProductID,PurDescription,PurQty,PurRate) values ('" & MyTxtBillNo.Text & "','" & rw.Cells(0).Value & "','" & rw.Cells(1).Value & "','" & rw.Cells(2).Value & "','" & rw.Cells(3).Value & "')", Conn)
                            cmd.ExecuteNonQuery()
                        End Using

                    End If

                Next
                For Each lgrw As DataGridViewRow In PurchaseGrid.Rows

                    Using cmd = New SqlCommand("INSERT INTO LogDetail (LogDate,Action,ProductID,LocationID,VendorID,VoucherNo,Trans,RATE) VALUES (@LogDate,@Action,@ProductID,@LocationID,@VendorID,@VoucherNo,@Trans,@RATE)", Conn)

                        cmd.Parameters.AddWithValue("@LogDate", TxtBillDate.Text)
                        cmd.Parameters.AddWithValue("@Action", "PS")
                        cmd.Parameters.AddWithValue("@ProductID", lgrw.Cells(0).Value)
                        cmd.Parameters.AddWithValue("@LocationID", "001")
                        cmd.Parameters.AddWithValue("@vendorID", TxtVendorID.Text)
                        cmd.Parameters.AddWithValue("@VoucherNO", MyTxtBillNo.Text)
                        cmd.Parameters.AddWithValue("@Trans", lgrw.Cells(2).Value)
                        cmd.Parameters.AddWithValue("@Rate", lgrw.Cells(3).Value)

                        cmd.ExecuteNonQuery()
                    End Using
                Next

                Mytransaction.Commit()

                MessageBox.Show("New Record has Added Successfuly", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                Mytransaction.Rollback()
                MessageBox.Show("Error :" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using

    End Sub

(but I receive an error: i.e
"Error: ExecuteNonQuery requires the commd 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."

 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,603
Location
Norway
Programming Experience
10+
The Transaction property of the command has not been initialized."
You can either set it when you create the new SqlCommand (as third argument), or assign to Transaction property after you created it.
 
Top Bottom