Solved

ASP mySQL RecordSet paging not working return -1

Posted on 2004-07-30
25
611 Views
Last Modified: 2008-01-16
Dear Experts:

For some reason my dbRes.PageCount, and dbRes.AbsolutePage is returning -1... why is it doing that?

Below is my code.

<% Option Explicit %>
<!-- #include virtual="/includes/conn.inc" -->
<!-- #include virtual="/includes/functions-admin.inc" -->
<!-- #include virtual="/includes/functions-general.inc" -->
<% GetConnection %>
<link rel="stylesheet" type="text/css" href="type.css">
<script language="JavaScript" type="text/JavaScript">
<!--
function pageJump(num){
      var currPage;
      if(num == 1){
            currPage = document.Set.page.value;
      }
      else{
            currPage = document.Set.page2.value;
      }
      var strLocation = "admin.asp?pg=<%= adminListSet %>&curr=" + currPage;
        window.location = strLocation;
}

function confirmDelete(id, txt) {
      conf = confirm("Are you sure you want to delete " + txt + "?");
      if (conf == true)
            window.location = "admin.asp?pg=<%= adminDeleteSet %>&id=" + id;
}
//-->
</script>
<form method="post" action="admin.asp" name="Set">
<input type="hidden" name="pg" value="<%= adminSearchSet %>">
<table border="0" cellpadding="0" cellspacing="0" width="100%" id="table1">
      <% If Session("ErrorMessage") <> "" Then %>
      <tr>
            <td width="100%" class="e_text" height="25" colspan="2">
            <table border="0" cellpadding="0" cellspacing="0" width="100%" id="table2">
                  <tr>
                        <td class="e_red" align="center" width="100%"><%= Session("ErrorMessage") %></td>
                  </tr>
            </table>
            </td>
      </tr>
      <%
                  Session("ErrorMessage") = ""
            End If
      %>
      <tr>
            <td width="100%" class="e_head" valign="top" height="15" colspan="2">Set Search</td>
      </tr>
      <tr>
            <td width="50%" class="e_text" valign="middle" height="25">
            <input type="text" name="search" size="25" class="e_text">
            <input type="submit" name="submit" value="Search" class="e_text" align="absbottom"></td>
            <td width="50%" class="e_text" valign="middle" height="25" align="right">
            <input type="button" value="Add New" name="New" class="e_text" onClick="window.location='admin.asp?pg=<%= adminAddSet %>'"></td>
      </tr>
      <tr>
            <td width="100%" class="e_head" valign="top" height="10" colspan="2">
            <img border="0" src="../images/spacer.gif" width="1" height="10"></td>
      </tr>
      <tr>
            <td width="100%" class="e_head" valign="top" colspan="2">Set Address</td>
      </tr>
      <tr>
            <td width="100%" class="e_text" colspan="2">
            <table border="0" cellpadding="0" cellspacing="0" width="100%" id="table5">
                  <%
                        Dim dbRes, SQL, currPage, nextPage, prevPage, totalPage, rowNum
                    
                        rowNum = GetDetailPerPage
                        SQL = "SELECT * FROM set_detail"
                        Set dbRes = Server.CreateObject("ADODB.RecordSet")
                           dbRes.Open SQL, dbConn, 2, 3
                           dbRes.PageSize = rowNum
                           totalPage = dbRes.PageCount
                           currPage = Request("curr")
                           If IsNumeric(currPage) Then
                                 currPage = Int(currPage)
                        Else
                                 currPage = 1
                        End If
                        If totalpage < 1 Then
                              totalpage = 1
                        End if
                        If currPage < 1 Then
                            currPage = 1
                        End If
                        If currPage = totalPage Then
                            nextpage = totalPage
                        End If
                        If currPage = 1 Then
                            prevPage = 1
                        Else
                            prevPage = currPage - 1
                        End If
                        If currPage = totalPage Then
                            nextPage = totalPage
                        Else
                            nextPage = currPage + 1
                        End If
                  %>
                  <tr class="e_text">
                        <td width="100%" height="25" colspan="6" align="right" class="e_text">
                        Goto
                        <select size="1" name="page" class="e_text" onChange="pageJump(1);">
                        <%
                              Dim i
                              For i = 1 to totalPage
                        %>      
                        <option value="<%= i %>" <% If currPage = i Then %>selected<% End If %>>Page <%= i %></option>
                        <%
                              Next
                        %>
                        </select></td>
                  </tr>
                  <tr class="e_text">
                        <td width="30%" height="25">Set Name</td>
                        <td width="25%" height="25">Set Folder</td>
                        <td width="20%" height="25">Set Date</td>
                        <td width="5%" height="25" align="center">Images</td>
                        <td width="10%" height="25"><%= dbRes.PageSize %></td>
                        <td width="10%" height="25"><%= dbRes.AbsolutePage & " " & dbres.PageCount %></td>
                  </tr>
                  <%      
                        Dim set_id, set_name
                      While Not dbRes.EOF AND Int(dbRes.AbsolutePage) <> Int(currPage)
                            set_id = dbRes("set_id")
                            set_name = dbRes("set_name")
                  %>
                  <tr class="e_text" onmouseout="ChangeBack('#FFFFFF')" onmouseover="ChangeTo('#F3F3F3')">
                        <td width="30%" height="20"><%= set_name %></td>
                        <td width="25%" height="20"><%= dbRes("set_folder") %></td>
                        <td width="20%" height="20"><%= dbRes("set_date") %></td>
                        <td width="5%" height="20" align="center">
                        <%  %></td>
                        <td width="10%" height="20" align="right">
                        <a href="admin.asp?pg=<%= adminEditSet %>&id=<%= set_id %>">
                        <img border="0" src="../images/but_edit.gif" width="20" height="16"></a></td>
                        <td width="10%" height="20" align="right">
                        <a href="javascript:confirmDelete('<%= set_id %>', '<%= set_name %>');">
                        <img border="0" src="../images/but_delete.gif" width="15" height="16"></a></td>
                  </tr>
                  <%
                              dbRes.MoveNext
                        WEnd
                        dbRes.Close
                        Set dbRes = Nothing
                  %>
                  <tr class="e_text">
                        <td width="100%" height="25" colspan="6" align="right" class="e_text">
                        &nbsp;</td>
                  </tr>
                  <tr class="e_text">
                        <td width="100%" height="25" colspan="6" align="right" class="e_text">
                        Goto
                        <select size="1" name="page2" class="e_text" onChange="pageJump(2);">
                        <%
                              For i = 1 to totalPage
                        %>      
                        <option value="<%= i %>" <% If currPage = i Then %>selected<% End If %>>Page <%= i %></option>
                        <%
                              Next
                        %>
                        </select></td>
                  </tr>
            </table>
            </td>
      </tr>
      </table>
</form>
0
Comment
Question by:RickDai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 11
25 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 150 total points
ID: 11679034
dbRes.Open SQL, dbConn, 3,2
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679176
when a pageSize is specified to be 5
dbRes.PageSize = 5

I tried that. It is still returning
Response.Write dbRes.PageSize  ' 5
Response.Write dbRes.RecordCount ' -1
Response.Write dbRes.AbsolutePage  ' -1
Response.Write dbres.PageCount ' -1

strange, why are they -1????

also now when i dont set the pageSize
Response.Write dbRes.PageSize  ' -1
Response.Write dbRes.RecordCount ' 10
Response.Write dbRes.AbsolutePage  ' -1
Response.Write dbres.PageCount ' -1

what is goin on, what am i missing?
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679248
Try this, exact code I use for paging, connected to MySQL

...
        rowNum = GetDetailPerPage
      RS.CursorLocation = 3
      RS.Open "select * from tickets where " & sql & " ORDER BY ticket_ID DESC",connStr , , 2
      RS.CacheSize = session("pagesize")
        dbRes.PageSize = rowNum
      totalPage = dbRes.PageCount
...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11679269
If I could chime in to support GaryC123 here:

RS.CursorLocation = 3

should fix it. I have had to make similar changes to paging code to make it work on MySQL. So, I think his post above is the solution here.

FtB
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679287
Ermmm an example with your select statement and not mine... :-/

...
     rowNum = GetDetailPerPage
     RS.CursorLocation = 3
     RS.Open "SELECT * FROM set_detail",connStr , , 2
     RS.CacheSize = rownum
     dbRes.PageSize = rowNum
     totalPage = dbRes.PageCount
...
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679540
Thanks guys, but i am getting this error:

Microsoft Cursor Engine error '80004005'
Data provider or other service returned an E_FAIL status.

/admin/set_list.asp, line 75


line 75' totalPage = dbRes.PageCount

any ideas?
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679547
my current code:
rowNum = GetDetailPerPage
                        SQL = "SELECT * FROM set_detail"
                        Set dbRes = Server.CreateObject("ADODB.RecordSet")
                        dbRes.CursorLocation = 3
                           dbRes.Open SQL, dbConn, , 2
                           dbRes.CacheSize = rownum
                      dbRes.PageSize = rowNum
                           totalPage = dbRes.PageCount
                           currPage = Request("curr")
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679573
I dont know if this is relevent but here is my connection code:

Sub GetConnection()
     If Not IsObject("dbConn") Then
         Set dbConn = Server.CreateObject("ADODB.Connection")
         dbConn.ConnectionTimeout = 15
         dbConn.CommandTimeout =  10
         dbConn.Mode = 3 'adModeReadWrite
         If dbConn.state = 0 Then
             dbConn.Open strConnectionString
         End If
     End If
End Sub

thanks 'fritz ;)
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679586
Ermm whats your connection string.
That error is rather generic.
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679621
Still whats your actual connection string.  
And I don't think you need this line, I certainly don't use it for anything
dbConn.Mode = 3 'adModeReadWrite

But Fritz may correct me...
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679673
const strConnectionString = "DSN=mydatabase;"
0
 
LVL 2

Author Comment

by:RickDai
ID: 11679865
More information as follows:
I've noticed if I use
adUseServer i get -1 for most Recordset Properties
adUseClient I get the E_Fail status....
0
 
LVL 58

Expert Comment

by:Gary
ID: 11679926
Ok just try this
dbRes.Open SQL, "DSN=mydatabase;", , 2
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680029
im sorry, how is that relevant??
It gave me the same error.
0
 
LVL 58

Expert Comment

by:Gary
ID: 11680147
Then I'm at a loss, all I can say is the dsn connected properly.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680227
garyC thank you for your time.
This is my first attempt to actually use full recordset paging,
i usually make a function to go through the recordset and increment count to get the RecordCount.... but that takes up too much resources when goin through over 10000 records.

im gonna actually try to redo this page without hTML to see if i missed something.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680353
on another note does the datatype in the database have any affect on this?

set_id     --- int - auto increment   - primary key
set_name --- text
set_description --- text   - allow null
set_category   --- int
set_section --- tinyint
set_date --- date
set_lastview --- date   - allow null

0
 
LVL 58

Expert Comment

by:Gary
ID: 11680381
Not at all, you're counting records, it doesn't matter what field types are being pulled back.
You could always use count(field) to get the number of records, but if the pagecount isnt working then this probably wont either.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680452
SUCCESS!!!!

it was the data types after all. i noticed that on my server , for some unknown reason, the ALLOW NULLS were not set. causing the errors.

Thank you all for your time.

Seems to me the E_FAIL errors pop up if a field is NULL and you did not allow it to be a null field.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680471
actually count field works, i did
"SELECT COUNT(*) AS count FROM set_detail"
dbRes("count") returns the correct value.

but anyways, thank you. -=D

A new learned lesson, data types and its parameters must be correct, and it usually causes dumb errors like these.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11680527
This is actually a question for the mods, what do experts do with all their expert points??
I mean they're only for status ranking right?

Why did expert-exchange not have it where if you answer questions, you get to use those expert points if you need to ask a question... i thought that would make sense...
0
 
LVL 58

Expert Comment

by:Gary
ID: 11680528
Well I probably didnt deserve to get the points in that case, but I've never come across that problem before and I've been working with MySQL for a while now.
0
 
LVL 58

Expert Comment

by:Gary
ID: 11680561
Thats how it works, if you get 10,000 points a month from answering questions you have unlimited points with which to ask questions, and of course the status thing in the hall of fame.
0
 
LVL 2

Author Comment

by:RickDai
ID: 11681551
10k points a month??? im assuming if you have 10k points but not a month, you do not get that benefit?
0
 
LVL 58

Expert Comment

by:Gary
ID: 11683375
Yep, but its very easy to get 10k, I can usually get that in a day, think about it 5 * 500 questions with grade A gives you 10k, if a question is asked for 500 points and they award an A then you get 2000 points, B gives you 1500 points and C gives you 1000 points, so its quite easy to achieve really
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question