How to create an almost duplicate record RRS feed

  • Question

  • I would like to use ADO.Net in Visual Basic 2008 to copy an existing record into the same table. To do this without generating a DUPLICATE record error, I need to not copy the Identity field and there are serveral other fields which I need to change before committing the Insert.

    I routinely use ADO.Net (System.Data.SQLClient.SQLXXXX) to Read, Insert, Delete and Upadate records. I would like to use a FOR Next loop that goes through all the fields except the last (the last field is the Identity record). This way I do not have to type out the 60 or so fields and i do not have to create the same number of variables.

    I used to do this in VB6 with an OLEDB connection. But I have no idea where to start with ADO.Net.



    Tuesday, February 22, 2011 4:22 PM


  • Hello Pat,


    Welcome to the MSDN Forum adn thanks for posting here.

    As I know, this issue has been solved in other forum, so I will quote one sample code for you:

    Here's ADO code for a copy button Darryl Kerkeslager used. This is a bound form with a
    listbox containing the list of addresses, with the bound column being the
    primary key. Start Date is the only change required.
    Private Sub btnCopy_Click()
    On Error GoTo handle_error
    Set cnxn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    If IsNull(Me.lstRecords.Value) Then
    MsgBox "No item selected", , "Selection Needed"
    With rs
    .Open "address", cnxn, adOpenKeyset, adLockOptimistic,
    .Index = "PrimaryKey"
    .Seek Me.txtId.Value, adSeekFirstEQ
    If Not .EOF Then
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me("add_off_id") = .Fields("add_off_id")
    Me("add_state_id") = .Fields("add_state_id")
    Me("add_residtype_id") = .Fields("add_residtype_id")
    Me("add_house_no") = .Fields("add_house_no")
    Me("add_apt") = .Fields("add_apt")
    Me("add_street_id") = .Fields("add_street_id")
    Me("add_zip_id") = .Fields("add_zip_id")
    Me("add_loc_id") = .Fields("add_loc_id")
    Me("add_map_id") = .Fields("add_map_id")
    End If
    End With
    Set rs = Nothing
    End If
    Exit Sub
    LogError Err.Number, "AddressForm.btnCopy", Err.Description '
    Error-logging sub
    End Sub
    You could add code that stored the old start date, and compared it to the
    new start date, and did not allow a save if they were the same.

    For more information, please see this:


    I hope this can help you.


    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jackie-Sun Wednesday, March 2, 2011 3:13 AM
    Wednesday, February 23, 2011 7:43 AM