Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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\lubeoil\MyDB_be.mdb;Jet OLEDB:database password=xxxxxxx"
                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


0
kenadelglass
Asked:
kenadelglass
1 Solution
 
mpcappsCommented:
put your .edit in before you modify and update :>)
0
 
Éric MoreauSenior .Net ConsultantCommented:
mpcapps,

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\lubeoil\MyDB_be.mdb;Jet OLEDB:database password=xxxxxxx"
   cnLube.CursorLocation = adUseServer
   cnLube.Open

   cnLube.Execute "Update tblEmailList Set Comments = ''"                
End If
0
 
jetforceCommented:
The standard open method for ADO recordsets, is read only mode.
try adding rstEmail.LockType=adLockOptimistic.
Also emoreau is right, it is much quicker to use the Update SQL statement and it looks cleaner.


Jetforce
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kenadelglassAuthor Commented:
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
0
 
Éric MoreauSenior .Net ConsultantCommented:
If the recordset is open for "Read only" (the default value) as JetForce mentionned, you can't update.
0
 
wsh2Commented:
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.
               

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now