3 Replies - 289 Views - Last Post: 29 January 2020 - 09:55 AM Rate Topic: -----

#1 nyt1972   User is online

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 102
  • Joined: 04-February 10

Use SQL Transaction in vb.net Code

Posted 29 January 2020 - 09:22 AM

Dear experts,

I wrote the below code to insert in multiple tables, but I want if any of them fails then all the others records should be removed from these tables, and this thing can be done with transaction, but I have no idea to use transaction properly.

My code is below.

 Try
            dbConnection()
            sqL = "select AdmissionNo from tblstudent where [email protected]"
            With cmd
                .Connection = conn
                .CommandText = sqL
                .Parameters.Clear()
                .Parameters.AddWithValue("@d1", RTrim(txtAdmissionNo.Text))
                result = .ExecuteNonQuery()
                If result <> 0 Then
                    dt = New DataTable
                    da = New SqlDataAdapter
                    da.SelectCommand = cmd
                    da.Fill(dt)
                    If dt.Rows.Count > 0 Then
                        MessageBox.Show("admission no. already exists", "error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                        txtAdmissionNo.Text = ""
                        txtAdmissionNo.Focus()
                        Return
                    End If
                End If
            End With
            Dim s1 As String = ""
            If rbMale.Checked = True Then
                s1 = "m"
            End If
            If rbFemale.Checked = True Then
                s1 = "f"
            End If
            sqL = "insert into tblstudent(admissionno, studentname,
fathername, mothername, fathercno,fathercnic, permanentaddress, temporaryaddress, contactno, emailid, dob, 
gender, admissiondate, religion, nationality,  
lastschoolattended, result, passpercentage, photo,house,accountno,
accountname,bank,branch,ibancode,awrno,boardregno) values (@d1,@d5,@d6,@d7,@d8,@d16,@d9,@d10,@d11,@d12,@d13,
@d14,@d15,@d18,@d20,@d22,@d23,@d24,@d26,@d27,@d29,@d30,@d31,@d32,@d33,@d34,@d35)"
            cmd = New SqlCommand
            With cmd
                .Connection = conn
                .CommandText = sqL
                .Parameters.Clear()
                .Parameters.AddWithValue("@d1", RTrim(txtAdmissionNo.Text))
                .Parameters.AddWithValue("@d5", txtStudentName.Text)
                .Parameters.AddWithValue("@d6", txtFathername.Text)
                .Parameters.AddWithValue("@d7", txtMotherName.Text)
                .Parameters.AddWithValue("@d8", txtFatherContactNo.Text)
                .Parameters.AddWithValue("@d9", txtPermanentAddress.Text)
                .Parameters.AddWithValue("@d10", txtTemrorayAddress.Text)
                .Parameters.AddWithValue("@d11", txtContactNo.Text)
                .Parameters.AddWithValue("@d12", txtEmailID.Text)
                .Parameters.AddWithValue("@d13", dtpDOB.Value.Date)
                .Parameters.AddWithValue("@d14", s1)
                .Parameters.AddWithValue("@d15", dtpAdmissionDate.Value.Date)
                .Parameters.AddWithValue("@d16", txtFatherCNIC.Text)
                .Parameters.AddWithValue("@d18", cmbReligion.Text)
                .Parameters.AddWithValue("@d20", txtNationality.Text)
                .Parameters.AddWithValue("@d22", txtLastSchoolAttended.Text)
                .Parameters.AddWithValue("@d23", cmbResult.Text)
                .Parameters.AddWithValue("@d24", txtPercentage.Text)
                .Parameters.Add(SavePhoto("@d26", Picture.Image))
                .Parameters.AddWithValue("@d27", cmbHouse.Text)
                .Parameters.AddWithValue("@d29", txtAccountNo.Text)
                .Parameters.AddWithValue("@d30", txtAccountName.Text)
                .Parameters.AddWithValue("@d31", txtBank.Text)
                .Parameters.AddWithValue("@d32", txtBranch.Text)
                .Parameters.AddWithValue("@d33", txtIBANCode.Text)
                .Parameters.AddWithValue("@d34", txtawrno.Text)
                .Parameters.AddWithValue("@d35", txtboardregno.Text)
                result = .ExecuteNonQuery
                If result <> 0 Then
                    'do nothing
                Else
                    MsgBox("cannot insert record")
                    Exit Sub
                End If
            End With
            sqL = "select studentid from tblstudent where [email protected]"
            cmd = New SqlCommand
            cmd.Connection = conn
            cmd.CommandText = sqL
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@d1", RTrim(txtAdmissionNo.Text))
            txtStudentID.Text = cmd.ExecuteScalar().ToString
            For i = 0 To ListView1.Items.Count - 1
                sqL = "insert into studentdocsubmitted(admissionno,docid,DocName) values (@d1,@d2,(Select DocName From [Document] Where [email protected]))
"
                cmd = New SqlCommand
                With cmd
                    .Connection = conn
                    .CommandText = sqL
                    .Parameters.Clear()
                    .Parameters.AddWithValue("@d1", RTrim(txtAdmissionNo.Text))
                    .Parameters.AddWithValue("@d2", ListView1.Items(i).SubItems(1).Text)
                    result = .ExecuteNonQuery
                    If result <> 0 Then
                        Continue For
                    Else
                        MsgBox("cannot insert document")
                    End If
                End With
            Next
            cmd = New SqlCommand
            sqL = "IF NOT EXISTS (SELECT * FROM tblenrollment WHERE [email protected] AND [email protected])
Begin
insert into tblenrollment
           ([StudentID],[SectionID],[InstitutionID],[SessionID],[ClassID],[CategoryID]
           ,[is_active])
     values (@d1,@d2,@inst,@SessionID,@ClassID,@d3,1)
End"
            With cmd
                .Connection = conn
                .CommandText = sqL
                .Parameters.Clear()
                .Parameters.AddWithValue("@inst", cmbInstitute.selectedvalue)
                .Parameters.AddWithValue("@SessionID", txtSessionID.Text)
                .Parameters.AddWithValue("@ClassID", txtClassID.Text)
                .Parameters.AddWithValue("@d1", txtStudentID.Text)
                .Parameters.AddWithValue("@d2", txtSectionID.Text)
                .Parameters.AddWithValue("@d3", txtCategoryID.Text)
                '.Parameters.AddWithValue("@d4", True)
                result = .ExecuteNonQuery()
                If result <> 0 Then
                    'do nothing
                Else
                    MsgBox("cannot insert record in enrollment")
                End If
            End With
            If CheckForInternetConnection() = True Then
                cmd = New SqlCommand
                cmd.Connection = conn
                cmd.CommandText = "select rtrim(apiurl) as apiurl from smssetting where isdefault='yes' and isenabled='yes'"
                cmd.ExecuteNonQuery()
                dt = New DataTable
                da = New SqlDataAdapter
                da.SelectCommand = cmd
                da.Fill(dt)
                If dt.Rows.Count > 0 Then
                    st2 = dbconn.dt.Rows(0).Item("apiurl")
                    Dim st3 As String = "hello dear, " & txtStudentName.Text & " your admission is successful having admission no '" & txtAdmissionNo.Text & "' in class '" & RTrim(txtClass.Text) & "'"
                    SMSFunc(txtContactNo.Text, st3, st2)
                    If dbconn.hasException(True) Then
                        Exit Sub
                    Else
                        MessageBox.Show("Student admitted successfully", "admitted", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        btnPrint.Visible = True
                    End If
                End If
            End If
        Catch ex As SqlException
            MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            conn.Close()
            cmd.Dispose()
        End Try


Please help.

Is This A Good Question/Topic? 0
  • +

Replies To: Use SQL Transaction in vb.net Code

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15477
  • View blog
  • Posts: 62,007
  • Joined: 12-June 08

Re: Use SQL Transaction in vb.net Code

Posted 29 January 2020 - 09:31 AM

You are in luck! MSDN docs show how to use transactions with the SQLCOMMAND object.

https://docs.microso...etframework-4.8
Was This Post Helpful? 0
  • +
  • -

#3 nyt1972   User is online

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 102
  • Joined: 04-February 10

Re: Use SQL Transaction in vb.net Code

Posted 29 January 2020 - 09:52 AM

Thanks for the reply,but I am still confused about using it in my code.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15477
  • View blog
  • Posts: 62,007
  • Joined: 12-June 08

Re: Use SQL Transaction in vb.net Code

Posted 29 January 2020 - 09:55 AM

Confused about what part? What have you attempted to implement of that doc?

The example on MSDN walks through creating a sqltransaction object, how to 'begin' the transaction, assigning that object to the command object, committing when done, and rolling back if need be.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1