Using SQL transaction in VB.NET

See more:

VB

SQL-Server

Hello,

I a trying to use SQL transaction for the first time. Tried he below code but it always catch transaction exception.

Please someone correct this code for me.

I want to rollback transaction and show an error message if any of all records got failed.






Thanks in advance.

What I have tried:

I tried the below code:

       dbConnection()
        Using transaction As SqlTransaction = conn.BeginTransaction
            Try

                cmd = New SqlCommand
                With cmd
                    For Each row As DataGridViewRow In dgw.Rows
                        If CheckExistingStu(row.Cells(2).Value) <> True Then
                            txt = Environment.NewLine &
            "insert into tblStudent(AdmissionNo,awrno,boardregno,AdmissionDate,
        StudentName,FatherName,DOB,Gender,FatherCNIC, FatherCNO, TemporaryAddress,Religion) 
        VALUES (@d3,@d4,@d5,@d6,@d10,@d11,@d12,@d13,@d14,@d15,@d16,@d17)" & Environment.NewLine & "

    INSERT INTO tblEnrollment ([StudentID],[SectionID],[CategoryID]
           ,[Is_Active])
     VALUES ((Select StudentID From tblStudent WHERE AdmissionNo=@d3),
        (Select tblSection.SectionID From tblSection
Where tblSection.SectionName=@d9 AND 
        tblSection.ClassID = (Select ClassID From tblClass Where tblClass.ClassName = @d8 
AND tblClass.SessionID = (Select SessionID From tblSession 
        WHERE tblSession.SessionName=@d7))),(Select CategoryID From tblStuCategory WHERE Category=@d18),1)
" & Environment.NewLine

                            Dim Jointarray As List(Of String) = New List(Of String)()
                            Jointarray.Clear()
                            test = row.Cells(18).Value.ToString
                            If Not String.IsNullOrWhiteSpace(test) Then
                                Jointarray.AddRange(test.Split(New Char() {","c}))
                                For Each str As String In Jointarray
                                    txt = txt + "
insert Into StudentDocSubmitted(DocID,AdmissionNo,DocName) VALUES ((SELECT [DocID]
  FROM [Document] WHERE [DocName] = '" & Trim(str) & "'),@d3,'" & Trim(str) & "')" & Environment.NewLine
                                Next
                            End If

                            sqL = sqL + txt

                            .Connection = conn
                            .CommandText = sqL
                            .Parameters.Clear()
                            .Parameters.AddWithValue("@d2", row.Cells(1).Value.ToString)
                            .Parameters.AddWithValue("@d3", row.Cells(2).Value.ToString)
                            .Parameters.AddWithValue("@d4", row.Cells(3).Value.ToString)
                            .Parameters.AddWithValue("@d5", row.Cells(4).Value.ToString)
                            .Parameters.AddWithValue("@d6", Convert.ToDateTime(row.Cells(5).Value).ToString("yyyy-MM-dd"))
                            .Parameters.AddWithValue("@d7", row.Cells(6).Value.ToString)
                            .Parameters.AddWithValue("@d8", row.Cells(7).Value.ToString)
                            .Parameters.AddWithValue("@d9", row.Cells(8).Value.ToString)
                            .Parameters.AddWithValue("@d10", row.Cells(9).Value.ToString)
                            .Parameters.AddWithValue("@d11", row.Cells(10).Value.ToString)
                            .Parameters.AddWithValue("@d12", Convert.ToDateTime(row.Cells(11).Value).ToString("yyyy-MM-dd"))
                            .Parameters.AddWithValue("@d13", row.Cells(12).Value.ToString)
                            .Parameters.AddWithValue("@d14", row.Cells(13).Value.ToString)
                            .Parameters.AddWithValue("@d15", row.Cells(14).Value.ToString)
                            .Parameters.AddWithValue("@d16", row.Cells(15).Value.ToString)
                            .Parameters.AddWithValue("@d17", Trim(row.Cells(16).Value.ToString))
                            .Parameters.AddWithValue("@d18", row.Cells(17).Value.ToString)

                        End If
                    Next
                    result = .ExecuteNonQuery
                    transaction.Commit()
                End With
            Catch ex As Exception
                Try
                    transaction.Rollback()
                Catch exRollback As Exception
                    ' Do logging or something when rollback failed.
                End Try
                MsgBox("Transaction failed check data / database settings")
            End Try
        End Using