April 8, 2010 at 8:14 pm
I have a stored procedure that Im calling via classic ASP. For some reason, when called, its only returning 30 or so records even though I know there are over 250 records in the database.
Here is my code:
Dim objCn, objRs
Set objCn = Server.CreateObject ("AdoDB.Connection")
objCn.Open dbLocation
set objRs = server.createObject("AdoDB.recordset")
objCmd.ActiveConnection = objCn
objCmd.CommandType = 4
objCmd.CommandText = "sp_GetAllRecords"
Set objRs = Server.CreateObject("ADODB.RecordSet")
objRs.CursorLocation = 2
objRs.CursorType = 1
objRs.Open objCmd
If objRs.BOF Or objRs.EOF Then
Response.Write "No Records Found."
End If
do while not objRs.EOF And not objRs.BOF
Response.Write objRs("Name")
Response.Write objRs("Title")
Response.Write objRs("Number")
Response.Write objRs("Location")
objRs.MoveNext
Loop
objRs.Close
objCn.Close
Am I doing somewrong? Perhaps Im using the wrong cursor type?
Thanks!
April 8, 2010 at 10:19 pm
What a nice trip back through memory lane. You just made me dig back through oooold code to find this ;-). Yes kids, way back when, I actually used a language called ASP. No no, not .NET, still don't know anything about that...
In any case, this is how I always used to do it. I threw in 3 different ways to pull in the fields there too. Hope this is helpful, but I was never all that advanced at ASP, so some of this could be wrong.
That adovbs.inc file is a standard include file. If you don't have it, you probably just need to replace adOpenForwardOnly, adLockReadOnly with 3,3. (The include file lets you use the names instead of the numbers)
That On Error Resume Next I remember throwing in so that it wouldn't error on a blank record set... but then I realized there were better ways to do it and all that, I just don't have those better ways handy at the moment.
<head>
<!-- #include file="adovbs.inc" -->
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.CursorLocation = adUseServer
conn.CommandTimeout=120
conn.ConnectionTimeout=120
conn.open "YourDSN"
sql = "EXECUTE dbo.yourquery @Param='" & Request.QueryString("YourParam") & "'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, adOpenForwardOnly, adLockReadOnly
</head>
<body>
On Error Resume Next
rs.MoveFirst
Do while not rs.eof
%>
First Field:<%=rs("Field1")%>
Second Field:<%=Server.HTMLEncode("Field2")%>
Third Field:<% Response.Write rs("Field3") %>
<%
rs.MoveNext
loop
rs.close
%>
</body>
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply