VFP6.0 INTO Microsoft SQL 7

[dakotafox]

I have been successful with connecting with commands from VFP6.0 into SQL 7 with a select statement, and retriving the correct data. The connection handles are correct
and the following is an example of that.

hdlPLINE = sqlconnect("fox2sql","sa","&quot


sSQL = "select d_customer,d_lineno,d_departm,d_user from tblAMC2 where d_customer='SUN'"

*-----SQLPrepare( nConnectionHandle,cCommand,cResultCursor )

sqlprepare(hdlPLINE,sSQL,"sqlPLINE&quot
rval= sqlexec(hdlPLINE)

However, I cannot come up with the correct syntax for INSERT or UPDATE, and the SQLRESULT is always -1. I have tried diffent examples found in publications including the method described in ADVISOR APRIL 2001, in Grow Solutions with MSDE. That command will not work.
Any help will be appreciated.

 

[foxdev]

What kind of error message are you getting? I'm sure you've already seen the basic commands, but here goes again:

[tt]hdlPLINE = sqlconnect("fox2sql","sa","&quot
cSQL = "insert into tblAMC2 " + ;
"(d_customer,d_lineno,d_departm,d_user) " + ;
" values ('value1', str(numvalue,10,2), 'value3', 'value4')"
sqlexec(hdlpline, cSQL)
[/tt]

Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[dakotafox]

Thanks for your input. We are running a scan of an open table from which to insert the records, so the values would be fields, and the syntax does not work, the same as the text values.

Thank you

 

[Guntruck]

Dakota, I highly suggest using ADO. I wrote a piece of software that does that same very thing. Just scans a .dbf and inserts it into a sql database. Check out this link,

http://support.microsoft.com/suppor...ado find&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=FOX

hope this helps

dave

 

[foxdev]

the syntax does not work

Again, I'd like to help, but you aren't telling me the error message you are receiving, nor the types of data columns you are working with. An example that uses various column data types:

[tt]cSQL = "insert into tblAMC2 " + ;
"(d_customer,d_lineno,d_departm,d_user) " + ;
" values ('" + MyCharColumn + ;
"', " + str(MyNumColumn,10,2) + ;
", '" + dtoc(MyDateColumn) + "', 'Myliteral')"[/tt]

Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[dakotafox]

Thank you Robert,
That is the frustrating problem for me. There is no error message but there is no insert of the record into the sql table. If I add in the following I get the -1 result:
SQLResult= SQLExec(nhdlfile,SQLcmd)

if SQLResult < 0
*---this is an error condition
messagebox(&quot;SQL Result is an error condition and is &quot;+str(sqlResult),0)
endif
The program will move thru each record in the table containing the insert records. If set step is on, the cSQL statement contains the fields and the values correctly, but again no actual insert into the table.
Thanks for all your help.

 

[foxdev]

Ahhh, you need the secret debugging ingredient:

[tt]SQLResult= SQLExec(nhdlfile,SQLcmd)
cMsg = message()
if SQLResult < 1
messagebox(&quot;Error: &quot; + cMsg)
endif
[/tt]

The important part is to capture the contents of Message() immediately after the SQLExec statement, as it can get reset by subsequent statements. The resulting message will give you insight as to the root cause of the problem. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[dakotafox]

Once again thank you for pointing out what should have been an obvious step to take regarding getting the message. The message is:
Connectivity error:[Microsoft][ODBC SQL Server Driver][SQl Server]Cannot insert the value NULL into column d_m_account,table ......... does not allow null, insert fails.

This should solve my problem, your help is really appreciated.

 

[foxdev]

No problem. One more heads-up: a NULL in any one of your source VFP columns will make the whole string NULL, so you'll have to provide handling for nullable columns.

For example:

[tt]* -- MyColumn3 is NULL
cSQL = MyColumn1 + &quot;, &quot; + MyColumn2 + &quot;, &quot; + MyColumn3
[/tt]
In this example, cSQL will be NULL. Instead, you'll have to do something like this:

[tt]cSQL = MyColumn1 + &quot;, &quot; + MyColumn2 + &quot;, &quot; + NVL(MyColumn3, &quot;&quot
[/tt]

Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[dakotafox]

As long as I am on such a positive input roll, two other questions:
1. Does Update have the same syntax as insert&quot;
2. Is there a place in the knowledgebase or where that lists the syntax requirements for VFP working with SQL.

Thanks again for all your help, and it proves the great working relationship in the FoxPro community.

 

[foxdev]

No, UPDATE isn't exactly the same structure, but the techniques of joining the strings and accomodating different VFP field types is the same.

[tt]update MyTable set MyColumn1='This Value', MyColumn2='That Value' where This=That[/tt]

Having a WHERE clause is important, because it is the opposite of a VFP Replace command: not specifying a WHERE condition will change every single record. Not a pretty sight when you do it unintentionally.

Also, SQL Server supports UPDATE...FROM, which VFP does not.

If you have SQLS Books Online (BOL), you'll have all the SQLS docs you could ever want. If not, Microsoft has pretty much the whole thing online at msdn.microsoft.com
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[pac1exl]

Thank you to Robert and Guntruck, i try the two solutions and both works.

Thank you also to Dakotafox for posting the query.

I was able to learn a lot.