kenadelglass
asked on
Updating A Record using ADO in VB6
I have this little VB executable that runs on the server that uses ADO to query a recordset and then loop through to see if there is any data in the Comments field. If there is data it needs to update the record. The database it is updating is in Access and the field is set in Access to accept zero length strings. I keep getting "The operation requested is not supported by the provider" every time I try to update the comment field of a record. Please let me know why I get this error everytime I try to update it?!?!?!
Here is my code...
Public Sub SendMail()
Dim cnLube As Connection
Dim rstEmail As Recordset
Dim strConnect As String
Dim objMail As CDONTS.NewMail
Dim strBody As String
On Error GoTo Proc_Err
If Day(Date) = 17 Then
Set cnLube = New ADODB.Connection
Set rstEmail = New ADODB.Recordset
cnLube.Provider = "Microsoft.Jet.OLEDB.3.51"
cnLube.ConnectionString = "Data Source=C:\inetpub\wwwroot\
cnLube.CursorLocation = adUseServer
cnLube.Open
rstEmail.Source = "Select * FROM tblEmailList"
Set rstEmail.ActiveConnection = cnLube
rstEmail.CursorType = adOpenKeyset
rstEmail.Open
End If
If Day(Date) = 17 Then
With rstEmail
Do Until .EOF
If Not !Comments = "" Then
!Comments = ""
.Update
End If
.MoveNext
Loop
End With
End If
Set rstEmail = Nothing
Set cnLube = Nothing
Proc_Err:
Debug.Print Err.Description
End Sub
Here is my code...
Public Sub SendMail()
Dim cnLube As Connection
Dim rstEmail As Recordset
Dim strConnect As String
Dim objMail As CDONTS.NewMail
Dim strBody As String
On Error GoTo Proc_Err
If Day(Date) = 17 Then
Set cnLube = New ADODB.Connection
Set rstEmail = New ADODB.Recordset
cnLube.Provider = "Microsoft.Jet.OLEDB.3.51"
cnLube.ConnectionString = "Data Source=C:\inetpub\wwwroot\
cnLube.CursorLocation = adUseServer
cnLube.Open
rstEmail.Source = "Select * FROM tblEmailList"
Set rstEmail.ActiveConnection = cnLube
rstEmail.CursorType = adOpenKeyset
rstEmail.Open
End If
If Day(Date) = 17 Then
With rstEmail
Do Until .EOF
If Not !Comments = "" Then
!Comments = ""
.Update
End If
.MoveNext
Loop
End With
End If
Set rstEmail = Nothing
Set cnLube = Nothing
Proc_Err:
Debug.Print Err.Description
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The standard open method for ADO recordsets, is read only mode.
try adding rstEmail.LockType=adLockOp
Also emoreau is right, it is much quicker to use the Update SQL statement and it looks cleaner.
Jetforce
try adding rstEmail.LockType=adLockOp
Also emoreau is right, it is much quicker to use the Update SQL statement and it looks cleaner.
Jetforce
ASKER
emoreau
Yes the table I am updating has a primary key. I was going to try the SQL statement you suggested but I wanted to try some ADO stuff - primarily for my own learning.
Jetforce - what could I use besides the standard rst.Open? What does the optimistic locking have to do with whether I can update a record - Nobody is in the database when I am testing so I don't think there is an editing conflict?
thx....
kenadelglass
Yes the table I am updating has a primary key. I was going to try the SQL statement you suggested but I wanted to try some ADO stuff - primarily for my own learning.
Jetforce - what could I use besides the standard rst.Open? What does the optimistic locking have to do with whether I can update a record - Nobody is in the database when I am testing so I don't think there is an editing conflict?
thx....
kenadelglass
If the recordset is open for "Read only" (the default value) as JetForce mentionned, you can't update.
Back to basics, in your access database is the Comments field Allow Zero Length Fields property set to Yes?
Emoreau & Jetforce: (From Above)
rstEmail.CursorType = adOpenKeyset
He's covered.
Emoreau & Jetforce: (From Above)
rstEmail.CursorType = adOpenKeyset
He's covered.
in ADO, we don't put the .Edit mtehod anymore!!!
kenadelglass,
Do this table have a primary index?
Why don't you simply use a SQL Update query? See this sample:
If Day(Date) = 17 Then
Set cnLube = New ADODB.Connection
cnLube.Provider = "Microsoft.Jet.OLEDB.3.51"
cnLube.ConnectionString = "Data Source=C:\inetpub\wwwroot\
cnLube.CursorLocation = adUseServer
cnLube.Open
cnLube.Execute "Update tblEmailList Set Comments = ''"
End If