No cursor was declared

I am at present constructing a little program in Visual Basic 6 to extract some data from an Microsoft SQL server running our main database within the college and have run into a little problem that has me stumped.
I have been using an MS access database that represents the final target database (Table names, Field names etc are identical with test data) and have developed an SQL select statement that works fine from within VB code on this access database :). But when I redirect the same SQL statement to the SQL server I get the following error.:(

Run-time error ‘40002’:
37000:[Microsoft][ODBC SQL server driver][SQL server]The cursor was not declared.

SQL Statement is as follows:

SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST, STENenrols AS se, st INNER JOIN se ON st.stud_student_id=se.sten_student_id WHERE se.sten_expctd_end_date__q17 > #" & Date & "# and se.sten_actual_end_date__q18 IS null

Database Structure
Table STENenrols
STEN_Student_ID Key field
STEN_College_Year
STEN_Prog_Code
STEN_Type_Of_Record
STEN_Actual_End_Date__Q18
STEN_Expctd_End_Date__Q17
STEN_Completion_Stat_Q19

Table STUDstudent
STUD_Student_ID key field
STUD_College_Year
STUD_Surname_s02
STUD_Forename_1_s03
STUD_Primary_Programme

Is there some thing I have overlooked with regard to differences between ACCESS and MS SQL Server?
:confused:

Your join statement needs to be changed, try this


SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST
INNER JOIN STENenrols AS se
ON st.stud_student_id=se.sten_student_id WHERE se.sten_expctd_end_date__q17 > getdate() and se.sten_actual_end_date__q18 IS null


If you are trying to get current date, use getdate() function instead of Date.

I have just tried your suggested code and got the same error again. The date part when tried on the access database fails when reverted to my original bit of code for date the select statement works fine on access but not the SQL server:confused:
Just to rule out the date I have now entered a fixed date yet again this works fine with the access database but not the SQL server.

Can you run the SELECT statement in Query Analyzer.

I don't see anything wrong with the SQL Statement.

Are you using ODBC to make a connection? Your version of ODBC may not support ANSI join syntax, try this one if you have to use ODBC



SELECT ST.STUD_Student_ID , ST.STUD_Surname_S02 , ST.STUD_Forname_1_S03, se.STEN_Student_ID, se.STEN_Prog_code FROM STUDstudent AS ST, STENenrols AS se
WHERE
st.stud_student_id=se.sten_student_id and se.sten_expctd_end_date__q17 > getdate() and se.sten_actual_end_date__q18 IS null

To answer your question yes I am using ODBC to talk to the SQL database server.
Did a bit more research yesterday and found some information on Microsoft site (Knowledge base article 154825) that suggested using the following code to force the ODBC to use local cursors not server cursors and all appears to be working fine for me now.:D

Thank you for your guidance. :cool:

The following line was added in to my VB code
rdoEnvironments(0).CursorDriver = rdUseOdbc

rdoEnvironments(0).CursorDriver = rdUseOdbc

Set Env1 = rdoEngine.rdoEnvironments(0)
Set CN = Env1.OpenConnection(odbcBud, rdDriverNoPrompt, False, sCon)
CN.QueryTimeout = 0

SQL1 = "SELECT * " & _
"FROM BorramePresupuesto " & _
"WHERE (len(CENTRAB)>16 or len(DEPTO)>16 or len(MONEDA)>1 or len(CVE_EGRESO)>1 or " & _
"len(TIPOREC)>2 or len(RENGLON)>3 or len(TIPO_PPTO)>1 or len(CVE_GASTO)>1 or " & _
"len(PROYECTO)>10 or len(OBRA)>11 or len(SUBDIR)>4 or len(PIN_PEF)>24 or len(CONOR)> 6) "


Set Rset1 = cn.OpenResultset(SQL1, rdOpenKeyset, rdConcurReadOnly, rdExecDirect)

If Rset1.RowCount > 0 Then

Quote:

---

Originally Posted by dougb

To answer your question yes I am using ODBC to talk to the SQL database server.
Did a bit more research yesterday and found some information on Microsoft site (Knowledge base article 154825) that suggested using the following code to force the ODBC to use local cursors not server cursors and all appears to be working fine for me now.:D

Thank you for your guidance. :cool:

The following line was added in to my VB code
rdoEnvironments(0).CursorDriver = rdUseOdbc

---