Results 1 to 13 of 13

Thread: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    DFW, Texas
    Posts
    35

    SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    I keep getting a -1 return value in rstUsers.RecordCount, meaning the query produces no results:

    The code in VB looks like this:

    Dim strsql As String
    CONST DUPKITTING = "dupkitting"
    strsql = "SELECT * FROM Admins WHERE user_name = '" & (strusername) & "' AND _
    Security_function = '" & (DUPKITTING) & "'"
    set rstUsers = mconsoftaid.Execute(strsql, adCmdText)
    If Err Then
    MsgBox "Unexpected error retrieving secuity details!", vbOKOnly
    Else
    If rstUsers.RecordCount >= 1 Then
    llUserAuthorized = True
    Else
    lluserAuthorized = False
    End If
    End If

    This same code for the Query only is run in SSMS and produces the one expected record. Yet, running the above code in VB produces no records.

    What have I missed or done incorrectly?

    Cecil

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    The query itself probably executed fine, you can't use RecordCount to check. Use .EOF
    RecordCount may or may not be available depending on the cursor location, position and locktype.

    Try this instead
    Code:
        'Set rstUsers = mconsoftaid.Execute(strsql, adCmdText)
        Set rstUsers = New ADODB.Recordset
        rstUsers.Open strsql, mconsoftaid, adOpenStatic, adLockReadOnly
    or
    Code:
    mconsoftaid.CursorLocation = adUseClient
    or
    Code:
    If Not rstUsers.EOF() Then
    llUserAuthorized = True
    Else
    lluserAuthorized = False
    End If
    Last edited by DEXWERX; Aug 5th, 2016 at 12:59 PM.

  3. #3
    gibra
    Guest

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by cecilchamp View Post
    This same code for the Query only is run in SSMS and produces the one expected record. Yet, running the above code in VB produces no records.
    I don't think that your above code run good in SSMS.
    Why uses ( and ) around your variables ?

  4. #4

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    DFW, Texas
    Posts
    35

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    The parens ( ) are a bad habit from Visual FoxPro. However, the parens did not seem to interfere with the program, but I will remove them and see what happens.

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2016
    Location
    DFW, Texas
    Posts
    35

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    I went with your last suggestion and it worked perfectly. I was actually expecting RecordCount to be available but am surprised that once a cursor has been produced that we cannot get the record count.

    And, thank you very much for the enlightenment.
    Last edited by cecilchamp; Aug 5th, 2016 at 01:35 PM.

  6. #6
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by cecilchamp View Post
    I went with your last suggestion and it worked perfectly. I was actually expecting RecordCount to be available but am surprised that once a cursor has been produced that we cannot get the record count.
    you can, but that would require the entire recordset to be loaded.
    You could force it if you really want using.
    Code:
    rstUsers.MoveLast

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by cecilchamp View Post
    I went with your last suggestion and it worked perfectly. I was actually expecting RecordCount to be available but am surprised that once a cursor has been produced that we cannot get the record count.

    And, thank you very much for the enlightenment.
    Quote Originally Posted by DEXWERX View Post
    you can, but that would require the entire recordset to be loaded.
    You could force it if you really want using.
    Code:
    rstUsers.MoveLast
    More specifically it depends on the TYPE of result you get back... if you get back a dynamic data set, then it doesn;t know how many records there are because not everythign has been loaded... so it returns -1 because it shouldn't report 0 as that would be inaccurate, but it can't be postitive about the number either. Best bet is to use EOF or EOF&BOF ... Or, if the cursor type allows it, you "jiggle" the recordset with a .MoveLast and then .MoveFirst... that will force the recordset to finish loading and then .RecordCount will be correct. BUT, if it's a large recordset, this is highly inefficient.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    gibra
    Guest

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by DEXWERX View Post
    you can, but that would require the entire recordset to be loaded.
    You could force it if you really want using.
    Code:
    rstUsers.MoveLast
    ADO doesn't require to use MoveLast (this was used with old DAO).

    RecordCount property become available only if set the CursorLocation property to adUseClient.
    If CursorLocation is not set, then the default value used is adUseServer, in this case RecordCount return -1.

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Yep the recordcount looks to be the issue given the code there is no need for it nor is there a need to use the open static mode. a basic forward only mode would be the fastest and suitable here.

    This block
    Code:
    If rstUsers.RecordCount >= 1 Then
    llUserAuthorized = True
    Else
    lluserAuthorized = False
    End If
    could be changed to
    Code:
    llUserAuthorized = Not  rstUsers.EOF

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by DataMiser View Post
    Yep the recordcount looks to be the issue given the code there is no need for it nor is there a need to use the open static mode. a basic forward only mode would be the fastest and suitable here.

    This block
    Code:
    If rstUsers.RecordCount >= 1 Then
    llUserAuthorized = True
    Else
    lluserAuthorized = False
    End If
    could be changed to
    Code:
    llUserAuthorized = Not  rstUsers.EOF
    The ADO-Recordsets RecordCount-Property works just fine as an indicator for
    "this Rs contains Records", when the comments techgnome has made,
    are taken into account:

    In case it does not contain any records (no matter, what cursortype it is based on):
    - Rs.RecordCount will always be 0

    So the code of the OP could be changed also this way:
    Code:
    If rstUsers.RecordCount Then
       llUserAuthorized = True
    Else
       llUserAuthorized = False
    End If
    or alternatively (explicitely testing for Zero):
    Code:
    If rstUsers.RecordCount = 0 Then 'Rs does not contain any records
       llUserAuthorized = False
    Else
       llUserAuthorized = True
    End If
    In my opinion this reads somewhat nicer, compared to using EOF/BOF tests
    (which are more thought for navigation-loops).

    Olaf

  11. #11
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Using recordcount does require a client side cursor be set but yes it will work if done correctly. I prefer using .EOF, works under all conditions.

    If I were to use the recordcount though then I would still use a single line of code rather than an if else block
    Code:
    llUserAuthorized=rstUsers.RecordCount>0
    imo recordcount should only be used when you actually need to know the actual number of records that are returned. If you simply want to know if there are records or not then .eof makes more sense and would be faster in many cases.

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    Quote Originally Posted by DataMiser View Post
    Using recordcount does require a client side cursor be set ...
    No, as already stated - Rs.Recordcount will always contain a Value <> 0, when the Rs "does contain Records" -
    no matter if the Rs was retrieved with adUseClient or adUseServer...

    Quote Originally Posted by DataMiser View Post
    If I were to use the recordcount though then I would still use a single line of code rather than an if else block
    Code:
    llUserAuthorized=rstUsers.RecordCount>0
    The above code is still wrongly written...
    Better (because working also for serverside Cursors) would be:
    Code:
    llUserAuthorized= Not rstUsers.RecordCount = 0
    Or:
    Code:
    llUserAuthorized= IIf(rstUsers.RecordCount, True, False)
    Quote Originally Posted by DataMiser View Post
    If you simply want to know if there are records or not then .eof makes more sense and would be faster in many cases.
    No, it would neither be faster - nor does it make "more sense" ...
    EOF = True means, that the internal Rs-Navigation-Pointer cannot move "down any further" -
    it is not directly related to an "the-Rs-does-contain-Records"-state (whilst Rs.RecordCount is).

    Therefore using Rs.EOF is also more error-prone - because it can return True although the Rs *does* contain records.

    Olaf

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program

    I guess you are entitled to your opinion but I have to disagree. Eof is not error prone if you know how to use it. When you open a recordset if eof is true then there are no records period. if there are records then it is false and the engine does not need to count all the records that are there so obviously it would be faster if the resulting recordset has lots of records in it and it works properly with all cursor types where as recordcount will return -1 in some cases. I am not sure if it would return 0 when the recordset is empty and recordcount is unsupported. I would expect a -1 there as well which of course would make it unreliable.

Posting Permissions

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



Click Here to Expand Forum to Full Width