Inserting Data into Sql from Vfp

[DominicProgrammer]

Hi Friends.

Kindly assist me post this data from vfp table to sql server.

"CLOSE ALL
USE timback IN 0 ORDER dc
gnConnHandle=SQLCONNECT('SQL',') && proper(mlogname)
IF gnConnHandle <= 0
MESSAGEBOX('Could not Connect to the SQL Server.', 16, 'SQL Server Error')
SQLDISCONNECT(gnConnHandle)
RETURN

ENDIF
sele timback
SQLEXEC(gnConnHandle,'insert into SMHP_Tmp_Data4 (hp_empno,empno) select hp_empno,empno from timback')
messagebox('Upload complete')
SQLDISCONNECT(gnConnHandle)"

 

[TamarGranor]

Your problem is that you're telling SQL Server to add records from a VFP table. You can't do that because SQL Server can't see the VFP table. The right way to do this depends on various factors, starting with whether this is a one-time thing or something you need to do regularly, and also including how many records you expect to add at once.

Couple of other points.

1) In this forum, wrap your code in code tags. That's the word "code" inside square brackets to open and "/code" inside square brackets to close. That'll keep the formatting of your code and make it easier to read.

2) This block of code:

SELECT hp_empno,empno FROM timback INTO TABLE tmpshft
calias=ALIAS()
SELECT &calias

is ugly and unnecessary. When you create a table or cursor with SQL SELECT, the table/cursor name becomes the alias, unless it's invalid for an alias. In addition, that work area becomes current. So, in this case, you don't need the two lines following.

Beside that, never, never, never use a macro operator in a place where VFP expects a name and only a name. Instead, use parentheses. So, if you even needed the SELECT line, it should be:

SELECT (cAlias)

3) In my view, you also should be using the mdot notation when referring to variables, so I'd actually write that last line as:

SELECT (m.cAlias)

That way, there's no risk of mixing up a variable with a field name.

Tamar

 

[DominicProgrammer]

Thanks Soo Much Tamar. Its very True what you say,i actualy forgot to remove those lines:

En Way for now Its a one time update from a vfp table into sql, about 300 records. I will appreciate if you can show me how to insert that data into sql, the vfp table is a free table, i use ODBC to make the connection.

Thanks alot.
Dominic

 

[Olaf Doschke]

In case of a one time job, I'd rather extend your approach to add single secords and iterate over the VFP table:

In short if you have a table with two fields id,data you can "upload" it this way:

lnHandle = SQLStringConnect(...)
SQLPrepare(lnHandle,"Insert Into SQLtable (id,data) Values (?VFPTable.id, ?VFPTable.data)")
Use VFPtable.dbf ALIAS VFPTable
Scan
   SQLExec(lnHandle)
EndScan

SQLPrepare is not necessary, you could also execute the query given in the SQLPrepare as the second parameter of SQLExec, but it helps SQL Server to know the query is the same and now just has other parameter values.
Those ?VFPTable.id, ?VFPTable.data are forwarded by VFP as ODBC parameters of the ODBC driver used in the connection, SQL Server then get's the values from the driver, it does not read form the VFPTable alias, as Tamar already said that's impossible. VFP->ODBC->SQL Server are the components of SQL Passthrough in general and these three are separated.

Bye, Olaf.

 

[DominicProgrammer]

Hi Olaf.

Thanks A Bunch, your solution so simple and it worked so well and av already completed the task uploading over 10,000 records into Sql server Erp. Thanks So much.

Dominic