Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Unanswered: How to perform multiple insert commands?

    I have on problem with a access DB. I'm working on some project where i have two tables in relation. For insertion in first table i have al least 3 insertion in second table which is in a relation with first. The tables are in 1-to-many relation. The problem is that when i try to insert values in second table it only saves first of them.

    I will show my code example. Please tell me where i'm getting wrong?

    Code:
    Dim cnn As New OleDbConnection(CONNECT_STRING)
      Dim cmd As New OleDbCommand()
    
     Try
          With cmd
    
          .Connection = cnn
          .CommandText = "INSERT INTO employe(name,age, tel, home) VALUES('" & TextBox_name.Text & "','" & Val(TextBox_age.Text) & "','" & Val(TextBox_tel.Text) & "','" & TextBox_home.Text& "')"
          .ExecuteNonQuery()
    
           .CommandText = "INSERT INTO travel(ID_employe,location,year,country) VALUES(@@IDENTITY,'" & location1.Text & "','" & Val(year1.Text) & "','" & country1.Text & "')"
           .ExecuteNonQuery()
    
      .CommandText = "INSERT INTO travel(ID_employe,location,year,country) VALUES(@@IDENTITY,'" & location2.Text & "','" & Val(year2.Text) & "','" & country2.Text & "')"
           .ExecuteNonQuery()
    
      .CommandText = "INSERT INTO travel(ID_employe,location,year,country) VALUES(@@IDENTITY,'" & location3.Text & "','" & Val(year3.Text) & "','" & country3.Text & "')"
           .ExecuteNonQuery()
    
        End With
      Catch ex As Exception
     End Try
    Please help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are you connecting to? IF it's access, just use DoCmd.RunSQL or db.execute.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    Quote Originally Posted by Teddy
    What are you connecting to? IF it's access, just use DoCmd.RunSQL or db.execute.
    Yes i'm using access database. But i'm allredy executing it with ExecuteNonQuery() command. Or not?

  4. #4
    Join Date
    Jan 2004
    Location
    Ft Collins, CO
    Posts
    22
    @@IDENTITY is MSSQL, isn't it?? Anyways, here's what's happening (I think):

    1.) You insert a record in the employee table. At that point, @@IDENTITY = ID from that insert, say 5 for example.

    2.) Then, you use that as a parameter in your first travel insert, but when you insert that first record in the travel table, @@IDENTITY then gets reset to that ID, say 10 for example.

    3.) You execute your second travel insert, but now @@IDENTITY has been reset again, to 11. This happens again on all subsequent executions. All of a sudden, the travel records (except for the first one) are no longer linked to the employee record, maybe even nothing at all.

    What you need to do is retrieve the @@IDENTITY value before you do your travel inserts, somewhere between steps 1 and 2. Does that make sense?

  5. #5
    Join Date
    Mar 2006
    Posts
    6
    billdo75 thanks for sugestion. I managed to solve it now. I saved last inserted ID to a variable and saved it to a related table with a simple insert statement.

    Thanks again friends!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •