Hello group,

I am trying to create a cursor from data on a SQL 6.5 machine. The SQL code is copied from the view designer where it ran fine, only the last line of the SQL has been changed so I can get the schoolyear ( a numeric value ) from the nSchoolyear field from the tblschool year.
The connection works I tested it with sqlexec(lnhandle,'select * from cisstudemo','brboe')


LOCAL lcScript, lnHandle, lnSchoolyear, lcSQL, lnDone
lnSchoolyear = 0
lnDone = 0
lnHandle = 0

* if it is not open, open schoolyear

if !used("tblschoolyear")

use tblschoolyear

endif

scatter memvar

lnSchoolyear = nschoolyear && in this case the nschoolyear is = 2003

SELECT tblschoolyear
USE

lcSQL = "SELECT Zschdist.descript, Studemo.lastname, Studemo.firstname," +;
" Studemo.ident, Studemo.genderc, Studemo.ethnicc, Studemo.birthdate," +;
" Studemo.gradyear, Studemo.phnnumber" +;
" FROM dbo.course Course, dbo.trkcrs Trkcrs, dbo.track Track," +;
" dbo.mstsched Mstsched, dbo.mstmeet Mstmeet, dbo.stusched Stusched," +;
" dbo.studemo Studemo, dbo.zschdist Zschdist" +;
" WHERE Trkcrs.crsuniq = Course.crsuniq" +;
" AND Track.trkuniq = Trkcrs.trkuniq" +;
" AND Mstsched.trkcrsuniq = Trkcrs.trkcrsuniq" +;
" AND Mstmeet.mstuniq = Mstsched.mstuniq " +;
" AND Stusched.meetuniq = Mstmeet.meetuniq " +;
" AND Studemo.suniq = Stusched.suniq "+;
" AND Studemo.resdistc = Zschdist.schdistc " +;
" AND (Course.coursec BETWEEN '79T00'and '79T99' "+;
" (Course.coursec BETWEEN '79W00' and '79W99'"
" AND Track.schyear = " + alltrim(Str(lnSchoolyear))+ "))"

** this is all one line on my program
lcScript = "DRIVER=SQL server;SERVER=196.106.178.171;UID=sa;PWD=password;APP=Microsoft® Visual FoxPro®;WSID=AUDAT;DATABASE=jojo"

** end of lcScript
lnHandle= SQLStringConnect(lcScript)
sqlexec(lnhandle,lcSQL,'brboe')


Thanks
Jim