|
 |
asp_databases thread: Getting more than one record from DB
Message #1 by "Hasenfratz, Philipp" <maillist@e...> on Mon, 23 Jul 2001 15:14:53 +0200
|
|
Hello List,
The following script works, but only if the result of the DB is only one
record. Strange. If I do a query like "SELECT count(*) AS '1' FROM ..."
it works, because the DB returns only one record ( "1" =3D> "8935"). But
if I do a query like "SELECT * FROM ..." which returns more than one
record, my script returns a "DBEnd"-String which means that (EOF and
BOF) is True (see GetRow).
Can someone help me : How to get more than one record????
Any help welcome
regards
---Philipp
<%
Option Explicit
Dim OurDB, DBResults, DBAlreadyGotRow, DBEnd
Sub OpenDB
Set OurDB =3D Server.CreateObject("ADODB.Connection")
OurDB.ConnectionString =3D "dsn=3DDotTV;database=3DDotTV;"
OurDB.Open
Set DBResults =3D Server.CreateObject("ADODB.Recordset")
End Sub
Sub SQL(sqlQuery)
Set DBResults =3D OurDB.Execute(sqlQuery)
DBAlreadyGotRow =3D False
End Sub
Sub GetRow
If DBAlreadyGotRow =3D True Then
DBResults.MoveNext
DBResults.Open
Else
' Nothing got yet =3D> Don't load new record
DBAlreadyGotRow =3D True
'DBResults.MoveFirst ' causes an error "80004005", "driver doesn't
support this parameter"
End If
DBEnd =3D DBResults.EOF and DBResults.BOF
End Sub
Function GetRowData(rowName)
if DBEnd =3D True Then
GetRowData =3D "DBEnd"
Else
GetRowData =3D DBResults(rowName)
End If
End Function
Sub CloseDB
DBResults.Close
Set DBResults =3D Nothing
OurDB.Close
Set OurDB =3D Nothing
End Sub
%>
Message #2 by Steve Carter <Steve.Carter@t...> on Mon, 23 Jul 2001 14:41:51 +0100
|
|
Hard to tell what you are trying to achieve here. I have a few suggestions,
interspersed with the code below. Also, a couple of points
1) When you open a recordset, you only need to do it once for the whole set
of results, not for each row.
2) If you get to the beginning of your recordset then rs.BOF is set, if you
get to the end then rs.EOF is set. The only time BOF and EOF are both set
is when there are no records.
Here's how I read a recordset:
dim oConn
set oConn = server.CreateObject("ADODB.Connection")
dim oRs
set oRs = server.CreateObject("ADODB.Recordset")
oConn.open MyConnectionString
oRs.Open theSQL, oConn, adOpenKeyset, adLockReadOnly, adCmdText
if oRs.BOF and oRs.EOF then
' NO ROWS
' Output something firm but polite.
else
oRs.MoveFirst ' Just ter be sure.
while not oRs.EOF ' While there are more records
' Do stuff with a row here
ors.movenext ' Go to the next record, or set EOF if no more records.
wend
end if
ors.close
oconn.close
set ors = nothing
set oconn = nothing
> -----Original Message-----
> From: Hasenfratz, Philipp [mailto:maillist@e...]
> Sent: 23 July 2001 14:15
> To: ASP Databases
> Subject: [asp_databases] Getting more than one record from DB
>
>
> Hello List,
>
> The following script works, but only if the result of the DB
> is only one record. Strange. If I do a query like "SELECT
> count(*) AS '1' FROM ..." it works, because the DB returns
> only one record ( "1" => "8935"). But if I do a query like
> "SELECT * FROM ..." which returns more than one record, my
> script returns a "DBEnd"-String which means that (EOF and
> BOF) is True (see GetRow).
>
> Can someone help me : How to get more than one record????
>
> Any help welcome
>
> regards
>
> ---Philipp
>
> <%
> Option Explicit
> Dim OurDB, DBResults, DBAlreadyGotRow, DBEnd
>
> Sub OpenDB
> Set OurDB = Server.CreateObject("ADODB.Connection")
> OurDB.ConnectionString = "dsn=DotTV;database=DotTV;"
> OurDB.Open
> Set DBResults = Server.CreateObject("ADODB.Recordset")
> End Sub
>
> Sub SQL(sqlQuery)
> Set DBResults = OurDB.Execute(sqlQuery)
Put in adOpenKeySet in the options, this will allow movefirst and movenext
> DBAlreadyGotRow = False
> End Sub
>
> Sub GetRow
> If DBAlreadyGotRow = True Then
> DBResults.MoveNext
> DBResults.Open
' Delete this line. The recordset only needs opening once then you can
navigate freely
> Else
> ' Nothing got yet => Don't load new record
> DBAlreadyGotRow = True
> 'DBResults.MoveFirst ' causes an error "80004005",
> "driver doesn't support this parameter"
adOpenKeySet should help here.
> End If
> DBEnd = DBResults.EOF and DBResults.BOF
just DBEnd = DBResults.EOF
> End Sub
>
> Function GetRowData(rowName)
> if DBEnd = True Then
> GetRowData = "DBEnd"
> Else
> GetRowData = DBResults(rowName)
> End If
> End Function
>
> Sub CloseDB
> DBResults.Close
> Set DBResults = Nothing
> OurDB.Close
> Set OurDB = Nothing
> End Sub
>
> %>
>
Message #3 by kalyan_ramji@h... on Mon, 23 Jul 2001 14:47:58
|
|
Hey try using a do-while loop. Try this..
Do While Not <<RecordSetName>>.EOF
<<Code to display records>>
<<RecordSetName>>.MoveNext
Loop
Good Luck!!
> Hello List,
>
> The following script works, but only if the result of the DB is only one
> record. Strange. If I do a query like "SELECT count(*) AS '1' FROM ..."
> it works, because the DB returns only one record ( "1" =3D> "8935"). But
> if I do a query like "SELECT * FROM ..." which returns more than one
> record, my script returns a "DBEnd"-String which means that (EOF and
> BOF) is True (see GetRow).
>
> Can someone help me : How to get more than one record????
>
> Any help welcome
>
> regards
>
> ---Philipp
>
> <%
> Option Explicit
> Dim OurDB, DBResults, DBAlreadyGotRow, DBEnd
>
> Sub OpenDB
> Set OurDB =3D Server.CreateObject("ADODB.Connection")
> OurDB.ConnectionString =3D "dsn=3DDotTV;database=3DDotTV;"
> OurDB.Open
> Set DBResults =3D Server.CreateObject("ADODB.Recordset")
> End Sub
>
> Sub SQL(sqlQuery)
> Set DBResults =3D OurDB.Execute(sqlQuery)
> DBAlreadyGotRow =3D False
> End Sub
>
> Sub GetRow
> If DBAlreadyGotRow =3D True Then
> DBResults.MoveNext
> DBResults.Open
> Else
> ' Nothing got yet =3D> Don't load new record
> DBAlreadyGotRow =3D True
> 'DBResults.MoveFirst ' causes an error "80004005", "driver doesn't
> support this parameter"
> End If
> DBEnd =3D DBResults.EOF and DBResults.BOF
> End Sub
>
> Function GetRowData(rowName)
> if DBEnd =3D True Then
> GetRowData =3D "DBEnd"
> Else
> GetRowData =3D DBResults(rowName)
> End If
> End Function
>
> Sub CloseDB
> DBResults.Close
> Set DBResults =3D Nothing
> OurDB.Close
> Set OurDB =3D Nothing
> End Sub
>
> %>
>
Message #4 by "Hasenfratz, Philipp" <maillist@e...> on Mon, 23 Jul 2001 18:28:29 +0200
|
|
THX Steve and Kalyan for your help.
Till now I wasn't able to fix the problem, but I do my best. :)
>2) If you get to the beginning of your recordset then rs.BOF is set, if you
>get to the end then rs.EOF is set. The only time BOF and EOF are both set
>is when there are no records.
Good to know. It wasn't written in my docu ( the book says that it's for
advanced programmers, I can't believe, it's written for beginners, for
example it's not written what BOF means, but I thought it means
BeginOfFile).
>Here's how I read a recordset:
Yes, I know the structure. But, as you've seen in my script, I wanted to
control DBConn and SQL stuff in procedures, to simplify the programs. Sorry
for this cryptographic like code I've publicated in the mail.
Kalyan : (topic : error " ADODB.Recordset error '800a0e79' ")
>As mentioned, it could be with the locktype and/or keytype. Also, don
>forget to include the adovbs.inc file in your asp page. Typically when you
>open a record set, the options that I think is best is adOpenStatic and
>adLockOptimistic.
THX I've solved the problem.
---Philipp
Message #5 by "Ken Schaefer" <ken@a...> on Tue, 24 Jul 2001 12:17:38 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Hasenfratz, Philipp" <maillist@e...>
Subject: [asp_databases] RE: Getting more than one record from DB
: >2) If you get to the beginning of your recordset then rs.BOF is set, if
you
: >get to the end then rs.EOF is set. The only time BOF and EOF are both
set
: >is when there are no records.
:
: Good to know. It wasn't written in my docu ( the book says that it's for
: advanced programmers, I can't believe, it's written for beginners, for
: example it's not written what BOF means, but I thought it means
: BeginOfFile).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BOF does stand for "Beginning of File"
EOF stands for "End of File"
This is a throw back to flat file databases. The only time when you are at
the Beginning of the File, and the End of the File at the same time, is when
there is nothing between the beginning and the end, ie there are no records.
Cheers
Ken
|
|
|
View Cart
