dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Posts
    656

    SQLSetPos failure

    Hi,
    I am trying to do follwing:
    Execute a query on the database and grab just some fields results.
    Then call SQLSetPos( stmt, 0, SQL_POSITION, SQL_LOCK_NO_CHANGE ); and then process the complete data again.

    Unfortunately the call to SQLSetPos() fails with the "Invalid cursor state". Looking at the MSDN, it says:

    The StatementHandle was in an executed state, but no result set was associated with the StatementHandle.
    I am not sure I understand. I am just trying to move the cursor pointer to the first record of the recordset and so yes the statement is in the executed state and the result set should be available from the record 1 again.

    Does anybody knows how do I fix it? Or there is a better way?

    I was trying to not to re-create the statement handle, since I already have it and I already have a result set.

    Just tried to use "1" as a second parameter. Got the same error.


    Thank you.
    Last edited by OneEyeMan; June 28th, 2018 at 11:19 PM.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,167

    Re: SQLSetPos failure

    Quote Originally Posted by OneEyeMan View Post
    Hi,
    I am trying to do follwing:
    Execute a query on the database and grab just some fields results.
    Then call SQLSetPos( stmt, 0, SQL_POSITION, SQL_LOCK_NO_CHANGE ); and then process the complete data again.

    Unfortunately the call to SQLSetPos() fails with the "Invalid cursor state". Looking at the MSDN, it says:

    The StatementHandle was in an executed state, but no result set was associated with the StatementHandle.
    MSDN also mentions some other reasons causing this error:

    (DM) A cursor was open on the StatementHandle, but SQLFetch or SQLFetchScroll had not been called.

    A cursor was open on the StatementHandle, and SQLFetch or SQLFetchScroll had been called, but the cursor was positioned before the start of the result set or after the end of the result set.

    The argument Operation was SQL_DELETE, SQL_REFRESH, or SQL_UPDATE, and the cursor was positioned before the start of the result set or after the end of the result set.
    Victor Nijegorodov

  3. #3
    Join Date
    Aug 2002
    Posts
    656

    Re: SQLSetPos failure

    Hi,
    After the initial Fetch() loop my cursor is positioned after the last record:

    Code:
    for( ret = SQLFetch( stmt ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( stmt ) )
    {
    }
    So how can I set the record pointer to 1 (or 0)?

    Thank you.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,685

    Re: SQLSetPos failure

    Why are there two
    Code:
     ret = SQLFetch( stmt )
    calls in the for loop?

  5. #5
    Join Date
    Aug 2002
    Posts
    656

    Re: SQLSetPos failure

    Hi, Arjay,
    Here is the full loop:

    Code:
    for( ret = SQLFetch( stmt ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( stmt ) )
    After it is done, the record pointer is behind the last record and call to SQLSetPos() fails.
    See anything wrong with it?

    Thank you.

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,167

    Re: SQLSetPos failure

    Quote Originally Posted by OneEyeMan View Post
    Hi, Arjay,
    Here is the full loop:

    Code:
    for( ret = SQLFetch( stmt ); ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO ) && ret != SQL_NO_DATA; ret = SQLFetch( stmt ) )
    Well, your for loop is very hard to read/understand.
    Why not just rewrite it like you can see it in MSDN example here?
    Victor Nijegorodov

  7. #7
    Join Date
    Aug 2002
    Posts
    656

    Re: SQLSetPos failure

    Hi,
    I think the SQLSetPos will still fail as I will break from the loop with SQL_NO_DATA.
    Or am I missing something?

    Thank you.

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    12,685

    Re: SQLSetPos failure

    Quote Originally Posted by OneEyeMan View Post
    Hi,
    I think the SQLSetPos will still fail as I will break from the loop with SQL_NO_DATA.
    Or am I missing something?

    Thank you.
    At this point, I'd suggest stepping through your code to understand what changes to make and/or compare with the code in the link Victor provided..

  9. #9
    Join Date
    Aug 2002
    Posts
    656

    Re: SQLSetPos failure

    Hi, guys,
    I got a suggestion to use SQLFetchScroll with SQL_FETCH_FIRST parameter.
    I will try to use that for now and if I hit an issue I will come back.

    Thank you.

  10. #10
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,167

    Re: SQLSetPos failure

    Quote Originally Posted by OneEyeMan View Post
    Hi, guys,
    I got a suggestion to use SQLFetchScroll with SQL_FETCH_FIRST parameter.
    I will try to use that for now and if I hit an issue I will come back.

    Thank you.
    OK! Try it!
    But did you try the code from MSDN example?
    Victor Nijegorodov

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)