CODEWITHSUNDEEP
sqlvb.net
Pallab Pal
Pallab Pal

Reputation: 1

insert record in multiple table using sqldataadapter and sqltransaction

I'm trying to insert record in multiple table at a time using sqldataadapter and sqltransaction. i use the following code. but it is not working. help me plz....... tell me the problem in this code

Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=AMHSDB;Integrated Security=True")
Dim da As New SqlDataAdapter
Dim ds As New DataSet
Dim trans As SqlTransaction
Dim tot As Integer
Public Sub RecordSave()
    tot = CInt(TB3.Text.Trim) + CInt(TB4.Text.Trim) + CInt(TB5.Text.Trim) + CInt(TB6.Text.Trim)
    Dim yr As String
    yr = Now.Year()
    Try
        cn.Open()
        trans = cn.BeginTransaction()
        da.InsertCommand.Transaction = trans
        da.InsertCommand = New SqlCommand("INSERT INTO[FTUT_tbl] (roll_no,s_name,class,session,eng_i,ben,math,stu_wor,gra_tot) VALUES ('" & TB1.Text.Trim & "','" & TB2.Text.Trim & "','Nursery','" & yr & "','" & TB3.Text.Trim & "','" & TB4.Text.Trim & "','" & TB5.Text.Trim & "','" & TB6.Text.Trim & "','" & tot & "')", cn)
        da.InsertCommand.ExecuteNonQuery()
        da.Dispose()
        da.InsertCommand = New SqlCommand("INSERT INTO[FTE_tbl] (roll_no,s_name,class,session) VALUES ('" & TB1.Text.Trim & "','" & TB2.Text.Trim & "','Nursery','" & yr & "')", cn)
        da.InsertCommand.ExecuteNonQuery()
        da.Dispose()
trans.Commit()
        MsgBox("Rocord Successfully Inserted!")
    Catch ex As Exception
        trans.Rollback()
        MsgBox(ex.Message)
    Finally
        cn.Close()
    End Try
End Sub

Upvotes: 0

Views: 857

Answers (3)

jepoy
jepoy

Reputation: 183

your problem is you disposed the sql command in the first insert, but then you didnt open it again in another insert.

try this one.....

cn.Open()
    trans = cn.BeginTransaction()
    da.InsertCommand.Transaction = trans
    da.InsertCommand = New SqlCommand("INSERT INTO[FTUT_tbl] (roll_no,s_name,class,session,eng_i,ben,math,stu_wor,gra_tot) VALUES ('" & TB1.Text.Trim & "','" & TB2.Text.Trim & "','Nursery','" & yr & "','" & TB3.Text.Trim & "','" & TB4.Text.Trim & "','" & TB5.Text.Trim & "','" & TB6.Text.Trim & "','" & tot & "')", cn)
    da.InsertCommand.ExecuteNonQuery()
    da.Dispose()
    cn.Open()
    da.InsertCommand = New SqlCommand("INSERT INTO[FTE_tbl] (roll_no,s_name,class,session) VALUES ('" & TB1.Text.Trim & "','" & TB2.Text.Trim & "','Nursery','" & yr & "')", cn)
    da.InsertCommand.ExecuteNonQuery()
    da.Dispose()

Upvotes: 0

user1102001
user1102001

Reputation: 707

try this, may be it will help for you

 Dim dsBillMst As DataTable
 Dim TrDtsSvr As SqlTransaction
 Dim cmdReadLocal As New SqlCommand
 Dim ConSvr As New SqlConnection(gblstrDBConnectionStr)
 ConSvr.Open()
 TrDtsSvr = ConSvr.BeginTransaction() 
 cmdReadLocal.Transaction = TrDtsSvr 
 cmdReadLocal.commandtext="//your insert statement"
 cmdReadLocal.executenonquery()
 adapter = New SqlDataAdapter(cmdReadLocal)
            dsBillMst = New DataTable
            adapter.Fill(dsBillMst)

Upvotes: 0

SysDragon
SysDragon

Reputation: 9888

Create the InsertCommand first, and then assign the transaction:

da.InsertCommand = New SqlCommand("...")
da.InsertCommand.Transaction = trans

And you need to commit your transactions:

da.InsertCommand.Transaction.Commit()

Upvotes: 1

Related Questions