recordset update, adnew...펌

 

ADODB: difference between ADDNEW and UPDATE methods?

No problem. We won't show you that ad again. Why didn't you like it?

• Uninteresting
• Misleading
• Offensive
• Repetitive

Oops! I didn't mean to do this.

up vote 0 down vote favorite 1	i am updating a table in mysql using ADODB i am adding new entries into a table should i be using addnew or update? sql mysql excel vba share|improve this question asked May 13 '10 at 3:03 Артём Царионов 3,06552233456

2 Answers

active oldest votes

up vote 3 down vote accepted

There's no difference, you will always use .Update to commit changes from where the current cursor is pointing at. AddNew allocates new row at the end of ADODB recordset ADODB recordset is a cursor-based data set, when you load rows into recordset, the cursor is automatically on first row, so anything you do on recordset's columns, it will modify wherever the recordset cursor is currently pointing at. For example: dim rs as new adodb.recordset rs.Open _ " select emp_id, lastname, firstname, middlename, age from emp " & _ " where location = 'LIVERPOOL'" & _ " ORDER BY emp_id", connectionVariableHere This will update the first row: rs!middlename = ono" rs.Update This will update the next row: rs.MoveNext rs!middlename = "Eastman" rs.Update To add a record (the cursor will move to last record) rs.AddNew rs!lastname = ono" rs!firstname = "Yoko" rs!middlename = "Yasuda" rs.Update This will update the last added record, after performing the step above: rs!lastname = "Lennon" rs.Update If i remember correctly, MoveNext, MoveFirst, etc, implicitly call .Update before moving to new cursor location, so if you are in first row... rs.MoveFirst ...Then you do: rs!age = 70 ' lennon's age of 2010 rs.MoveNext ...That will call .Update before moving to Paul McCartney. Anyway, don't rely on it, just call .Update when you want to commit the changes on row share|improve this answer edited May 13 '10 at 3:42 answered May 13 '10 at 3:35 Michael Buen 18.1k13158

1   Amusing pseudo-data is worth a vote just by itself! – Smandoli May 13 '10 at 4:05

No problem. We won't show you that ad again. Why didn't you like it?

• Uninteresting
• Misleading
• Offensive
• Repetitive

Oops! I didn't mean to do this.

up vote 1 down vote	To edit an existing record: .Edit to start, .Update to finish. To create a new record: .AddNew to start, .Update to finish. share|improve this answer answered May 13 '10 at 3:22 Smandoli 2,7831235
	Use Michael B's answer. I forgot this, but it sounds right that .Edit is just for DAO. You leave it off for ADO. – Smandoli May 13 '10 at 4:03

CursorType, LockType, and EditMode Properties Example (VB)

4 out of 5 rated this helpful - Rate this topic

This example demonstrates setting the CursorType and LockType properties before opening a Recordset. It also shows the value of the EditMode property under various conditions. The EditModeOutput function is required for this procedure to run.

Copy

'BeginEditModeVB
    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
Public Sub Main()
    on Error GoTo ErrorHandler
    ' recordset variables
    Dim rstEmployees As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim SQLEmployees As String
     ' open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
      ' set recordset properties through object refs
      ' instead of through arguments to Open method
    Set rstEmployees = New ADODB.Recordset
    Set rstEmployees.ActiveConnection = Cnxn
    rstEmployees.CursorLocation = adUseClient
    rstEmployees.CursorType = adOpenStatic
    rstEmployees.LockType = adLockBatchOptimistic
     ' open recordset with data from Employee table
    SQLEmployees = "employee"
    rstEmployees.Open SQLEmployees, , , , adCmdTable
    ' Show the EditMode property under different editing states
    rstEmployees.AddNew
        rstEmployees!emp_id = "T-T55555M"
        rstEmployees!fname = "temp_fname"
        rstEmployees!lname = "temp_lname"
            'call function below
            'to output results to debug window
        EditModeOutput "After AddNew:", rstEmployees.EditMode
        rstEmployees.UpdateBatch
        EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
        rstEmployees!fname = "test"
        EditModeOutput "After Edit:", rstEmployees.EditMode
    rstEmployees.Close
    ' Delete new record because this is a demonstration
    Cnxn.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"
    ' clean up
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub
ErrorHandler:
    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
Public Function EditModeOutput(strTemp As String, _
   intEditMode As Integer)
   ' Print report based on the value of the EditMode
   ' property
   Debug.Print strTemp
   Debug.Print "  EditMode = ";
   Select Case intEditMode
      Case adEditNone
         Debug.Print "adEditNone"
      Case adEditInProgress
         Debug.Print "adEditInProgress"
      Case adEditAdd
         Debug.Print "adEditAdd"
   End Select
End Function
'EndEditModeVB