Move to last record and print?

Hi, Ive got a small yet annoying problem here. I have a database with a table containing user names and I want to be able to move to the last record and display the user name.....so the end result is that I can display on my main page:

"Welcome to our newest member: XXXXX"

For some reason I cant seem to do this, it says to me that objRst.MoveFrist is ok but objRst.MoveLast cannot be accepted. Any help would be very much appreciated! Here is my code:


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%

Dim objRst, objConnect, SQL_DetailsRetreival

Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT userName FROM tblUsers"

SET objRst = objConnect.execute(SQL_DetailsRetreival)

objRst.MoveFirst
objRst.MoveLast
Response.Write "Welcome to our newest member: " & objRst("userName")


objRst.Close
Set objRst = nothing
%>

The MoveLast method is called to move to the last record in the specified Recordset object.

If the Recordset does not support bookmarks and is using a forward only cursor, then an error will be generated when you call this method.

Ok, so how would I go about specifying a foward AND reverse cursor type for this recordset? Otherwise im completely stupmed as to what I shoudl do next.

Many thanks.

Well...for starters..
I'd create an extra field in your TABLE

The field would hold the date(including the time) the user was created.
NOTE: If your using SQL Server, why not create the field with a default value. The default value would be GetDate()
Now each time a new user is created, it'll insert the Date/Time

Now you can create an SQL Query that searchs for the last user created using an ORDER BY clause in your SQL Statement.

Hope this helps!
Sincerely

Vlince

there's many tutorials about how to declare record sets and bookmarks etc online, made by people far more knowledgable then me so i recommend you search if you want to find out how to do that.

take vlince for example, he actually reads what you type, while i on the other hand only scan what you type. On second notice and on me actually reading what you want, i see you are doing "newest member". I'd say it'd be a good idea to keep track of when users join just for inactive account reasons and see vlince's post as to how, this is actually the most common way of doing it too from what i've seen. And I 100% agree with Vlince, by only returning the most recent member, you'll reduce network conguestion from sending unneeded info from all the other members on your site. Generally if you can do it in the query, DO IT THAT WAY!!!

Thanks for all your help guys but I woudl still just like to know how to implement this line into my code:


objRst.CursorType = adOpenStatic


Ive tried inserting it everywhere but i keep getting the error "Operation is not allowed when the object is open."

So I try inserting it before the object is open and it says "Operation is not allowed when the object is closed."

So this leaves me kind of stuck and frustrated, all I want to do is change the cursor type from the stupid default forward only type. If anyoen could show me how to insert that small cursor change line in to the above code i posted in my first post then I wouldbe very grateful.

many thanks,
Seb

---BEGIN QUOTE---
but I woudl still just like to know how
---END QUOTE---

We know you'd still like things to work...that's why I told you to add a field called, for example, CreatedOn that would hold the date/time the user was created...

That's basic/good database design, why won't you try it?
Why won't you do this? Why do you seem reluctant to try?

All you wan is an answer, and I understand, but you're doing it the *wrong* way.
Trust me, you'll probably have other problems in the long run if you don't simply add an extra field where you can make a condition on!

Now...if you absolutely want/need an answer then here it is...
You're IMPLICITly creating a Recordset Object, that's why you get *all* the default values...

Now in order to change that, you'll need to EXPLICITly create a Recordset object.

This is what I mean by EXPLICITly declaring a recordset object:
Set objRst = Server.CreateObject("ADODB.Recordset")

Hope this helps!
Sincerely

Vlince

Thanks for your help once again Vline, but im still getting the error "Rowset does not support fetching backward."

Here is the code:


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%

Dim objRst, objConnect, SQL_DetailsRetreival

Set objConnect = Server.CreateObject("ADODB.Connection")
Set objRst = Server.CreateObject("ADODB.Recordset")

objConnect.Open objConn

SQL_DetailsRetreival = "SELECT userName FROM tblUsers"



objRst.CursorType = adOpenStatic


Set objRst = objConnect.execute(SQL_DetailsRetreival)


objRst.MoveLast

Response.Write "Welcome to our newest member: " & objRst("userName")


objRst.Close
Set objRst = nothing

%>


Surely I am using the correct cursor type?

Thanks.

ok...I've created a simple example with an Access database.

Here is the code:
------------------------------------------------------------------------------
<%
Dim strSql
Dim objConn
Dim objRst

strSql = "SELECT * FROM Users"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=C:\Inetpub\wwwroot\tmp\db1.mdb;"


'Set objRst = Server.CreateObject("ADODB.Recordset")
'objRst.CursorType = 1
'objRst.Open strSql, objConn


Set objRst = objConn.Execute(strSql)


While not objRst.EOF
'objRst.MoveLast
Response.Write objRst(1) & "<br>"
objRst.MoveNext
Wend

objRst.Close
Set objRst = nothing

objConn.Close
Set objConn = nothing
%>
------------------------------------------------------------------------------

Notice the lines in bold, they are COMMENTS
If you copy/paste this code, and change the database connection string of course(and the SQL query)
It should display the list of userName.
In my example, my database has 3 fields UserId, UserName, CreatedOn
I've manually inserted 5 records in my database for testing purposes.

Now when I do:
Response.Write objRst(1) & "<br>"

I'm actually asking for the *second* field.
Since I did a SELECT *, notice the * I'm going to ask for the *second* field which is the number 1. Like arrays, it starts at 0
so first field is 0, second field is 1'
To avoid confusion, I could've simply written
Response.Write objRst("UserName") & "<br>"
But you get the idea...

Now the example will display my 5 UserNames but is you UNCOMMENT the lines in bold it should display *only* the last one!.

The code works, it's been tested.
Make sure you make a couple of *refresh*'s on your page!!!

Oh and before I forget, if you UNCOMMENT the lines of code in bold don't forget to COMMENT this line:
Set objRst = objConn.Execute(strSql)

Since you don't want an error to happen.

Hope this helps!
Sincerely

Vlince

Vlince, you the man! Thanks for the help, I genuinely appreciate it. Now looking back, I can see the benefit of using the date system you suggested and then using an SQL query to find the latest entry.

Im trying this out at the moment by doing this:

New field in my users table called dateRegged (text field). When a user signs up, the data from date() gets inserted with the format 08/09/2003.

Could you give me some pointers on how to acheive this? Am i on the right track by doing this? I mean I could also insert the time in to the same field maybe, by using time()....but then how would i wirte an SQL query to get the latest registered user?

many thanks.
seb

Make *YOUR* life easier...

Don't code *anything* let your *database* do the work for YOU!

Example, if you're using Access, simply create a field like CreatedOn and add a default value. The field should be a Date/Time field **NOT** a text field. Then the default value should be something like: Now()

If you're using SQL Server, then create a field called CreatedOn that is a date/time filed. Give that field a default value instead of giving it Now(), like in Access, give it GetDate().

The idea behind this approach is that *EACH* time you INSERT a new record, the date/time is automatically INSERTed for you.
You don't have to worry about INSERTing it, since it'll do it automatically for you. You the programmer have NO CODE whatsoever to create since its the database that takes care of that for you.

Now all you need to do is *add* a clause inside your Query...something like:

SELECT UserName FROM Users ORDER BY CreatedOn DESC

Notice the ORDER BY clause **AND** the DESC keyword.
You could also, simply say:
SELECT TOP(1)...read up on that, the TOP keyword.

Hope this helps!
Sincerely

Vlince

Brilliant Vlince, it works perfectly! Thank you so much!