Beginning SPT help needed...

[thatguy]

Hey there folks--

I'm trying to connect to an Advantage database back-end thru ODBC, pull what I need into an updatable cursor, makes changes and close the connection.. it sounds simple enough and from what I've read so far, I'm pretty sure I'm doing everything that needs to be done.. here's the code I have:

locxn = SQLCONNECT('PatientFile')
lnretval = SQLPREPARE(locxn,"SELECT * FROM Data10",'lData10')
?'prepare: ' + STR(lnretval)

lnretval = SQLEXEC(locxn)
?'exec: ' + STR(lnretval)

?CURSORSETPROP("Tables","Data10",'lData10')
?CURSORSETPROP("UpdatableFieldList","Appt_len",'lData10')
?CURSORSETPROP("UpdateNameList","Appt_len appt_len",'lData10')
?CURSORSETPROP("SendUpdates",.t.,'lData10')

LOCATE FOR ldata10.code = '99205'
IF FOUND('ldata10')
    replace ldata10.appt_len WITH 99
ENDIF

SELECT ldata10
BROWSE

USE IN ldata10
lnretval = SQLDISCONNECT(locxn)
?'disconnect: ' + STR(lnretval)

Whenever this gets to the [USE IN ldata10] line, it gives me a "No update tables are specified" error, but using CURSORGETPROP() on ldata10 shows Data10. The only way I can get away from the error is by ending Foxpro from task manager.

Could someone explain what I'm doing wrong?

Thanks
-- frank~

 

[Mike Gagnon]

pull what I need into an updatable cursor, makes changes and close the connection.

What are you try to achieve? Are you trying to update the SQL table with your cursor? If so this technique will not work.

?CURSORSETPROP("Tables","Data10",'lData10')

This line looks wrong.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[thatguy]

Well, I'm trying to connect to and manipulate data in an Advantage database on our server, but whenever I try to connect using a remote view in a local database, Foxpro generates a fatal error and dies. SQL passthrough is the only way that I can connect to the data without errors, but, of course, I'm unfamiliar with how it all works and I haven't found a good tutorial on it yet. I'm trying to use simple table commands (replace, locate, etc) instead of (UPDATE <table> SET <field>...), which I'm not really familiar with -- kind of looking for a quick solution since this will just be a one-time fix-the-data-that-was-imported-incorrectly kinda of thing.

Any thoughts?

Thanks
-- frank~

 

[foxwizard]

Frank,

You don't need to pull all the data from remote table then search for the record make changes to an updatable cursor then update the remote database. You can directly pass an SQL statement to the remote table using SPT.

Try this:

=sqlexec(locxn,"update data10 ;
     set appt_len = 99 where code = '99205'")

Hope this will help.


Bren
PJH - Philippines

 

[Mike Lewis]

Frank,

You posted this question, and I suggested an answer, in forum Microsoft: VFP - Databases, SQL&VFP, and Reports.

It is not very helpful to post identical questions in more than one place. People take the trouble to reply to one of them, only to find that others have aleady replied in the other.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[thatguy]

Hello Bren--

The LOCATE was originally used just for testing. I have to move data from one field to another in every record of the table (move "work_phone" to "home_phone"), which is why I was hoping to pull all records into a cursor, update them by SCANning thru the cursor, and write the changes back to the server.


Hello Mike--

I apologize for posting in two forums, but the traffic in the SQL forum is, at best, half that of here and I was looking for a quick answer, so I tried posting here to get more views. I'll stop that.

You mentioned taking out the SQLPREPARE for a direct SQLEXEC to create the cursor. Unfortunately, I tried that first and got the same results.

I'm out of the office for a couple days, but I'll play around more when I get back.. I'm just trying to avoid having to manually move a phone number from one field to another in about 4500 records.

Thanks and my apologies again
-- frank~

 

[Mike Gagnon]

I apologize for posting in two forums, but the traffic in the SQL forum is, at best, half that of here and I was looking for a quick answer, so I tried posting here to get more views.

This is due to a recent forum split. Where there was only one VFP forum in the past (this one) and they where split up in a logical fashion. But the "experts" all have an eye in all 5 areas.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.