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,Compan
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.
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
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,Compan
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.
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
Visual Basic.NET
Last Comment
Mani Pazhana
Between this two SQLstatements you have to use executeNon Query for each SQL Statement.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
could you please explain further. sorry.......
ASKER
gotcha, I will try the code
Try using transaction,
Basically you can commit the changes when insert and update succeeds.
Or rollback if any one (insert or update) failed.
Thanks
Basically you can commit the changes when insert and update succeeds.
Or rollback if any one (insert or update) failed.
Thanks
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,Compan
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.
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.BeginTransact
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
--------------------------
'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,Compan
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.
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.BeginTransact
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