Link to home
Create AccountLog in
Avatar of chrisryhal
chrisryhal

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
Avatar of Mani Pazhana
Mani Pazhana
Flag of United States of America image

Between this two  SQLstatements you have  to use executeNon Query for each SQL Statement.
ASKER CERTIFIED SOLUTION
Avatar of Mani Pazhana
Mani Pazhana
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of chrisryhal
chrisryhal

ASKER

could you please explain further.  sorry.......
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
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