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.

    Wednesday, February 23, 2011 7:43 AM