Wot! No RecordCount??
-
I've recently moved over from MS Access, and that has a wonderful piece of code which tells me exactly how many matches there are after a Search has been executed. It's called RecordCount as in rs.RecordCount.
I now find that SQL Server 2K doesn't seem to have an equivalent command. Or if it does, I don't know what it is.
I've come across Count(*) which must have something to do with what I want, but I don't know how to extract the result.
I've created a small Stored Procedure which, when run via Query Analyser, gives me the exact answer that I'm after, but I just don't know how to extract this elusive piece of information via my ASP code, here.
sql = "EXEC sp_NoOfRecords" &_
" @VTitle='" & VTitle & "'"
Set rs = Conn.Execute(sql)
I've tried things like
rows = rs.fields(1).value, rows = rs.value
but all to no avail.
Please help and put me out of my misery. Thank you.
-
rs.recordCount is the number of records produced by the query/stored proc that you supplied to the recordset.
Just run something like this in vb :
declare connection
declare recorset
open connection
MyRs.Open "Select * from dbo.SysObjects", MyConnection, 1, 3
msgbox MyRs.RecordCount
this should pop up a value over 100 (might be less but it's definitly gonna be over 0)
-
In addition to Remi code I would ensure that either one of the below (or both are performed)
1. Make sure that the CursorLocation is set to be ADUseClient either RecordSet or Command (I honestly forget)
2. Before checking the recordcount do a MoveLast and then MoveFirst for the recordset
Why do these? If you don't you may received RecordCount = -1. This is because 1. The "cursor" that holds # records is on the server not your PC, 2. Can be found by moving to the last record and then back to the 1st (for processing)....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Nice trick... but I thaught that the RecordCount problem was due to the cursor and lock type, not the location. Do you have any readings on this.
BTW it's the recordset that must be set on ADUseClient.
-
OK guys, thanks for the quick replies, I've tried setting the CursorLocation as suggested but am now getting this error.
Operation is not allowed when the object is open.
My code now looks like this:
sql = "EXEC sp_NoOfRecords" &_
" @VTitle='" & VTitle & "'"
Set rs = Conn.Execute(sql)
rs.CursorLocation=adUseClient
Can you tell me exactly what to do (easy there
) like they do in the Dummies books. I know that once I've got the syntax and code order sorted out, I'll be able to progress, but at the moment I'm stuck here.Thanks again
-
sql = "EXEC sp_NoOfRecords" &_
" @VTitle='" & VTitle & "'"
rs.CursorLocation=adUseClient
Set rs = Conn.Execute(sql)
-
I tried that Remi but am now getting this error message:Object required: 'adUseClient'
I feel as though the adUseClient command must go after I've set the rs variable.
Here are the connection strings, just in case I've got these wrong.
Set Conn = Server.CreateObject("ADODB.Connection")
connStr = "Driver={SQL SERVER};Server=srv; UID=sa;PWD=;DATABASE=MyDBase;"
Conn.Open connStr
sql = "EXEC sp_NoOfRecords" &_
" @VTitle='" & VTitle & "'"
rs.CursorLocation=adUseClient
Set rs = Conn.Execute(sql)
Thanks for perservering
-
Here's how I open a record set that uses a stored proc
proc code :
CREATE PROCEDURE [dbo].[SPNDemo] @ObjectName as varchar(50), @id as int output
AS
SET NOCOUNT ON
SET @id = object_id(@ObjectName)
SELECT
name
, XType
FROMdbo.SysObjects
WHERE name like '%' + @ObjectName + '%'
ORDER BYXType, Name
IF @@Rowcount > 0
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
SET NOCOUNT OFF
GO
test the proc from query analyser :
Declare @id as int
Declare @return as int
exec @Return = documentation.dbo.SPNDemo 'SysObjects', @id output
--returns only 1 line
select @id as 'id is found', @return as success
exec @Return = documentation.dbo.SPNDemo 'Sys', @id
--returns a full recordset
select @id as 'no match for id', @return as success
exec @Return = documentation.dbo.SPNDemo 'This object doesn''t exists', @id
--returns a nothing
select @id as 'no match for id', @return as failed
function that returns the recordset :
Private Function exec_SPNDemo(ByVal ObjectName As String, ByRef id As Integer, Optional ByRef ReturnValue As Integer) As ADODB.Recordset
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.SPNDemo"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@ObjectName"
MyParam.Value = ObjectName
MyParam.Size = 50
MyParam.Direction = adParamInput
MyParam.Type = adVarChar
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@id"
MyParam.Value = id
MyParam.Size = 4
MyParam.Direction = adParamInputOutput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Dim MyRs As ADODB.Recordset
Set MyRs = New ADODB.Recordset
MyRs.CursorLocation = adUseClient
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyRs.Open MyCmd, , adOpenKeyset, adLockOptimistic
If MyRs.State = 1 Then
Set exec_SPNDemo = MyRs.Clone
exec_SPNDemo.ActiveConnection = Nothing
Else
Set exec_SPNDemo = Nothing
End If
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
id = MyCmd.Parameters("@id").Value
DisposeRS MyRs
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_SPNDemo", Err
MsgBox Err.Description & " : " & Err.Number
End Function
-
I'm sure you'll have a few questions after this one... Don't be afraid to ask.
-
You're right, it is a bit confusing. There seems to be a lot of presets going on here especially with the MyParam bits. Do I really need to do the same?
I've tried out a few commands, but it looks like they should be used in a VB6 environment instead of an ASP one. Which is what I am using.
I feel as though we're straying from the original question, which was, "is there a way the determine the number of records from a search of an SQL table". Maybe I need to have an adovbs.inc file!!
There has to be a way, surely! My code, below, executes a Stored Procedure that counts the number of matches there for my search. It produces one row of code where the contents of that row is the actual number of rows from the search (i.e. 512). I just need to know know to retrieve the contents of that row.
BTitle = tempTitle
VTitle = tempTitle
sql = "EXEC sp_NoOfRecords" &_
" @VTitle='" & VTitle & "'" '''
Set rs1 = Conn.Execute(sql)
response.write "No Of Records = " & rs1.fields.count & "<br>"
Produces: No Of Records = 1
Thanks anyway Remi
-
I've been told to stay away from that include file so I'll forward that to you.
here's the bit that's missing :
response.write "No Of Records = " & rs1.fields("Fieldname").value & "
"
-
Problem has been solved. It was to do with the way that I set up the rs variable.
Here's the code that works and I hope that it may help fellow beginners.
'Setting up the Recordset connection - ultra important
set rs = Server.CreateObject("ADODB.Recordset")
'Setting up the database connection
Set Conn = Server.CreateObject("ADODB.Connection")
connStr = "Driver={SQL SERVER};Server=srv; UID=sa;PWD=;DATABASE=MyDBase;"
'Opening the database ready to use.
Conn.Open connStr
'Setting up a variable called SQLtext that will be used in the next command
SQLtext = "BookTitle LIKE '%harry%potter%'"
'This is the command which selects everything from the table where BookTitle equals 'Harry Potter. Of course, this can be set to any value.
strSQL = "SELECT * FROM BookDetails WHERE " + SQLtext + " order by Author, ISBN"
'Setting the Lock Type and Cursor Type - you must have these.
rs.Open strSQL, conn, adLockReadOnly, adOpenStatic
'Printing the number of records to screen.
response.write "No Of Records = " & rs.RecordCount
'Of course, not forgetting to CLOSE everything neatly
Conn.Close
Set Conn = Nothing
Thanks to everyone for helping out, it led to the solution that I was after.
-
Thanks for that extra piece about getting the results from a stored procedure Remi. I've cut & pasted that to my Useful Code document.
Just a p.s. for the above code, I forgot to mention that this next line must be included earlier on in the program. Head section normally.
<!-- #include file="../MultiUse/adovbs.inc" -->
Cheers
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply