Solved

ASP mySQL RecordSet paging not working return -1

Posted on 2004-07-30
25
603 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

746 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

12 Experts available now in Live!

Get 1:1 Help Now