Solved

Database - Parsing a Recordset

Posted on 2001-09-08
44
296 Views
Last Modified: 2013-11-25
Hi All!

I have a need to query a database and break up the results into chunks of say 25 or 50 records a piece.

If the user wants to see more of the records they can select >> Next or choose the number of the page they want to jump to.  Kind of like how some web pages handle it.

I'm kind of lost as to where to start.  I know I need to get the first 25 records and then make the number links to the associated records, but ?

Any help is greatly appreciated!  I eagerly await awarding you an "A" for all of you helP  :)

Thanks!
Candis
0
Comment
Question by:Candis_Sue
  • 24
  • 10
  • 4
  • +6
44 Comments
 
LVL 2

Expert Comment

by:YourBuddyToo
ID: 6466852
Start by looking at the GetRows Method

Retrieves multiple records of a Recordset object into an array.

Syntax

array = recordset.GetRows( Rows, Start, Fields )

Return Value

Returns a Variant whose value is a two-dimensional array
0
 

Author Comment

by:Candis_Sue
ID: 6466891
I need help with the SQL statements too.  Thanks!
0
 
LVL 39

Expert Comment

by:appari
ID: 6467007
you can use pagesize, absolutepage properties of ado and loop through the records. here is the example from MSDN

Public Sub AbsolutePageX()
   
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim strMessage As String
   Dim intPage As Integer
   Dim intPageCount As Integer
   Dim intRecord As Integer

   ' Open a recordset using a client cursor
   ' for the employee table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstEmployees = New ADODB.Recordset
   ' Use client cursor to enable AbsolutePosition property.
   rstEmployees.CursorLocation = adUseClient
   rstEmployees.Open "employee", strCnn, , , adCmdTable
   
   ' Display names and hire dates, five records
   ' at a time.
   rstEmployees.PageSize = 5
   intPageCount = rstEmployees.PageCount
   For intPage = 1 To intPageCount
      rstEmployees.AbsolutePage = intPage
      strMessage = ""
      For intRecord = 1 To rstEmployees.PageSize
         strMessage = strMessage & _
            rstEmployees!fname & " " & _
            rstEmployees!lname & " " & _
            rstEmployees!hire_date & vbCr
         rstEmployees.MoveNext
         If rstEmployees.EOF Then Exit For
      Next intRecord
      MsgBox strMessage
   Next intPage
   rstEmployees.Close
End Sub

0
 
LVL 1

Expert Comment

by:Atsafrir
ID: 6467109
You can use the pagesize property of ADO but this means that you must return all the records including those that you don't need for the corrent page to be displayed.
you can use a dynamic sql statement that returns only the desiered records. i'll give you here the algorithem (the t-sql statement)that I think is the best.hope this will help you.
Test if this solution gives you more performance.

CREATE Procedure StoredProcedure1(    
     @CurrentPage int,
     @ResultsInPage int,
     @SortBy int,
     @SortOrder int,
     @SearchStr nvarchar(50),
     @NumOfResults int OUTPUT
)
As

-- Declare the local varibels
DECLARE @SQLStr nvarchar(2048)
DECLARE @LastRecord int

 
SET NOCOUNT ON

/****************************************************
The Code
****************************************************/

-- The number of records in the innner query is @LastRecord
SET @LastRecord = @ResultsInPage * @CurrentPage
-- @SQLStr hold the dynamic sql query
SET @SQLStr = ''

/*******************     inner query     *************************/

SET @SQLStr =''
-- the number of records that the inner query returns is
-- (the records to exclude)+(the number of records in page)= @LastRecord
SET @SQLStr = @SQLStr + 'SELECT TOP ' + CONVERT(nvarchar(5),@LastRecord) + '
           [your fields\columns go here ...] '
-- search criterions
SET @SQLStr = @SQLStr + 'FROM [your table\view go here ...]
                WHERE [your field]  like ''' + REPLACE(@SearchStr,'''','''''')+ '%'''

/*******************     end inner query     *************************/




/*********     select current page results     ****************/

SET @SQLStr = 'SELECT TOP ' + CONVERT(nvarchar(3),@ResultsInPage) + ' *
            FROM (' + @SQLStr + ') AS TempQ
            ORDER BY ' + CONVERT(nvarchar(2),@SortBy)


IF (@SortOrder=0)
  BEGIN
     SET @SQLStr = @SQLStr + ' DESC'
  END
ELSE
  BEGIN
     SET @SQLStr = @SQLStr + ' ASC'  
  END

/*********     end select current page results     ****************/


/*********     reorder results          ****************/
SET @SQLStr = 'SELECT *
            FROM (' + @SQLStr + ') AS TempQ
            ORDER BY ' + CONVERT(nvarchar(2),@SortBy)    

IF (@SortOrder=0)
  BEGIN
     SET @SQLStr = @SQLStr + ' ASC'
  END
ELSE
  BEGIN
     SET @SQLStr = @SQLStr + ' DESC'
  END

--PRINT @SQLStr
EXECUTE sp_executesql @SQLStr
IF (@@error <> 0) GOTO ErrorHandler

/********     end reorder results     **************/



IF (@@error <> 0) GOTO ErrorHandler





/****************** Calculate Number of pages ****************************/

SET @SQLStr =''
-- the number of records that the inner query returns is
-- (the records to exclude)+(the number of records in page)= @LastRecord
SET @SQLStr = @SQLStr + 'SELECT TOP ' + CONVERT(nvarchar(5),@LastRecord) + '
           [your fields\columns go here ...] '
-- search criterions
SET @SQLStr = @SQLStr + 'FROM [your table\view go here ...]
                WHERE [your field]  like ''' + REPLACE(@SearchStr,'''','''''')+ '%'''
     --enable rowcount
     SET NOCOUNT OFF
     EXECUTE sp_executesql @SQLStr
     SET @NumOfResults=@@ROWCOUNT
     IF (@@ERROR <> 0) GOTO ErrorHandler
     --PRINT @NumOfResults
     --disable rowcount
     SET NOCOUNT ON

  END

/****************** End Calculate Number of pages ****************************/


-- On success, return 1
RETURN 1


ErrorHandler:
-- On Error, return 0
     RETURN 0
0
 

Expert Comment

by:stuartpearson
ID: 6467171
You can use 2 TOP's, one inside a sub-select.

Ex:

SELECT TOP 20 (SELECT TOP 40 MyColumn FROM MyTable ORDER BY MyColumn ASC)
FROM MyTable
ORDER BY MyColumn DESC

I think that will work.

Good luck
0
 

Author Comment

by:Candis_Sue
ID: 6467210
I'll select the answer I want.

Thanks
0
 
LVL 17

Accepted Solution

by:
smozgur earned 100 total points
ID: 6467510
Hi,

This is not for answer (there are lots of right answer here) but this is really interesting. Yes, we use the old answers sometime by changing them depending the request of the new question but you must see this. This is really interesting :)

http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20085528


And stuartpearson, to copy and paste theold questions' links is easier than doing same thing for full answers. At least it is my idea :)

Regards.
suat



0
 
LVL 17

Expert Comment

by:smozgur
ID: 6467513
Warning again: this is not for answer. So dont loose your point to check the PAQ.

suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6467515
i mean the PAQ that i show the link in my first comment

suat
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6467931
just do your query to get the recordset.  

set a counter to count records read

do/loop until you read desired # of records.

On the next or previous event just readnext or read previous # of records you want.
0
 

Author Comment

by:Candis_Sue
ID: 6475239
This concept seems to be the best.  (I know nothing about stored procedures...)

SELECT TOP 20 (SELECT TOP 40 MyColumn FROM MyTable ORDER BY MyColumn ASC)
FROM MyTable
ORDER BY MyColumn DESC

But of course it doesn't work, and I cannot find an example anywhere on the web.  Does anyone have a version that will work with access?  Thanks!
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475290
Why doesnot it work? It should work because there is nothing wrong in that sql text (instead MyColumn and MyTable names to describe your field and table names. I am sure you changed them as your table name and field name in sql and also some changing because the comment is coming from another question's answer as it written there)

sqlstr="SELECT TOP 25 Field1Name, Field2Name FROM TableName ORDER BY SortingFieldName ASC"

This is the main idea. It is like it is written. Select first 25 records from TableName table which sorted ascending by SortingFieldName.

If you want to select records from 26 to 50 then you need 2 tops like said above:

sqlstr="SELECT TOP 25 (SELECT TOP 50 Field1Name, Field2Name FROM TableName ORDER BY SortingFieldName ASC) FROM TableName ORDER BY SortingFieldName DESC "

This says do in brackets first then out of brackets. Means: Select first 50 records (it sorted ascending by SortingFieldName) then sort this 50 records by descending (out of the brackets, second select) then select first 25 within 50 sorted ascending. You have 26 to 50.

If you have still problem please let us know the field names, sortingfieldname, table name and the records you want for each page.

Regards.
Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475294
Actually in second sql you have 50 to 26.

Suat
0
 

Author Comment

by:Candis_Sue
ID: 6475312
Here is my Query...

SELECT TOP 25 (SELECT TOP 50 Short_Desc, Category FROM tblProducts ORDER BY ProductID
ASC) FROM tblProducts Where ORDER BY ProductID DESC

This is the error I get

"You've written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM Clause.  Revise the SELECT statement of the subquery to request only one field."
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475314
I am sorry. Please try this sqlstr with explanations above.

"SELECT TOP 25 * FROM (SELECT 50 FieldName from TableName ORDER BY SortingFieldName ASC) ORDER BY SortingFieldName DESC;"

You have 50 to 26 records with this sqltext.

Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475316
Your sqlstr as corrected.

"SELECT TOP 25 * FROM (SELECT TOP 50 Short_Desc, Category FROM tblProducts ORDER BY ProductID ASC) Where ORDER BY ProductID DESC;"

It will work.

Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475326
In english;

Select last 25 records sorted descending by ProductID field from selected 50 records sorted descending by ProductId.

1- Select records 1 to 50 (ASC)
2- Sort this records as 50 to 1 (DESC)
3- Get first 25 (50,49,48,....,26)
4- This means 26 to 50 in ASC order.

Focus on the second SELECT statement (in bracket) is used as FROM clause's source.

Thats all.
Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475327
Oops; unnecessary WHERE in correction if you have no criteria: And last comment and exact one i think (because i run it in Access SQL view)

"SELECT TOP 25 * FROM (SELECT TOP 50 Short_Desc, Category FROM tblProducts ORDER BY ProductID ASC) ORDER BY ProductID DESC;"

0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475330
Waiting for result.

:)
Suat
0
 

Author Comment

by:Candis_Sue
ID: 6475346
Hi smozgur,

Now it give me the error

"Syntax error in FROM clause"

Any Ideas?  It selects the second select statement

here is the exact query
"SELECT TOP 25 * FROM (SELECT TOP 50 Short_Desc, Category FROM tblProducts ORDER BY ProductID ASC) ORDER
BY ProductID DESC;"
0
 

Author Comment

by:Candis_Sue
ID: 6475348
Anyone know if there is a keyword in access like "rownum" in oracle...?
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475351
This is mine. And it works perfect. Did you remove WHERE?

"SELECT TOP 25 * FROM (select top 50 CustNumber, policyid from m_pol_main order by policyid asc) ORDER BY m_pol_main.CustNumber;"

I used this by pasting it into SQL view in Query design.

waiting..
Suat
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Candis_Sue
ID: 6475353
What version of access do you have?  I'm using '97 currently
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475354
And my sqlstr is exactly same i wrote for yours in my last comment.

suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475360
It is not problem that the version is 97 or newer at this point. I am curently using 2000 but it should work also in 97. I will try it in my other notebook which has 97.
0
 

Author Comment

by:Candis_Sue
ID: 6475362
I've triple checked the syntax so it must be '97
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475368
It is not problem that the version is 97 or newer at this point. I am curently using 2000 but it should work also in 97. I will try it in my other notebook which has 97.
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475373
I also use that sql text within VB and it also works here.

Interesting.

0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475375
Sorry but i have to leave. I will check for 97 (even i am sure that it is not the problem)

Is it possible to send me your db file as zipped? If you can then i will wait now.

smozgur@hotmail.com

waiting...
Suat
0
 

Author Comment

by:Candis_Sue
ID: 6475383
Ok, it's on it's way.

Thanks
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475389
No problem.

I hope it is not too big file. :)
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475395
I got it.

Because you dont retrieve the ProductID in SELECT in bracket. I did this and it run in your database. Please try and let me know.

SELECT TOP 25 *
FROM (SELECT TOP 50 ProductID, Short_Desc, Category FROM tblProducts ORDER BY ProductID ASC)
ORDER BY ProductID DESC;

Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475402
And to get ASC in result query, use one more select:

SELECT * FROM (SELECT TOP 25 * FROM (SELECT TOP 50 ProductID, Short_Desc, Category FROM tblProducts ORDER BY ProductID ASC)ORDER BY ProductID DESC)  ORDER BY ProductID ASC;

It works nice.
Suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475409
Query name is SelRec in your CopyofSportsDB2 file. Sent into your email.

0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475417
Problem was sorting second query result by ProductId which isnot retrieved by first SELECT. So if you add that field into SELECT in brackets then it can order by ProductID.

Just try my last sql string above.
Clear and work? waiting and unfortunately have to go in a short time. :)

suat
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475425
working?
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6475441
I waited for your last response but i think you went before me.

Good Luck!
Suat
0
 
LVL 1

Expert Comment

by:Atsafrir
ID: 6476205
Candis_Sue I gave you a good answer if you look carefully you will see that my answer is the best one.
smozgur solution will give you the results in the wrong order + smozgur dose not know t-sql.
if you want you can only use the sql statement inside the stored procedure.
smozgur didn't test "his" answer (it dosen't work) and didn't write "his" answer.
0
 
LVL 17

Expert Comment

by:smozgur
ID: 6476461
to Atsafrir;

Of course I did test my answer and you didnot see the database file because Candis_Sue sent it to me and i created the query in database (working version). So what you mean? It is a basic access 97 database and your comment ( i am pretty sure it is really the best one ) didnot get a response from Candis_Sue until this time.

However;

"smozgur didn't test "his" answer (it dosen't work) and didn't write "his" answer. "

 is really unlucky style that you used for another helper. Question wasnot need t-sql according to me. So it is not problem that i know or not t-sql. If you want to explain your comment more please do that for questioner. Questioner would select and also response for the best answer for her/himself.

Regards.
Suat

0
 

Author Comment

by:Candis_Sue
ID: 6476844
Atsafrir,

Can you make that stored procedure code look like something I can understand?  Like I said, I do not know anything thing about stored procedures...
0
 
LVL 1

Expert Comment

by:Atsafrir
ID: 6477108
how about vb code ?


Private Function GenSQL(ByVal lngCurrentPage As Long, _
    ByVal lngResultsInPage As Long, _
    ByVal lngSortBy As Long, _
    ByVal lngSortOrder As Long, _
    ByVal strSearch As String) As String
   
    Dim strSQL As String
    Dim lngLastRecord As Long
' The number of records in the innner query is @LastRecord
lngLastRecord = lngResultsInPage * lngCurrentPage

'strSQL hold the dynamic sql query

'****************************************************
' inner query
strSQL = "SELECT TOP " & lngLastRecord & " * " & vbCrLf
'search criterions
strSQL = strSQL & " FROM [your table\view go here ...] " & vbCrLf
strSQL = strSQL & " WHERE [your field] like '" & Replace(strSearch, "'", "''") & "%'"

'end inner query
'*****************************************************

'*****************************************************
'select current page results

strSQL = "SELECT TOP " & lngResultsInPage & " * " & vbCrLf & _
          "FROM (" & strSQL & ") AS TempQ " & vbCrLf & _
          "ORDER BY " & lngSortBy

If lngSortOrder = 0 Then
    strSQL = strSQL & " DESC"
Else
    strSQL = strSQL & " ASC "
End If

'end select current page results
'*****************************************************

'*****************************************************
'reorder results

strSQL = "SELECT * " & vbCrLf & _
            "FROM (" & strSQL & ") AS TempQ " & vbCrLf & _
            "Order By " & lngSortBy

If lngSortOrder = 0 Then
    strSQL = strSQL & " ASC"
Else
    strSQL = strSQL & " DESC"
End If
GenSQL = strSQL
End Function
0
 
LVL 1

Expert Comment

by:Atsafrir
ID: 6477120
in sql server you can sort results by column number I don't know if it's possible in Access.
If it's not change the lngSortBy parameter to string and send the column Name.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7207756
Hi Candis_Sue,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: Atsafrir and smozgur

Candis_Sue, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7241091
Per recommendation, force-accepted.

Netminder
CS Moderator

Atsafrir: points for you at http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20341067.html
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 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

13 Experts available now in Live!

Get 1:1 Help Now