Results 1 to 6 of 6

Thread: No cursor was declared

  1. #1
    Join Date
    May 2003
    Location
    Belvedere
    Posts
    3

    Unhappy 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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  3. #3
    Join Date
    May 2003
    Location
    Belvedere
    Posts
    3

    Unhappy

    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
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  5. #5
    Join Date
    May 2003
    Location
    Belvedere
    Posts
    3

    Talking

    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.

    Thank you for your guidance.

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

  6. #6
    Join Date
    May 2015
    Posts
    1

    very usefull

    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 View Post
    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.

    Thank you for your guidance.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •