CODEWITHSUNDEEP
sql-servervb.nettransactions
Codemunkeee
Codemunkeee

Reputation: 1613

sql Transaction and dual table insertion in vb.net

What I wanted to do is insert data on the first table, then get the last inserted ID on the first table and insert it on the second. I already got this without transaction, but I can't do it because I need to have transactions later on because I will add a lot of inserts in one go.

This is my code so far..
Take note that this is inside a transaction.. 

                command.CommandText = "INSERT INTO tblCarMaintenance " & _
                      "(ID_Car, fDate, fMechanic, fOverseer, fDescription, fDateNext, fAmount) " & _
                      "VALUES (@myCarID, @myDate, @myMechanic, @myOverseer, @myDescription, @myDateNext, @myAmount)"
                command.Parameters.Add("@myCarID", SqlDbType.Int).Value = pCarID
                command.Parameters.Add("@myDate", SqlDbType.DateTime).Value = myDate
                command.Parameters.Add("@myMechanic", SqlDbType.VarChar).Value = pMechanic
                command.Parameters.Add("@myOverseer", SqlDbType.VarChar).Value = pOverseer
                command.Parameters.Add("@myDescription", SqlDbType.VarChar).Value = pDescription
                command.Parameters.Add("@myDateNext", SqlDbType.DateTime).Value = pNext
                command.Parameters.Add("@myAmount", SqlDbType.Float).Value = pAmount
                command.ExecuteNonQuery()

               'insert records on the second table (the problem is here)

                command.CommandText = "INSERT INTO tblCarMaintenance2 " & _
                      "(ID_Main, ID_Supplier, fParts, fAmount) " & _
                      "VALUES (@myMainID, @mySupplierID, @myParts, @myAmount) " & _
                      "FROM tblCarMaintenance"
                command.Parameters.Add("@myMainID", SqlDbType.Int).Value = myID
                command.Parameters.Add("@mySupplierID", SqlDbType.DateTime).Value = myDate
                command.Parameters.Add("@myParts", SqlDbType.VarChar).Value = pMechanic
                command.Parameters.Add("@myAmount", SqlDbType.VarChar).Value = pOverseer
                command.ExecuteNonQuery()

 transaction.Commit()

EDIT: @myMainID is the parameter equal to the last inserted ID on tblCarMaintenance

EDIT:
The solution for the problem is this: 

command.CommandText = "INSERT INTO tblCarMaintenance " & _
                  "(ID_Car, fDate, fMechanic, fOverseer, fDescription, fDateNext, fAmount) " & _
                  "VALUES (@myCarID, @myDate, @myMechanic, @myOverseer, @myDescription, @myDateNext, @myAmount)" & _
                  "Select SCOPE_IDENTITY()"

                command.Parameters.Add("@myCarID", SqlDbType.Int).Value = pCarID
                command.Parameters.Add("@myDate", SqlDbType.DateTime).Value = myDate
                command.Parameters.Add("@myMechanic", SqlDbType.VarChar).Value = pMechanic
                command.Parameters.Add("@myOverseer", SqlDbType.VarChar).Value = pOverseer
                command.Parameters.Add("@myDescription", SqlDbType.VarChar).Value = pDescription
                command.Parameters.Add("@myDateNext", SqlDbType.DateTime).Value = pNext
                command.Parameters.Add("@myAmount", SqlDbType.Float).Value = pAmount
                Dim InsertedItemID = command.ExecuteScalar()

                command.CommandText = "INSERT INTO tblCarMaintenance2 " & _
                      "(ID_Main, ID_Supplier, fParts, fAmount) " & _
                      "VALUES (" & InsertedItemID & ", @mySupplierID, @myParts, @myAmount2) "

                command.Parameters.Add("@mySupplierID", SqlDbType.Int).Value = pSupplier
                command.Parameters.Add("@myParts", SqlDbType.VarChar).Value = pParts
                command.Parameters.Add("@myAmount2", SqlDbType.Float).Value = 12
                command.ExecuteNonQuery()

Upvotes: 0

Views: 503

Answers (1)

Ryan Nigro
Ryan Nigro

Reputation: 4629

In your first query, edit to:

command.CommandText = "INSERT INTO tblCarMaintenance " & _
                      "(ID_Car, fDate, fMechanic, fOverseer, fDescription, fDateNext, fAmount) " & _
                      "VALUES (@myCarID, @myDate, @myMechanic, @myOverseer, @myDescription, @myDateNext, @myAmount)" & _
                      "Select SCOPE_IDENTITY()";

and change the first command.ExecuteNonQuery() to:

Dim InsertedItemID = command.ExecuteScalar()

Upvotes: 1

Related Questions