|
 |
access_asp thread: recordset and Movelast property
Message #1 by richhern35@h... on Tue, 26 Feb 2002 14:56:15
|
|
I am building a basic program in which I need to get just the first record
lower than the beginning date(begdate) in an access database. I just need
the record in the database that is lower that the beginning date.
I query the database with a select statement and try to use the recordset
property movelast to get only the last record in the recordset but i get
an error telling me "Rowset does not support fetching backward"
(everything else is working. here is the little peace of code:
<%
Option Explicit
Dim strConnect
%>
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -
->
<HTML>
<HEAD>
<TITLE>Connection</TITLE>
</HEAD>
<BODY>
<%
Dim objCommand, objRS,begdate,enddate
begdate=#4/12/2002#
enddate=#5/15/2002#
response.write begdate & " This is the beginning date<p>"
response.write enddate & " This is the ending date<p>"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inetpub\wwwroot\laquercia\laquercia\prices.mdb;Persist Security
Info=False"
objCommand.CommandText = "SELECT * FROM prices WHERE
prices.GiornoIniziale <= #" & begdate& "#"
objCommand.CommandType = adCmdText
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = adOpenStatic
Set objRS = objCommand.Execute
Set objCommand = Nothing
objRS.MoveLast '*****************In this line I am getting the error
message************************
Response.Write objRS("Descrizione") & " GiornoIniziale " & objRS
("GiornoIniziale") & "<BR>"
'Descrizione means description and GiornoIniziale is begining date in
the access database
objRS.Close
Set objRS = Nothing
%>
</BODY>
</HTML>
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 27 Feb 2002 12:00:35 +1100
|
|
If you do:
Set objRS = objCommand.Execute
-or-
Set objRS = objConn.Execute
then you are opening a recordset with the default cursor
(adOpenForwardOnly). It doesn't matter what properties you've set before. As
soon as you write Set objRS you are setting objRS to reference what
objCommand.Execute returns.
To do what you want, you need to explicitly open the recordset:
objRS.Open strSQL, objConn, adOpenStatic, adOpenKeyset, adCmdText
That said, a *better* way to do this would be to just limit what comes back
in the recordset in the first place.
SELECT TOP 1 Field1, Field1, Field3
FROM table1
WHERE DateField < #YourDateHere#
ORDER BY DateField DESC
will return only 1 record - the record with the date just before the date
you specify.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <richhern35@h...>
Subject: [access_asp] recordset and Movelast property
: I am building a basic program in which I need to get just the first record
: lower than the beginning date(begdate) in an access database. I just need
: the record in the database that is lower that the beginning date.
: I query the database with a select statement and try to use the recordset
: property movelast to get only the last record in the recordset but i get
: an error telling me "Rowset does not support fetching backward"
: (everything else is working. here is the little peace of code:
:
:
: <%
: Option Explicit
: Dim strConnect
: %>
: <!-- METADATA TYPE="typelib"
: FILE="C:\Program Files\Common
Files\System\ado\msado15.dll" -
: ->
:
: <HTML>
: <HEAD>
: <TITLE>Connection</TITLE>
: </HEAD>
: <BODY>
:
: <%
:
:
:
: Dim objCommand, objRS,begdate,enddate
: begdate=#4/12/2002#
: enddate=#5/15/2002#
: response.write begdate & " This is the beginning date<p>"
: response.write enddate & " This is the ending date<p>"
:
: Set objCommand = Server.CreateObject("ADODB.Command")
:
: objCommand.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
: Source=C:\Inetpub\wwwroot\laquercia\laquercia\prices.mdb;Persist Security
: Info=False"
: objCommand.CommandText = "SELECT * FROM prices WHERE
: prices.GiornoIniziale <= #" & begdate& "#"
: objCommand.CommandType = adCmdText
:
: Set objRS = Server.CreateObject("ADODB.Recordset")
: objRS.CursorType = adOpenStatic
: Set objRS = objCommand.Execute
: Set objCommand = Nothing
: objRS.MoveLast '*****************In this line I am getting the error
: message************************
:
:
: Response.Write objRS("Descrizione") & " GiornoIniziale " & objRS
: ("GiornoIniziale") & "<BR>"
: 'Descrizione means description and GiornoIniziale is begining date in
: the access database
:
: objRS.Close
: Set objRS = Nothing
: %>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
