VFP - ADO and update record

[mrwb]

I have done some searching here on ADODB and FoxPro but have come up empty. I can connect to our MS SQL databases with ADO and read and update records but cannot do the same with a Visual FoxPro file. The file is not part of a DBC - it is a free file containing program options. I need to toggle a couple of values. Here is what I have so far:

$sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Mode = 16  ; sharedenynone
    $sqlCon.CursorLocation = 3 ; client side cursor
    $sqlCon.Open("Provider=vfpoledb.1;Data Source=C:\Myfiles\;Collating Sequence=general;")
    if @error Then
        MsgBox(0, "ERROR", "Failed to connect to the database")
        Exit
    EndIf

    $sqlRs = ObjCreate ("ADODB.Recordset")
    if not @error Then
        $sqlRS.open("select name, value from  Opts.dbf where name = 'Option1'", $sqlCon)
        if not @error Then
            ;Loop until the end of file
                While Not $sqlRS.EOF
                    ;Retrieve data from the following fields
                    $OptionName = $sqlRS.Fields('name').Value
                    $OptionVal = $sqlRS.Fields('value').Value
                    MsgBox(0, "Record Found", "Name:  " & $OptionName & @CRLF & "Value:  " & $OptionVal)
                    $sqlRS.MoveNext
                    WEnd
            $sqlRS.close
            $sqlCMD = ObjCreate("ADODB.Command")
;~          $sqlCMD.CommandType = adCmdText    ; need to find integer values for these options
            $sqlCMD.ActiveConnection = $sqlCON
            $sqlCMD.CommandText = "update Opts.dbf set value = '.F.' where name = 'Option1'"
            $sqlCMD.Execute
;~          if $OptionVal = '.T.' Then
;~              $sqlCon.execute("update C:\Myfiles\Opts.dbf set value = '.F.' where name = 'Option1'")
;~          Else
;~              $sqlCon.execute("update C:\Myfiles\Opts.dbf set value = '.T.' where name = 'Option1'")
;~          EndIf
        EndIf
    EndIf

The message box does popup with the expected values from the database. The read is succeeding.

The error I get is at the $sqlCMD.Execute (or $sqlCon.execute if I try the other method) : ==> The requested action with this object has failed.:

[ResNullius]

Wow, so somebody else actually uses the Foxpro OLE with AutoIt, whaddya know!

I usually use the Field Update command. So with your code I would do something like

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Mode = 16  ; sharedenynone
$sqlCon.CursorLocation = 3 ; client side cursor
$sqlCon.Open ("Provider=vfpoledb.1;Data Source=C:\Myfiles\;Collating Sequence=general;")
If @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
EndIf

$sqlRs = ObjCreate("ADODB.Recordset")
If Not @error Then
    $sqlRs.open ("select name, value from  Opts.dbf where name = 'Option1'", $sqlCon)
    If Not @error Then
        ;Loop until the end of file
        While Not $sqlRs.EOF
            ;Retrieve data from the following fields
            $OptionName = $sqlRs.Fields ('name' ).Value
            $OptionVal = $sqlRs.Fields ('value' ).Value
            MsgBox(0, "Record Found", "Name:  " & $OptionName & @CRLF & "Value:  " & $OptionVal)
            $sqlRs.FIELDS('"' & $OptionName & '"') = ".F." ; ADDED THIS LINE
            $sqlRs.Update  ; ADDED THIS LINE
            $sqlRs.MoveNext  
        WEnd
        $sqlRs.close
    EndIf
EndIf

I also suggest you look at implementing a Com Error Handler to get more descriptive error results with error numbers that correspond to the OLE implementation so you can pinpoint your problems better.

Edited September 13, 2007 by ResNullius

[ptrex]

@all

Count me in for VFP.

Does anyone know how to run a DB schema query for VFP ?

Regards

prex

[ResNullius]

@all

Count me in for VFP.

Does anyone know how to run a DB schema query for VFP ?

Regards

prex

The VFP OLE doesn't support all the methods of VFP, so sometimes workarounds have to be figured out. What info are you after?

Everything- tables, field names/sizes/types, indexes, etc?

Or would you be happy with just some of that?

I'm no VFP expert, but I've worked extensively with VFP databases, mostly free tables, and I have written some small Foxpro programs to get certain tasks done.

[ptrex]

@ResNullius

Thanks for the feedback.

You can ignore my question, I already found the solution to query the DB schema (Catalog).

I did have to use DBC (database Container) approach, rather than the "Free Tables" approach.

Regards,

ptrex

[mrwb]

Wow, so somebody else actually uses the Foxpro OLE with AutoIt, whaddya know!

I usually use the Field Update command. So with your code I would do something like

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Mode = 16  ; sharedenynone
$sqlCon.CursorLocation = 3 ; client side cursor
$sqlCon.Open ("Provider=vfpoledb.1;Data Source=C:\Myfiles\;Collating Sequence=general;")
If @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
EndIf

$sqlRs = ObjCreate("ADODB.Recordset")
If Not @error Then
;~      $sqlRS.LockType = 3 ; adLockOptimistic
;~      $sqlRS.CursorType = 3 ; adOpenStatic
    $sqlRs.open ("select name, value from  Opts.dbf where name = 'Option1'", $sqlCon)
    If Not @error Then
        ;Loop until the end of file
        While Not $sqlRs.EOF
            ;Retrieve data from the following fields
            $OptionName = $sqlRs.Fields ('name' ).Value
            $OptionVal = $sqlRs.Fields ('value' ).Value
            MsgBox(0, "Record Found", "Name:  " & $OptionName & @CRLF & "Value:  " & $OptionVal)
;~          $sqlRs.FIELDS('"' & $OptionName & '"') = ".F." ; ADDED THIS LINE
                        $sqlRs.FIELDS('value') = ".F." ; CHANGED TO THIS LINE since FIELDS needs the FIELD NAME
            $sqlRs.Update  ; ADDED THIS LINE
            $sqlRs.MoveNext  
        WEnd
        $sqlRs.close
    EndIf
EndIf

I also suggest you look at implementing a Com Error Handler to get more descriptive error results with error numbers that correspond to the OLE implementation so you can pinpoint your problems better.

Thanks for the tips. I finally got around to retesting this evening. This is just some test code that I am working with to isolate the issue and learn more about ADO. The application that uses FoxPro is from an outside vendor. We have this running on about 200 laptops and I am trying to give my users a shortcut to managing a couple of options without letting them loose on the whole configuration process.

I am still using AutoIt 3.2.4.9. I will try updating but looking at the release history does not seem to show anything that would be causing me a problem. I think it is my understanding of ADO and FoxPro that is lacking.

I changed the one line as indicated for the syntax of the Fields command. I added the COM Error Handler.

Initially I got the error that the "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected lock type." The error occurs on the line that sets the field name 'value' to ".F."

So then I added the 2 lines setting the lock type and the cursor type. Now the field gets set but I get an error on the Update command: "Multiple-step operation generated errors. Check each status value." The same error then triggers for the MoveNext command and the loop never exits.

So where do I go from here?

[ResNullius]

Initially I got the error that the "Current Recordset does not support updating. This may be a limitation of the provider, or of the selected lock type." The error occurs on the line that sets the field name 'value' to ".F."

So then I added the 2 lines setting the lock type and the cursor type. Now the field gets set but I get an error on the Update command: "Multiple-step operation generated errors. Check each status value." The same error then triggers for the MoveNext command and the loop never exits.

So where do I go from here?

Oh boy, I should really look more closely at the code in original posts before I go off half-cocked.

As for my original suggestions, of course the recordset doesn't support updating, because I only created it by reading the records from the original database: we would need to open the whole database first, then do a "filter" or "find" command, and then apply the field update.

Also my bad on the $sqlRs.FIELDS('"' & $OptionName & '"') = ".F." as I didn't pay enough attention to what you had posted, of course you need the $sqlRs.FIELDS('value') = ".F."! Good catch.

Anyway I did some testing with your original post on my own dbf, and you can use your original code.

If you run you original post with the Com Error handler, instead of just a "The requested action with this object has failed" message, I am sure you would get a "data type mismatch error".

That's because the "value" field is a logical field, and while ".F. and ".T." are used in foxpro programming, they are strings here and can't be used as a value in that field.

The value for true is 1 and false is 0

$sqlCMD.CommandText = "update Opts.dbf set value = 0 where name = 'Option1'"

Note: no quotes around the 0

By the way, if you still need to know, the value of adCmdText = 1

Hope that helps

[mrwb]

Finally got it working!!

I did have the COM Error handler in when I posted last. I did not get the data type mismatch . Even though it looks like it should be a binary field because of the .T. and .F. they are just storing it as character. It is one of those generic tables that stores configuration options as individual records so one record could have OptionA in the name field and a T/F representation for option value but OptionB could contain a directory path for the option value field.

Field   Type
===== ==========
name      char(15)
value    char(100)

I could never get the $sqlRS.Update to work. I went back to the $sqlCon.Execute and fixed the syntax of my update statement. [$sqlCMD worked also but the $sqlCon.Execute is shorter to code]

I changed update <filename> to update <tablename>. Then I had the issue where the fieldname I was trying to update is also a keyword so I had to specify tablename.fieldname:

$sqlCon.execute("update C:\Myfiles\Opts.dbf set value = '.T.' where name = 'Option1'")

became

$sqlCon.execute("update Opts set Opts.value = '.T.' where name = 'Option1'")

One final cleanup I had to do was StringStripWS when I was getting the $OptionVal. I had spaces at the end of the string that caused my if...then to fail.

[ResNullius]

Finally got it working!!

Good to hear! Sorry I wasn't as much help as I should have been, but if you need any more VFP help, post away and I promise I'll do better next time :">

[odaylton]

I have done some searching here on ADODB and FoxPro but have come up empty. I can connect to our MS SQL databases with ADO and read and update records but cannot do the same with a Visual FoxPro file. The file is not part of a DBC - it is a free file containing program options. I need to toggle a couple of values. Here is what I have so far:

$sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Mode = 16 ; sharedenynone
    $sqlCon.CursorLocation = 3 ; client side cursor
    $sqlCon.Open("Provider=vfpoledb.1;Data Source=C:\Myfiles\;Collating Sequence=general;")
    if @error Then
        MsgBox(0, "ERROR", "Failed to connect to the database")
        Exit
    EndIf

    $sqlRs = ObjCreate ("ADODB.Recordset")
    if not @error Then
        $sqlRS.open("select name, value from Opts.dbf where name = 'Option1'", $sqlCon)
        if not @error Then
            ;Loop until the end of file
                While Not $sqlRS.EOF
                    ;Retrieve data from the following fields
                    $OptionName = $sqlRS.Fields('name').Value
                    $OptionVal = $sqlRS.Fields('value').Value
                    MsgBox(0, "Record Found", "Name: " & $OptionName & @CRLF & "Value: " & $OptionVal)
                    $sqlRS.MoveNext
                    WEnd
            $sqlRS.close
            $sqlCMD = ObjCreate("ADODB.Command")
;~          $sqlCMD.CommandType = adCmdText ; need to find integer values for these options
            $sqlCMD.ActiveConnection = $sqlCON
            $sqlCMD.CommandText = "update Opts.dbf set value = '.F.' where name = 'Option1'"
            $sqlCMD.Execute
;~          if $OptionVal = '.T.' Then
;~              $sqlCon.execute("update C:\Myfiles\Opts.dbf set value = '.F.' where name = 'Option1'")
;~          Else
;~              $sqlCon.execute("update C:\Myfiles\Opts.dbf set value = '.T.' where name = 'Option1'")
;~          EndIf
        EndIf
    EndIf

The message box does popup with the expected values from the database. The read is succeeding.

The error I get is at the $sqlCMD.Execute (or $sqlCon.execute if I try the other method) : ==> The requested action with this object has failed.:

translated by google (sorry for mistakes) (PTBR)

I know it is another matter but who knows with their esperiencias would possibly help me.

I have a program in foxpro done by others and can control it perfectly but I would make sure the current focus.

I can get the handle of the current form but do not know where I'm joined field.

Control only send () and sleep (500) between ({TAB}) sent for each registration but I would be sure to identify which field I am really alert by current focus.

x, y coordinate of the field or Handle field name or if there