Solved

ASP mySQL RecordSet paging not working return -1

Posted on 2004-07-30
25
604 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now