How to establish connection to MS SQL database in VFP

[EdenD]

I have already finished my system with databases and tables defined in VFP meaning the *.dbf and *.dbc files. I was told by my superior to migrate it to MS SQL. I don't have experience yet in this implementation. All I know is to connect through remote views. But how can I reference to these MS SQL tables? connect in code? Any sample code you could share is greatly appreciated. Thanks in advance for all who will heed my call....

 

[rajeevnandanmishra]

Hi Edend,

I think the best way to start is start reading some book on using VFP & SQL.
Still i am giving here a few startup things. Hope this will move you in right direction.
--------------------------------------
1. Create a ODBC dsn for the sql server to which you want to connect.
2. In vfp create a public variable mP_handle and issue the command
mP_handle=SQLCONNECT(myDsn) &&Displays SQL Login Prompt
3. Issue the following command to retrieve data from SQL table
mP_ret=SQLEXEC(mP_handle,'SELECT * FROM myTable','myCursor')

** This will create a local cursor with name myCursor and populate it with all the records of myTable.
** If an error occurs, mP_ret will be <= 0, so can check its value to show error messages.
4. Do modification in the myCursor as you are doing in the local dbf.
5. A loop for updating data back to SQL server.
SELECT myCursor
GO TOP
DO WHILE ! EOF()
mcom=&quot;UPDATE myTable &quot;+;
&quot;SET myColumn=&quot;+myCursor.myColumn+;
&quot;WHERE myKey=&quot;+myCursor.myKey
mP_ret=SQLEXEC(mP_handle,mcom)
IF mP_ret<=0
*Routine for Showing Error Message
ENDIF
SKIP
ENDDO




These are very simple statements but help you as startup.

 

[foxdev]

See: faq184-65
and:

http://www.foxdev.com/educs.html

Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com