Results 1 to 5 of 5

Thread: Ado Insert

  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Ado Insert

    I am using the following code to insert a single record into a table:

    Code:
    Dim cnn As New ADODB.Connection
    Dim rs3 As New ADODB.Recordset
    Dim sQRY As String
    
    cnn.Open CurrentProject.Connection
    Set rs3 = New ADODB.Recordset
    
    sQRY = "INSERT INTO tblWRIDLeedsCodes " & _
    "(WorkRequestID, LeedsCode) " & _
    "VALUES (" & rs1("WorkRequestID") & ", '" & rs2("CodeOut") & "');"
    If rs3.State = 1 Then rs3.Close
    rs3.Open sQRY, cnn, adOpenStatic, adLockOptimistic
    Set rs3 = cnn.Execute(sQRY)
    This works when executed, and a record is inserted into the appropriate table. However, I then get an error:

    The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

    WorkRequestID is a primary key in tblWRIDLeedsCodes. The table is empty prior to my inserting the new record. I have checked that rs1 and rs2 have returned values in the Locals Window. Anyone with any ideas?
    Last edited by bcass; 05-13-04 at 12:02.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    When you run the query it does not return a recordset for you to assign it. Do this instead

    Dim cnn As New ADODB.Connection
    Dim rs3 As New ADODB.Recordset
    Dim sQRY As String

    cnn.Open CurrentProject.Connection
    Set rs3 = New ADODB.Recordset

    sQRY = "INSERT INTO tblWRIDLeedsCodes " & _
    "(WorkRequestID, LeedsCode) " & _
    "VALUES (" & rs2("WorkRequestID") & ", '" & rs3("CodeOut") & "');"

    cnn.Execute sQRY, adExecuteNoRecords
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks a lot. That worked perfectly.

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Out of interest, lets say I did try to insert a duplicate into the primary key. How can I trap the error and proceed with the rest of the routine?

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    You could try somthing like this

    if DCount("WorkRequestID","tblWRIDLeedsCodes","WorkRe questID = " & _
    rs2("WorkRequestID")) = 0 then

    ..execute SQL..

    else

    ....Write error message...

    end if


    MTB

Posting Permissions

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