AddNew Method Example (VB)

AddNew Method Example (VB)

This example uses the AddNew method to create a new record with the specified name.

Public Sub AddNewX()

    Dim cnn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim strID As String
    Dim strFirstName As String
    Dim strLastName As String
    Dim booRecordAdded As Boolean

    ' Open a connection.
    Set cnn1 = New ADODB.Connection
    strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;"
    cnn1.Open strCnn
        
    ' Open Employee table.
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.CursorType = adOpenKeyset
    rstEmployees.LockType = adLockOptimistic
    rstEmployees.Open "employee", cnn1, , , adCmdTable

    ' Get data from the user. The employee ID must be formatted as 
    ' first, middle, and last initial, five numbers, then M or F to 
    ' signify the gender. For example, the employee id for Bill Sornsin
    ' would be "B-S55555M".
    strID = Trim(InputBox("Enter employee ID:"))
    strFirstName = Trim(InputBox("Enter first name:"))
    strLastName = Trim(InputBox("Enter last name:"))

    ' Proceed only if the user actually entered something
    ' for both the first and last names.
    If (strID <> "") And (strFirstName <> "") _
        And (strLastName <> "") Then

        rstEmployees.AddNew
        rstEmployees!emp_id = strID
        rstEmployees!fname = strFirstName
        rstEmployees!lname = strLastName
        rstEmployees.Update
        booRecordAdded = True

        ' Show the newly added data.
        MsgBox "New record: " & rstEmployees!emp_id & " " & _
            rstEmployees!fname & " " & rstEmployees!lname

    Else
        MsgBox "Please enter an employee ID, " & _
            "first name, and last name."
    End If
        
    ' Delete the new record because this is a demonstration.
    cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
        
    rstEmployees.Close
    cnn1.Close

End Sub