Microsoft KB Archive/113753

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft FoxPro Connectivity Kit, version 2.5
 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
 * Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6

-

SUMMARY
By using the FoxPro Connectivity Kit's DBExec command, you can insert records in a native platform's database that you are connecting to.

MORE INFORMATION
Using the standard SQL INSERT command with the Connectivity Kit's DBExec command, you can insert records into a table that exists on the native platform. "Native platform" refers to the DBMS that you are connecting to.

In the example below, the source is SQL Server and the code example inserts a record in the sales table in the pubs database.

With minor modifications, this same code can be used to insert a new record in an Oracle database or in any other data source that may be in use.

NOTE: If you have problems running the following code in FoxPro for MS-DOS, please see the following article in the Microsoft Knowledge Base:

"Q112482 How to Make an MS-DOS Level SQL Server Connection"

Code Sample
 *****SET THE LIBRARY AND INITIALIZE VARS IF _DOS SET LIBRARY TO SYS(2004)+"fpsql.plb" ELSE SET LIBRARY TO SYS(2004)+"fpsql.fll" ENDIF

PUBLIC errval PUBLIC errmsg PUBLIC handle PUBLIC do_more

errval=0 errmsg=' ' do_more=.T.

*****SOURCE INFORMATION sourcename= 'test' user= 'sa' passwd=''

*****CONNECT

handle=DBConnect(sourcename,user,passwd) IF handle > 0 WAIT WINDOW 'Successfully Connected' ELSE error=DBError(0,@errmsg,@errval) WAIT WINDOW STR(error)+' '+STR(errval)+' '+errmsg do_more=.F.  ENDIF

IF do_more=.T.     *****SET SESSION DEFAULTS

=DBSetOpt(handle,'Asynchronous',0) =DBSetOpt(handle,'BatchMode',1) =DBSetOpt(handle,'ConnTimeout',0) =DBSetOpt(handle,'Transact',1) =DBSetOpt(handle,'UseTable',0)

*****USE THE PUBS DATABASE ON SQL SERVER; On SQL Server *****it is necessary to open the pubs database *****in order to insert a record into the sales table that is     *****in the pubs database. In other words, databases *****on SQL Server contain tables and we are inserting *****into a table that is part of the pubs database.

err=DBExec(handle,'use pubs') DO errhand WITH err,'USE PUBS'

*****INSERT_IT-- THE PROCEDURE THAT INSERTS RECORDS INTO *****THE SALES TABLE.

DO insert_it

*****Displays the change made after the insert is completed.

sqlcomm= 'select * from sales' err=DBExec(handle,sqlcomm) DO errhand WITH err,"DBExec(handle,"+sqlcomm+")"

IF err > 0 WAIT WINDOW 'PRESS ESC TO CLEAR BROWSE WINDOW AND TO DISCONNECT' BROWSE ENDIF

*****DISCONNECT

err=DBDisconn(handle) DO errhand WITH err,"DBDisconn" SET LIBRARY TO     CLOSE ALL ENDIF

*****Simple Error Handler Program PROCEDURE errhand PARAMETERS err,command IF err > 0 WAIT WINDOW ALLTRIM(UPPER(command))+" Completed Successfully" ELSE ? UPPER(ALLTRIM(command))+" NOT Completed Successfully" error=DBError(handle,@errmsg,@errval) ? ALLTRIM(STR(error))+" "+ALLTRIM(STR(errval))+" " ; + ALLTRIM(errmsg) ENDIF RETURN

*****THE DBExec FUNCTION IS USED TO INSERT A RECORD

PROCEDURE insert_it

m.stor_id="0000" m.ord_num="QA7442.3" m.date="09/13/85" m.qty="75" m.payterms="Pauls Play" m.title_id="PS2091"

insertcomm= "insert sales ;               values ("+"'"+ALLTRIM(m.stor_id)+"',"+ ;                          "'"+ALLTRIM(m.ord_num)+"',"+;                          "'"+ALLTRIM(m.date)+"',"+;                          + ALLTRIM(m.qty)+","+;                          "'"+ALLTRIM(m.payterms)+"',"+;                          "'"+ALLTRIM(m.title_id)+"'"+")"

err=DBExec(handle,insertcomm) DO errhand WITH err,"DBExec(handle,"+insertcomm+")" IF err > 0 WAIT WINDOW 'Insert went ok' ENDIF

RETURN * End of Code Additional query words: FoxDos FoxWin ODBC CK

Keywords         : kbcode FxtoolCk Version          : 2.50 2.50a 2.50b 2.60 | 2.50 2.5 Platform         : MS-DOS WINDOWS Issue type       :
 * }