>Can anyone tell me why this code is not returning any values from the Connection. If I use the Interactive SQL query program that comes with SQL 6.5, and give it [Select * from students where loginid="john"], the query returns the expected results. If I use the program below, gnConnHandle is a positive integer; however, lnResult is -1 and the query returns nothing.
>
>If I try this with lcSQL = "Select * from students" it works just fine. It seems that the where clause is causing the problem. What am I overlooking? Using vfp5.0, sql6.5, nt4.0
>
>STORE SQLCONNECT('MyDataBase', 'sa') TO gnConnHandle
>IF gnConnHandle <= 0
> RETURN
>ENDIF
>
>lcStudentID='john'
>lcPassword = 'n'
>lcSessionId=''
>
>lcSQL = 'SELECT * FROM students WHERE Loginid="' + lcStudentID +'"'
>lnResult = SQLEXEC(gnConnHandle, lcSQL, 'cStud')
>SELE cStud
>brow
>IF _TALLY = 0
> **-- generate error
> RETURN "Bad Student ID"
>ENDIF

John,

I think the problem your're having is in substituting lcSQL in your lnResult statement. Try writing out the select in lnResult (lnResult = SQLEXEC(gnConnHandle,"SELECT * ......) and see if that works. You might also try macrosubstitution in the lcSQL statement: lcSQL = 'SELECT * FROM students WHERE Loginid = &lcStudentID'

You might want to rewrite your select as a stored procedure in SQLSever and pass the student id as a parameter. I've found that stored procedures are a very fast way to run queries like yours and you don't have to worry about differences in syntax between the front end and back end.

Good Luck!