>I am fairly new to working with SQL server and am looking for the best way to do inserts and updates. I am working on additions to an existing program and so do not have a VFP DBC to store remote views although I could create a temporary one if needed. The code below is what I am using to send the contents of a temporary VFP table to a SQL server table. It works just fine but I can't believe that it is the best way to do it. With all of the typing and quotes and double quotes and continuation lines, it is a real pain (this example is a table with the least number of fields that I could find).
>
>I have looked all over to find a utility to speed up some of this "grunt" work but the closest I could find was one by Erik Moore (where is he anyway?) that would create a stored procedure on the SQL server. I have a similar question about doing updates. What am I missing?
>
>Thanks,
>Bill
I don't believe it should be so hard either :) Search for SPT and you'll find tons of samples. For a quick sample check this one that updates pubs.dbo.authors (what it does is to make an SPT cursor an updatable cursor) :
lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
'SERVER=servername;DATABASE=pubs;Trusted_Connection=Yes')
SQLExec(lnHandle,'select * from dbo.authors','v_authors')
CursorSetProp('KeyFieldList','au_id','v_authors')
CursorSetProp('WhereType',1,'v_authors')
CursorSetProp('Tables','authors','v_authors')
CursorSetProp("UpdateNameList", ;
"au_id authors.au_id,"+;
"au_lname authors.au_lname,"+;
"au_fname authors.au_fname,"+;
"contract authors.contract",'V_authors')
CursorSetProp('UpdatableFieldList','au_fname,au_lname,contract','v_authors')
CursorSetProp('SendUpdates',.T.,'v_authors')
CursorSetProp('Buffering',5,'v_authors')
Browse title "You're allowed to update lname, fname, contract"
Tableupdate(2,.T.,'v_authors')
SQLExec(lnHandle,'select * from dbo.authors','afterupdate')
SQLDisconnect(lnHandle)
Select afterupdate
Browse
PS: VFP8 cursoradapter makes this much easier.
Cetin