asked on

Update 2 tables in SQL with one event

Ok, well I had this same question open just the other day, and I was able with help from people on here to get my update to work, but my insert isn't working now. Below is the code. No errors, just doesn't insert anything into my TL_Events but it does update TL_Tooling.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'\\\\\\\\\\\\\\\\\SQL CONNECTION TO INSERT TO TL_EVENTS\\\\\\\\\\

'build sql connection string at run time

Dim connect As SqlConnection = New SqlConnection()
connect.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=Triumph;" & _
"Integrated Security=SSPI"

'Opens the SQL Connection
connect.Open()

Dim strSQLInsert As String

strSQLInsert = "Insert into TL_EVENTS(EventType,Company,EntryDate,EntryPerson,ToolID,Comments) values ("
strSQLInsert = strSQLInsert & "'" & Trim(txtRedTag.Text) & "',"
strSQLInsert = strSQLInsert & "'" & Trim(txtCompany.Text) & "',"
strSQLInsert = strSQLInsert & "'" & Trim(txtEntryDate.Text) & "',"
strSQLInsert = strSQLInsert & "'" & Trim(txtSupervisor.Text) & "',"
strSQLInsert = strSQLInsert & "'" & Trim(txtToolID.Text) & "',"
strSQLInsert = strSQLInsert & "'" & Trim(txtDamageDescription.Text) & "')"

strSQLInsert = "Update TL_Tooling "
strSQLInsert = strSQLInsert & " SET StatusCode = '" & Trim(txtRedTag.Text) & "',"
strSQLInsert = strSQLInsert & " Company = '" & Trim(txtCompany.Text) & "'"
strSQLInsert = strSQLInsert & " WHERE ToolID = '" & Trim(txtToolID.Text) & "'"

'open command object(takes two parameters sqlstring, connection)
Dim command As New SqlCommand(strSQLInsert, connect)

Try

'execute command object
command.ExecuteNonQuery()

Catch ae As SqlException

MessageBox.Show(ae.Message)

Finally

'clean up code
command.Dispose()
connect.Close()
connect = Nothing

End Try

Mani Pazhana

Between this two SQLstatements you have to use executeNon Query for each SQL Statement.

ASKER CERTIFIED SOLUTION

Mani Pazhana

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrisryhal

ASKER

could you please explain further. sorry.......

chrisryhal

ASKER

gotcha, I will try the code

Mani Pazhana

Try using transaction,

Basically you can commit the changes when insert and update succeeds.

Or rollback if any one (insert or update) failed.

Thanks

Mani Pazhana

Sample code example:
----------------------------------------

'build sql connection string at run time

Dim connect As SqlConnection = New SqlConnection()
connect.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=Triumph;" & _
"Integrated Security=SSPI"

'Opens the SQL Connection
connect.Open()

Dim strSQLInsert1, strSQLInsert2 As String

strSQLInsert1 = "Insert into TL_EVENTS(EventType,Company,EntryDate,EntryPerson,ToolID,Comments) values ("
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtRedTag.Text) & "',"
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtCompany.Text) & "',"
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtEntryDate.Text) & "',"
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtSupervisor.Text) & "',"
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtToolID.Text) & "',"
strSQLInsert1 = strSQLInsert1 & "'" & Trim(txtDamageDescription.Text) & "')"

strSQLInsert2 = "Update TL_Tooling "
strSQLInsert2 = strSQLInsert2 & " SET StatusCode = '" & Trim(txtRedTag.Text) & "',"
strSQLInsert2 = strSQLInsert2 & " Company = '" & Trim(txtCompany.Text) & "'"
strSQLInsert2 = strSQLInsert2 & " WHERE ToolID = '" & Trim(txtToolID.Text) & "'"

Dim myCommand As New SqlCommand()
Dim myTrans As SqlTransaction

myTrans = myConnection.BeginTransaction()
myCommand.Connection = connect

'Here is the key line of code where we assign the transaction to a SQLCommand object:

myCommand.Transaction = myTrans

Try

myCommand.CommandText = strSQLInsert1
myCommand.ExecuteNonQuery()
myCommand.CommandText = strSQLInsert2
myCommand.ExecuteNonQuery()

'The Commit method send the transaction through:

myTrans.Commit()

Catch

'and the Rollback method prevents any of it from happening:

myTrans.Rollback()

Finally

connect.Close()

End Try