Wayne Barron
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
This is the main page.
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
(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%>
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>
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
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.
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?
do you need access?
ASKER
Yes, I need both, as I stated in the OP and in the Title.
access version is
where
X = PageSize
Y = (Page+1)*PageSize
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
where
X = PageSize
Y = (Page+1)*PageSize
ASKER
I created a table in SQL Server 2012, and I ran your code.
This is my output.
There seems to be a lot of errors in the code you provides.
Double SELECT SELECT and WHERE when there is no value.
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()
ASKER
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
ASKER
The one you provided.
And I also added in where id=10
However, either way, results in the same error.
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
ASKER
sql, version
dont see any issue with access after putting numbers for page=3 pagesize=10
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
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
missed a "," at the end of 1st line
sql server
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
ASKER
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.
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
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
ASKER
I tried the SQL Server code on the page, and it does not return any records.
ASKER
Within SQL Server, I get 4 records back.
rm = 9, 19, 29, 39
But nothing on the actual page.
rm = 9, 19, 29, 39
But nothing on the actual page.
SQL
or
sql server uses / not % for division
119 / 10 = 11
125 / 10 = 12
130 / 10 = 13
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
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
sql server uses / not % for division
119 / 10 = 11
125 / 10 = 12
130 / 10 = 13
ASKER
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.
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
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
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)
is it returning what you need?
are you passing correct values?
what is actual query are you running from the page (response.write)
ASKER
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
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?
ASKER
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 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
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")
ASKER
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.
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...
lemme check what you put into code...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Got it.
So, in the JS portion of the page, it needs to be.
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
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.
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
ASKER
@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.
It gives an error
Incorrect syntax near 10
How can I test-run this in SSMS?
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
It gives an error
Incorrect syntax near 10
How can I test-run this in SSMS?
ASKER
Got it. Had to wrap it.
top (2 * 10)
ASKER
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
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.
Would there be a limitation with the original, when dealing with multiple tables connected together?
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
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
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
ASKER
I tried to run this in SSMS
, with top (@PageSize * @Page)
, with top (5 * 5)
, with top (@PageSize * @Page)
, with top (5 * 5)
Incorrect syntax near 'with'
what did you try in ssms
on the one you asked above
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) ) s ORDER BY IDOne DESC )
so ORDER BY IDOne
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
ASKER
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
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
ASKER
>> you are missing order
That could have been it???
That could have been it???
ASKER
Yep, that was it.
It was missing the order.
I placed that in, and it went to working.
Have a good one, Huseyin.
Wayne
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
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
this query will give you same result + rn column and you can just filter by desired page
>>> so your query will be
Open in new window
no command parameter needed...