drhoades32


I've placed more of the code at the bottom of this message. The problem is with the Insert command with SQLEXEC.

If I use:

lcStr = ["INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')"]
lnResult=SQLEXEC(m.lnConn, m.lcStr)

The error I get says: Connectivity error:...Could not find stored procedure 'INSERT INTO SAUTHORS(firstname,lastname) values ("Kareen","Abdul-Jabar")'.

If I use:

lcStr = ['INSERT INTO SAUTHORS (firstname,lastname) values ("] + m.firstname + [","] + m.lastname + [")']

lnResult=SQLEXEC(m.lnConn, m.lcStr)

I get an error that says Invalid Syntax. Can someone steer me in the right direction I want to populate empty SQL Server Express tables with data from FoxPro tables (that are getting too big). Thank you!

USE authors IN 0

SELECT *;
from authors;
WHERE pkey > 0;
INTO CURSOR sqlAuthors readwrite

USE IN AUTHORS
SELECT sqlAuthors

INDEX on UPPER(lastname)+UPPER(firstname)+UPPER(middle) TAG lastname
lnConn=SQLSTRINGCONNECT("dsn=DataSrc1;UID=MOMNEW\Diana;PWD=")

=SQLEXEC(m.lnConn, 'use books') && use sauthors
= SQLSETPROP(m.lnConn, 'Transactions', 2) && Manual transactions
SELECT sqlAuthors
SCAN
llSuccess = .t.
SCATTER MEMVAR MEMO
firstname = ALLTRIM(m.firstname)
middle = ALLTRIM(m.middle)
lastname = ALLTRIM(m.lastname)

lcStr = ["INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')"]
lnResult=SQLEXEC(m.lnConn, m.lcStr)

IF lnResult = -1
llSuccess = .f.
=MESSAGEBOX(MESSAGE(),0,"ERROR OCCURRED")
ENDIF




Re: SQL Server Express & Foxpro-populating SS from Foxpro

AndyKr


This is becuase you are using the quotes incorrectly in every case.
The rules are that:
Double Quotes (") are reserved for names, and cannot be used as delimiters
SQL Statements must be passed as Literals, and parameters must be enclosed in SINGLE quotes
Embedded single quotes (') must be doubled ('')

So lcStr = ["INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')"]
Fails becuse you are actually enclosing the whole string in Quotes, making it a Paramer being passed to a Stored procedure.
This should be either:
lcStr = [INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')]
or
lcStr = "INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')"

And lcStr = ['INSERT INTO SAUTHORS (firstname,lastname) values ("] + m.firstname + [","] + m.lastname + [")']
fails because you are using double quotes as a string delimiter AND still enclosing the entire statement as a string:

This should be either:
lcStr = [INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')]
or
lcStr = "INSERT INTO SAUTHORS(firstname,lastname) values ('] + m.firstname + [','] + m.lastname + [')"

Use the correct form odf the command and you will have no problems







Re: SQL Server Express & Foxpro-populating SS from Foxpro

drhoades32

Thanks for the syntax help--I knew it had to be an issue.

I used your first lcStr example above and then when the code hits the SQLEXEC I get:

Connectivity error...String or binary data would be truncated.

Do you have any suggestions on that. Thanks for your time and suggestions!






Re: SQL Server Express & Foxpro-populating SS from Foxpro

AndyKr

>>Connectivity error...String or binary data would be truncated.

A column is SQL Server is not large enough to accommodate the data you are trying to insert. Presumably one or other of FirstName/Lastname (or both) is longer than the column definition - check the column sizing!






Re: SQL Server Express & Foxpro-populating SS from Foxpro

drhoades32

This is my first foray into SQL Server and I'm trying to do a small test run before expanding my code. Thanks for your patience.

I wasn't populating my key fields, so I changed this line to this:

lcStr = [INSERT INTO SAUTHORS(APKEY, firstname,lastname) values (] + M.PKEY + [,'] + m.firstname + [','] + m.lastname + [')]

And I get the Operator/operand mismatch error. With PKEY and APKEY being integers, is there a different syntax I have to use since it's not character or string type





Re: SQL Server Express & Foxpro-populating SS from Foxpro

drhoades32

And one more question. I can't find my SQL Server database or tables on my hard drive (working off one PC). I have the options checked so that all hidden files should be visible and yet I can't find it and any Search I do produces no results. Any help on this Thanks again!



Re: SQL Server Express & Foxpro-populating SS from Foxpro

AndyKr

>> lcStr = [INSERT INTO SAUTHORS(APKEY, firstname,lastname) values (] + M.PKEY + [,'] + m.firstname + [','] + m.lastname + [')]

>> And I get the Operator/operand mismatch error. With PKEY and APKEY being integers, is there a different syntax I have to use since it's not character or string type

Yes, you have to convert values to strings, so for numerics/boolean/currency use TRANSFORM(), for dates and datetimes use the appropriate date function. Your string should, therefore be:

values (] + TRANSFORM( M.PKEY ) + [,'] + m.






Re: SQL Server Express & Foxpro-populating SS from Foxpro

AndyKr

I think you need to buy a book on SQL Server! There are several available - go to Amazon.com and search for SQL Express.

I am afraid that I can't run a continuous on-line tutorial service for free and I have no idea where SQL Express puts its files by default since I do not use it.

Check out the SQL DMO object in MSDN Help - I am sure it has properties that will tell you where the files are located - but they will probably be somewhere in your C:\PRogram files\ directory tree....