asked on

ASP/ADO Cannot get RecordSet.RecordCount value

I am successfully connecting to a SQL database.

But when I attempt to access the RecordSet(rs) RecordCount property, it returns -1.

I've made sure that rs.CursorType = adOpenStatic
and I can actually access the values in the table, but I really need to get ahold of the record count and absolute page, etc to put together some navigation.

This is SQL server 2000 running (almost) the latest MDAC, 2.6. I use almost the exact same script in a different app w/ access and there's no problem.

I'm dumbfounded as to why I can't get this count.

Oh, also I tested to see if bookmarks are supported by doing this: rs.Supports(adBookmark) and it returns true!

Any help or suggestions would be greatly appreciated!

Thanks 1,000,000
Greg

here's a bit of the code....

%
SQLStmt = "SELECT * FROM GregTest"
strCon = "Provider=SQLOLEDB;Persist Security Info=False; User ID=sa;" &_
" Initial Catalog=CommandCenter;Initial File Name=E:\CommandCenter_Data.mdf;"

' Data Connection
SET con = Server.CreateObject("ADODB.Connection")
con.ConnectionTimeout = 35
con.CommandTimeout = 60
con.Open strCon
' Command Object
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = con
cmdDC.CommandText = SQLstmt
cmdDC.CommandType = 1
' Recordset
Set rs = Server.CreateObject("ADODB.Recordset")
'rs.CursorLocation = adUseClient //causes conflict error
rs.CursorType = adOpenStatic
rs.Open cmdDC

If rs.eof Then
response.write "P CLASS='result'>No records matched the search criteria. /P>
"
End If
Response.Write("Record Count = " + CStr(rs.RecordCount) + "
") 'returns -1
Response.Write("Absolute Page = " + CStr(rs.AbsolutePage) + "
") 'returns -1
Response.Write("Supports Bookmarks = " & rs.Supports(adBookmark)) 'returns true
Response.Write("Supports Curson Position = " & rs.Supports(adApproxPosition)) 'returns true
%>

jitganguly

I would just ask you to put on erro resume next and get the errors

I have a feeling it is nto even running the sqlstmt

here is the way

%
SQLStmt = "SELECT * FROM GregTest"
strCon = "Provider=SQLOLEDB;Persist Security Info=False; User ID=sa;" &_
" Initial Catalog=CommandCenter;Initial File Name=E:\CommandCenter_Data.mdf;"

' Data Connection
SET con = Server.CreateObject("ADODB.Connection")
con.ConnectionTimeout = 35
con.CommandTimeout = 60
on error resume next
con.Open strCon
if err.number<> 0 then
response.write "Problem is " & Err.description
end if
' Command Object
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = con
cmdDC.CommandText = SQLstmt
cmdDC.CommandType = 1
' Recordset
Set rs = Server.CreateObject("ADODB.Recordset")
'rs.CursorLocation = adUseClient //causes conflict error
rs.CursorType = adOpenStatic
on error resume next
rs.Open cmdDC
if err.number<> 0 then
response.write "Problem is " & Err.description
end if

If rs.eof Then
response.write "P CLASS='result'>No records matched the search criteria. /P>
"
End If
Response.Write("Record Count = " + CStr(rs.RecordCount) + "
") 'returns -1
Response.Write("Absolute Page = " + CStr(rs.AbsolutePage) + "
") 'returns -1
Response.Write("Supports Bookmarks = " & rs.Supports(adBookmark)) 'returns true
Response.Write("Supports Curson Position = " & rs.Supports(adApproxPosition)) 'returns true
%>

gtosi

ASKER

I tested that and it does not return an error.
As a matter of fact I can even get the items from the first record by doing this farther down the page...

<TABLE>
<TR>
<TD CLASS="result"><%= Trim(rs.Fields("Name")) %></TD>
<TD CLASS="result"><%= Trim(rs.Fields("Dept")) %></TD>
<TD CLASS="result"><%= Trim(rs.Fields("ID")) %></TD>
</TR>
</TABLE>

jitganguly

Hmmm,
I have never seen such a syntax

Response.Write("Record Count = " + CStr(rs.RecordCount) + "
") 'returns -1
Response.Write("Absolute Page = " + CStr(rs.AbsolutePage) + "
") 'returns -1

Change this to

Response.Write "Record Count = " & rs.RecordCount
Response.Write "Absolute Page = " & rs.AbsolutePage

gtosi

ASKER

Changed it and they still both return -1.

jitganguly

What happens if you use adOpenDynamic instead of adUseClient ?

gtosi

ASKER

same result unfortunately...

jitganguly

and if you use enumarated values ? then

rs.CursorType = 2 ' i.e. adOpenDynamic

gtosi

ASKER

same result unfortunately...

jitganguly

Hmmm,
Though you are getting data, but I would just want you to change the Connections

From
strCon = "Provider=SQLOLEDB;Persist Security Info=False; User ID=sa;" &_
" Initial Catalog=CommandCenter;Initial File Name=E:\CommandCenter_Data.mdf;"

to something like

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

and test the recordcount

gtosi

ASKER