Link to home
Start Free TrialLog in
Avatar of Wayne Barron
Wayne BarronFlag for United States of America

asked on

SQL Server and Access Database (Project Codes) get the next record

Hello All;
(SQL Server, is what it will run on, for the production environment.
Access Database is what it will run in, for demonstration)

This is the code.
The query is sent through Jquery, to this page.
Page = 10
Start = -10

The issue is, the database ID's are never in sequence of
1,2,3,4,5
It is more like
1,4,9,12,63,109,523,1503, ect...

How can I make the select statement below, pick up on the next record in line, if the start page is nowhere close to the next record to display?

If the START = 50
and the next record in the database is 74,
It will not move to the next record, until the START = 70
<!--#include file="ACN.asp"-->
<%
page = request("page")
start = (page-1)*10
'response.Write start

Set sqlStaff = CreateObject("ADODB.Command")
sqlStaff.ActiveConnection=objConn
sqlStaff.Prepared = true
sqlStaff.commandtext = "SELECT top 10 id, LoadTitle, LoadURL, LoadText, DateDiff('s',LoadDate,Now()) AS timestamps, LoadDate FROM LoadIt where ID >=? GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate ORDER BY ID ASC " ' Set the limit of 10 records, change to suite your needs
sqlStaff.Parameters.Append sqlStaff.CreateParameter("@id", 3, 1, , start)
set rsProCom = sqlStaff.execute
%>
<ul class="page_result">
<%
while not rsProCom.eof
if not rsProCom.eof then
LoadURL = rsProCom("LoadURL")
ID = rsProCom("ID")
Title = rsProCom("LoadTitle")%>
<li id="item_<%=ID%>"><%=ID%>. <span class="page_name"><%=Title%></span><span class="page_message"><%=LoadURL%></span></li>
<%
end if
%>


    <%rsProCom.movenext
	wend%>
    </ul>
    <%
	rsProCom.close
	objconn.close%>
	<%End If%>

Open in new window


This is the main page.
<!--#include file="ACN.asp"-->
<%
Set sqlStaff = CreateObject("ADODB.Command")
sqlStaff.ActiveConnection=objConn
sqlStaff.Prepared = true
sqlStaff.commandtext = "SELECT count(LoadIt.ID) as ctIDS FROM LoadIt"
set rsProCom = sqlStaff.execute
If NOT rsProCom.EOF Then
ctIDS = rsProCom("ctIDS")
rsProCom.close
objConn.close
per_page = 10' //rows per page
end if
%>
<script type="text/javascript" src="1/js/jquery-1.11.2.min.js"></script>
<script type="text/javascript" src="1/js/jquery.bootpag.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
	$("#results").load("fetch_pages.asp");  //initial page number to load
	$(".pagination").bootpag({
	   total: <%=ctIDS%>,
	   page: 1,
	   maxVisible: <%=per_page%> 
	}).on("page", function(e, num){
		e.preventDefault();
		$("#results").prepend('<div class="loading-indication"><img src="ajax-loader.gif" /> Loading...</div>');
		$("#results").load("fetch_pages.asp", {'page':num});
	});

});
</script>
<link href="1/css/style.css" rel="stylesheet" type="text/css">
</head>
<body>
<div id="results"></div>
<div class="pagination"></div>

Open in new window



Any ideas on this one?
I have been searching for the better part of 5 hours, trying different methods, and thought I had, then I found out I was back in the same place as before.

Carrzkiss
Avatar of HainKurt
HainKurt
Flag of Canada image

first add a rownum to the query

with t as (your query here - no top, no order)
s as (select select t.*, row_num() over (order by id) rn)
select s.* from s where , (rn % 10) + 1 = @page

Open in new window


this query will give you same result + rn column and you can just filter by desired page

>>> so your query will be

sqlStaff.commandtext = "with t as (SELECT id, LoadTitle, LoadURL, LoadText, DateDiff('s',LoadDate,Now()) AS timestamps, LoadDate FROM LoadIt where GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate)
s as (select select t.*, row_num() over (order by id) rn)
select s.* from s where (rn % 10) + 1 = " & @page

Open in new window


no command parameter needed...
Avatar of Wayne Barron

ASKER

Now, is this code cross platform?
Will it work on both SQL Server and Access Database, or if this database specific?

As I am getting an error while running this under Access Database.

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
sql server...

do you need access?
Yes, I need both, as I stated in the OP and in the Title.
access version is
SELECT *
FROM (
    SELECT Top X *
    FROM (
        SELECT TOP Y id, LoadTitle, LoadURL, LoadText, DateDiff('s',LoadDate,Now()) AS timestamps, LoadDate
          FROM LoadIt
         GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate
         ORDER BY ID
    ) s
   ORDER BY ID DESC
) so
ORDER BY ID

Open in new window


where

X = PageSize
Y = (Page+1)*PageSize
I created a table in SQL Server 2012, and I ran your code.
This is my output.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 's'.
Msg 195, Level 15, State 10, Line 2
'row_num' is not a recognized built-in function name.


There seems to be a lot of errors in the code you provides.
Double SELECT SELECT and WHERE when there is no value.
use row_number()
The access codes.

The select statement includes a reserved word or an argument name that is misspelled of missing, or the punctuation is incorrect.
what is exact query you tried
The one you provided.
And I also added in    where id=10
However, either way, results in the same error.
SELECT *
FROM (
    SELECT Top X *
    FROM (
        SELECT TOP Y id, LoadTitle, LoadURL, LoadText, DateDiff('s',LoadDate,Now()) AS timestamps, LoadDate
          FROM LoadIt where id=10
         GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate
         ORDER BY ID
    ) s
   ORDER BY ID DESC
) so
ORDER BY ID

Open in new window

The SQL Server code.
(Cut it down for easy read)
with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle)
s as (select t.*, row_number() over (order by id) rn)
select s.* from s where (rn % 10) + 1 =10

Open in new window



Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 's'.
User generated image
sql, version

with t as (SELECT id, LoadTitle, LoadURL, LoadText, DateDiff(s,LoadDate, getdate()) AS timestamps, LoadDate FROM LoadIt GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate),
s as (select t.*, row_number() over (order by id) rn)
select s.* from s where (rn % 10) + 1 = @page

Open in new window


dont see any issue with access after putting numbers for page=3 pagesize=10

SELECT *
FROM (
    SELECT Top (10) *
    FROM (
        SELECT TOP (30) id, LoadTitle, LoadURL, LoadText, DateDiff('s',LoadDate,Now()) AS timestamps, LoadDate
          FROM LoadIt where id=10
         GROUP BY ID, LoadTitle, LoadURL, LoadText, LoadDate
         ORDER BY ID
    ) s
   ORDER BY ID DESC
) so
ORDER BY ID

Open in new window

missed a "," at the end of 1st line

sql server
with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle),
s as (select t.*, row_number() over (order by id) rn)
select s.* from s where (rn % 10) + 1 =10

Open in new window

How are you running the Access code, for it to work?
I am testing it inside of Access database.
Do I need to test this inside of the page itself, as I am getting the same error in access?

And, for SQL Server.
I am testing it out in SSMS, and it is giving the following errors.

with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle),
s as (select t.*, row_number() over (order by id) rn)
select s.* from s where (rn % 10) + 1 =10

Open in new window



Msg 107, Level 15, State 1, Line 2
The column prefix 't' does not match with a table name or alias name used in the query.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'id'.
Msg 8155, Level 16, State 2, Line 2
No column name was specified for column 1 of 's'.
oops, where is from?

with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle),
s as (select t.*, row_number() over (order by id) rn from t)
select s.* from s where (rn % 10) + 1 =10

Open in new window

I tried the SQL Server code on the page, and it does not return any records.
Within SQL Server, I get 4 records back.
rm = 9, 19, 29, 39
But nothing on the actual page.
SQL

with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle),
s as (select t.*, row_number() over (order by id) rn from t)
select s.* from s where (rn / @PageSize) + 1 = @Page

Open in new window


or

with t as (SELECT id, LoadTitle FROM LoadIt GROUP BY ID, LoadTitle),
s as (select t.*, row_number() over (order by id) rn from t)
select s.* from s where rn between @PageSize*(@Page-1)+1 and @PageSize*@Page

Open in new window


sql server uses / not % for division

119 / 10 = 11
125 / 10 = 12
130 / 10 = 13
It runs, just is not returning any records.
And no errors.

So, I am uncertain how to run your code.

The
@PageSize
I am assuming is the number of records to return, correct?
So, in this case, would be: @PageSize = 10

The is foreign to me, although, I would love to learn how to use this on my page.
@PageSize*(@Page-1)+1 and @PageSize*@Page

Open in new window

How does this work, inside of a select statement of a page?
if you want page 5, and page size is 10
then you actually need records from 41 and 50

ie

(10*(5-1))+1  and 10*5
so what query are you using and where
is it returning what you need?

are you passing correct values?

what is actual query are you running from the page (response.write)
Here is the demo project.
Usually, I include these when I am trying to get assistance.
I was about the fall asleep when I opened this thread.
www.cffcs.com/stuff/load.zip
so whats the problem now?
That same issue, I just provided the demo of the project using the code I supplied.
I could not get your Access code to work.
And the SQL Server code, (Which is implemented in the project, commented out), does not return any records.

Right now, if you run the project, it will give you the first few sets, then once you get to 40
It will not populate the next set until you hit 60.
(the number is calculating at the top of the page.)
i need to install ace driver lol

https://www.microsoft.com/en-ca/download/details.aspx?id=13255

and it works fine I guess
it shows 10 rec first
then paging works
until page 15 I guess
then no records since your total records is not more than 150
use the new updated driver and this is connection string

objConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & _
Server.MapPath ("load.mdb")

Open in new window

Nope, it does not work with the code that is in the file I posted.

Run the page.
It SHOULD load the first 10 from the start.
Then click on
2, 3, 4
3, 4 and 5
are the same records.
Then you click on
6 etc..
And you get the correct records.


This is what it is doing right now.
Page    ---   Records
1          ---     1-10
2          ---     10-46
3          ---     41-50
4          ---     41-50
5          ---     41-50
6          ---     50-59
7          ---     60-69
etc

As you can see, 3, 4, 5
Are all the same records.
exactly what you describe :)

lemme check what you put into code...
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to go to bed, and then wake up to realize that the code for ACCESS also works for SQL.
I thought it was getting too many records, but come to find out the index.asp page was getting the wrong amount of records.

In the JS,
total:
This needs to be the total of PAGES not the Total of RECORDS.

For example.
If there are 125 records, then it should be 13 pages. (with 5 records on the last page)
I am doing a division, and it is giving me a 12.5 so I am only getting 12 pages.
Just need to round to the next number up, and this will be done.

Thank you, Huseyin.
You Rock my Friend.
Have an awesome coming up week.

Wayne
Got it.
So, in the JS portion of the page, it needs to be.
ctIDS = rsProCom("ctIDS") ' Number of records in database
per_page = 10' //rows per page
TotalPages =  ctIDS / per_page ' Divide to get the number of pages

total: <%=FormatNumber(TotalPages,0) + 1%>, // This will round it to the next number UP.

Open in new window


Even though the last page, still has 10 records on it, duplicating some from the previous page.
It is not too bad, I doubt anyone will raise a fuss about it.

All is good.
This is done.

Wayne
@Huseyin

I am working with the code in SQL Server, in my actual page now.
And it runs, initially, but does not retrieve the records when I click on the paging numbers.
When I load the Processing page, itself. It does not page through when I change the query page=2, page=3 etc...

I loaded the query into SSMS, and I cannot get this to work.

top " & page*pagesize & "
-- to --
top 1*10

Open in new window


It gives an error
Incorrect syntax near 10

How can I test-run this in SSMS?
Got it. Had to wrap it.
top (2 * 10)

Open in new window

OK, have a question for you.
Are there any limitations, that you can think of when dealing with this SQL statement?

This is the code that runs, without flaw.
You can cycle through the records, until the end
SELECT * FROM ( SELECT Top 5 * FROM (SELECT DISTINCT TOP (1 * 5) ThreadID, MedName 
FROM Threads where askid=1 GROUP BY ThreadID, MedName ORDER BY ThreadID ) s 
ORDER BY ThreadID DESC ) so ORDER BY ThreadID

Open in new window


And this is the code, that I am running that is not getting the updated records.
(Example of the structure)
If you change the 1 to 2 to 3 etc...
It stays on the first set of records.

SELECT * FROM ( SELECT Top 5 * FROM (SELECT DISTINCT top (1 * 5) Table1.IDOne, 
Table1.MedName FROM Table2 INNER JOIN Table1 ON Table2.IDOne = Table1.IDOne 
INNER JOIN Table4 ON Table1.IDFour = Table4.IDFour INNER JOIN Table3 ON 
Table4.IDThree = Table3.IDThree WHERE (Table2.AskID = 1) ) s ORDER BY IDOne DESC ) 
so ORDER BY IDOne

Open in new window


Would there be a limitation with the original, when dealing with multiple tables connected together?
still you are here :)
the main structure is this

SELECT * FROM (
  SELECT Top @PageSize * FROM (
   your select query here ordered by ColumnX, with top (@PageSize * @Page)
  ) s ORDER BY ColumnX DESC
) so ORDER BY ColumnX

Open in new window

I tried to run this in SSMS
, with top (@PageSize * @Page)
, with top (5 * 5)
Incorrect syntax near 'with'
what did you try in ssms
on the one you asked above
SELECT * FROM ( SELECT Top 5 * FROM (SELECT DISTINCT top (1 * 5) Table1.IDOne, 
Table1.MedName FROM Table2 INNER JOIN Table1 ON Table2.IDOne = Table1.IDOne 
INNER JOIN Table4 ON Table1.IDFour = Table4.IDFour INNER JOIN Table3 ON 
Table4.IDThree = Table3.IDThree WHERE (Table2.AskID = 1) ) s ORDER BY IDOne DESC ) 
so ORDER BY IDOne

Open in new window


you are missing order

SELECT * FROM ( SELECT Top 5 * FROM (SELECT DISTINCT top (1 * 5) Table1.IDOne, 
Table1.MedName FROM Table2 INNER JOIN Table1 ON Table2.IDOne = Table1.IDOne 
INNER JOIN Table4 ON Table1.IDFour = Table4.IDFour INNER JOIN Table3 ON 
Table4.IDThree = Table3.IDThree WHERE (Table2.AskID = 1) order by IDOne) s ORDER BY IDOne DESC ) 
so ORDER BY IDOne

Open in new window

I got it.
I am not sure what was happening, but for some reason, it did not work earlier.
So, I stripped out the SELECT Statement and started over again.
This time it worked.

I think we are done with this one now.
Thanks once again.
Wayne
>> you are missing order
That could have been it???
Yep, that was it.
It was missing the order.
I placed that in, and it went to working.

Have a good one, Huseyin.
Wayne
that query is for access, works for sql as well but not good as the one here

for sql I gave better options before

declare @page int = 5
declare @pagesize int = 10

with t as
--(your select query here without order)
select * from (
select *, row_number() over (order by id) rn 
  from t
) x where rn between (@page-1)*@pagesize +1 and @page*@pagesize

Open in new window