need help with stored procedure

  • 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!

  • 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>

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply