-
Aug 5th, 2016, 12:40 PM
#1
Thread Starter
Member
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
-
Aug 5th, 2016, 12:47 PM
#2
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.
-
Aug 5th, 2016, 01:04 PM
#3
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by cecilchamp
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 ?
-
Aug 5th, 2016, 01:10 PM
#4
Thread Starter
Member
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.
-
Aug 5th, 2016, 01:11 PM
#5
Thread Starter
Member
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.
-
Aug 5th, 2016, 01:22 PM
#6
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by cecilchamp
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.
-
Aug 5th, 2016, 02:54 PM
#7
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by cecilchamp
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.
Originally Posted by DEXWERX
you can, but that would require the entire recordset to be loaded.
You could force it if you really want using.
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
-
Aug 5th, 2016, 05:03 PM
#8
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by DEXWERX
you can, but that would require the entire recordset to be loaded.
You could force it if you really want using.
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.
-
Aug 5th, 2016, 08:19 PM
#9
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
-
Aug 7th, 2016, 10:22 AM
#10
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by DataMiser
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
-
Aug 7th, 2016, 11:21 AM
#11
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.
-
Aug 7th, 2016, 02:59 PM
#12
Re: SQL Statement produces a record in SSMS Query Analyzer, but not in VB program
Originally Posted by DataMiser
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...
Originally Posted by DataMiser
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)
Originally Posted by DataMiser
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
-
Aug 7th, 2016, 05:22 PM
#13
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|