Vista - Rec set navigation

Again guys and gals, please forgive my ignorance…. I’m traveling the
learning curve relative to this ‘newer’ age (I’ve been stuck in a cave), and
trying to absorb VBScript, Java Script, HTML, XHTML, and all things web. OK,
I’ve been in the cave a long time. That said, I’m looking at an asp page
using a DSN-less connection to SQL Server.
Here’s basically what I’ve done:
Const STR_CONN1 = "Provider=SQLOLEDB;User ID=mister_msql_dbo…..
Set conn = CreateObject("ADODB.Connection")
conn.Open STR_CONN1
set AuthorsRS=conn.execute("Select * From Authors")
I’m connecting but a bit confused about what’s happening. I display the
first record. I can display the second or whatever with rec.movenext or
rec.move 5. What I’m trying to do is navigate the record set without adding
any data commands to the global.asa or using the scripting object model. How
do I navigate, create buttons to move first, last, next, last in this record
set without the scripting object model?
Visual Inetrdev 6.0
SQL Server 2000
Thanks,
Mark

"Mark" wrote:
Not sure how you *could* use global.asa to any advantage here. And I sure
don't know what you mean by the "scripting object model", other than the ADO
objects you are already using.

But never mind...

What kind of navigation do you want to achieve and how?

Do you want your web browser user to be able to see (say) 10 records per
page and then be able to view any other group of 10 records within the full
number of available records? If so, that's called "paging" or "recordset
paging" and a quick google on those terms (be sure to throw in "ASP" and
maybe "VBScript" or you'll get PHP and JSP and who knows what answers) will
get you more reading material than you'll ever need.

With SQL Server, you have several choices on how to do paging: You can let
ADO do the work for you or you can use a Stored Procedure that will deliver
exactly the "page" you want. ADO is simpler. The Stored Proc solution is
much more efficient. If you are just playing around for now, use ADO.

Note that to do most of the useful paging work, you'll have to open your
recordset in a different way. Using
set AuthorsRS=conn.execute("Select * From Authors")
is the most efficient way to get a recordset, but it's inflexible. Doesn't
allow some of the more useful features.

So in place of that line, do this:
Set AuthorsRS = Server.CreateObject("ADODB.Recordset")
' should always use an ORDER BY of some kind to ensure repeatability
SQL = "SELECT * FROM Authors ORDER BY lastname, firstname"
AuthorsRS.Open SQL, conn, 3 ' 3 is "adOpenStatic"

NOW your code can do things such as
RecordCount = AuthorsRS.RecordCount
AuthorsRS.AbsolutePosition = 187 ' start reading from record 187
or
AuthorsRS.PageSize = 10
PageCount = AuthorsRS.PageCount
AuthorsRS.AbsolutePage = 7 ' would get records number 61 through 70

And more. But that should get you started.

"Mark" wrote:
By the by, your post *seemed* to indicate you were creating ASP pages, so
you should use Server.CreateObject instead of just CreateObject. And I used
that in my example code.

But if you are writing standalone ".VBS" files, just change my
Server.CreateObject back to CreateObject.

Thank you pedant sir,
I assume that what I was doing was only getting one record per record set.
The global.asa and other ‘wizards’ are part of Visual Interdev. It’s the grab
the object you want and drag it onto the page (Tools, Design Time Controls).
This works fine and advances records properly but the connection string is
defined in the global.asa. With this technique, all the records are available
through the buttons in the navigation bar. I have no idea what this is doing
on the server though. With this, the scripting object model is required and
places the following in the page:

<% ' VI 6.0 Scripting Object Model Enabled %>
<!--#include file="_ScriptLibrary/pm.asp"-->
<% if StartPageProcessing() Then Response.End() %>
<FORM name=thisForm METHOD=post>VI 6.0 Scripting Object Model EnabledVI 6.0
Scripting Object Model EnabledTop of Form

I don’t want to be limited with record set size. Maybe I must? But without
using the above mentioned method to avoid record set size, a stored procedure
which brings only the desired record is where I’m headed. This investigation
will eventually lead to ‘ownership’ of some complex asp pages which at this
point seem a bit overwhelming. I’m just trying to get my footing. Teachers
such as yourself are indispensable.
Thank you.
Mark

"Mark" wrote:
Have never used (or even seen) it. (Been writing ASP code for 9 years &
spent 3 years at MS in VB.NET group...so you can see how popular it is, even
at MS.)
You aren't. Recordsets are always limited only by the number of records
available from the DB.

Dunno where you got any other impression.

Even when you do paging, as I showed you, and do something like
RS.AbsolutePosition = 189 ' moves to the 189th record
you can *still* do
Do Until RS.EOF
... process one record ...
RS.MoveNext
Loop
and if there are 13,481 records in that database query, you'll go to the
13,481st of them!

You can do some "tricks" in ADO to restrict the number of records grabbed in
one transfer (e.g., RS.MaxRecords = 17) but even that is overridable.

Now, if you really only want one record at a time (as you seemed to imply
when you wrote:
a stored procedure which brings only the
desired record is where I’m headed
I'm not even sure why you are worried about "recordset size".

SO... If there are limitations in that scripting object model that you
don't like, just don't use it. I'm sure 90% or more of what it is doing can
be duplicated with a couple of lines of HTML code and a few lines of VBScript.

************

You are going to hate me for saying this, but...

You are really wasting your time learning ASP. ASP is obsolescent and
rapidly dying. One popular ASP forum that used to get 250 to 300 posts per
day is down to maybe 10 a day. Very few companies are doing original work in
ASP.

I really feel that if you are trying to learn the Web and trying to learn
new technology that you should abandon ASP and move to ASP.NET. The tools
are many times better and the online documentation (and especially tutorials)
are so much better that it's hard to find words to describe the differences.
I really would STRONGLY suggest that you visit
http://msdn.microsoft.com/Express
and start by downloading the Visual Web Developer Express pacakage. And
then just explore around on that site and on http://www.asp.net and you will
be overwhelmed with wonderful learning materials.

Still, if you are determined to learn ASP, there are a bunch of us obsolete
old-timers around.


Maybe I must? But without

Thanks again.
I understand the asp issue. I work for the State of California and don’t
have the luxury of defining the technology for a project. I’m working with
antiquated systems that have grown into a hodgepodge of technologies and
consequently dysfunctional, cost intensive problems. As you might expect, the
government has its own ways and methods. You might have seen local Cal news
where state workers here are threatened with $6:15/hr because the legislature
cannot pass a budget. To say we work at a disadvantage really is an
understatement.

I understand the future is asp.net or .net in general. We are trying to move
to C# but are consistently about 4 or 5 years behind the curve. Most of the
menagerie I’m working with is COBOL and IDMS. I really don’t like the
mainframe. I mean REALLY don’t like it.

"The use of COBOL cripples the mind; its teaching should, therefore, be
regarded as a criminal offense". My simple mind prefers the object oriented
approach to programming logic. I’m getting too old to enthusiastically
embrace radical departures from my comfort zone.

But then again as I have said, these choices are not mine to make. I
appreciate these forums because they have helped me enormously through the
last several years. Expertise such as yours is a life saver to many of us.
Thanks for all your efforts.
Mark

Old Pedant wrote:Not necessary. I stopped using Server.CreateObject years ago.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

"Bob Barrows [MVP]" wrote:
I have to admit that my use of ASP is colored by the fact that I worked for
ChiliSoft developing ASP for Linux and Unix (and IBM mainframes), and since
we didn't allow running VBS standalone, the only thing that worked was
Server.CreateObject.

When I moved over to PC-based ASP (basically, when I jumped from Sun to MS)
I got to learn VBS 5, but never really bothered to learn the difference in
IIS vs. our own proprietary Apache plugin emulation thereof.