Hello list,



again me, again the same problem.



Description : I can't get more than one record! - If the SQL query 

returns only one record the script works, but I can't get more than one 

record.



If I try the query : "SELECT count(*)" which returns only one record, no 

problem. But If I try the query "SELECT * FROM ..." which returns more 

than one record (about 12 at the moment), the script returns : "Got an 

EOF" ( after execute the query, Recordset.EOF is True ).



Why can't I get more than one record?



---Philipp



<%

   Set OurDB =3D Server.CreateObject("ADODB.Connection")

   Set Res =3D Server.CreateObject("ADODB.Recordset")



   Const adOpenKeyset =3D 1

   Const adLockReadOnly =3D 1

   Const adCmdText =3D &H0001

   Const adBigInt =3D 20

   Const adOpenForwardOnly =3D 0

   Const adOptionUnspecified =3D -1



   OurDB.Open "dsn=3DDotTV;database=3DDotTV;User=3Droot;Pwd=3D"



   Res.Open "SELECT TSecurityKeyID AS '1' FROM SecurityKey", OurDB, 

adOpenForwardOnly, adLockReadOnly, adCmdText And adOptionUnspecified

   if Res.EOF Then

     Response.Write("Got an EOF")

   Else

    Res.MoveFirst

    while Res.EOF =3D False

      Response.Write(Res("1") & "<br>")

      Res.MoveNext

    Wend

   End If



   ' OurDB.Close

   ' Res.Close

   Set OurDB =3D Nothing

   Set Res =3D Nothing

%>

Your SQL statement is not valid:



: Res.Open "SELECT TSecurityKeyID AS '1' FROM

: SecurityKey", OurDB, adOpenForwardOnly,

: adLockReadOnly, adCmdText And adOptionUnspecified



When troubleshooting SQL statement problems, always allocate your SQL

statement to a variable. Then you can Response.Write your variable to see

exactly what you are sending to the database.



<%

strSQL = _

    "SELECT SecurityKeyID " & _

    "FROM SecurityKey"



Response.Write(strSQL)



objRS.Open strSQL,objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

%>



Cheers

Ken





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Hasenfratz, Philipp" <maillist@e...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 25, 2001 2:21 AM

Subject: [asp_databases] Can't receive more than one record





Hello list,



again me, again the same problem.



Description : I can't get more than one record! - If the SQL query returns

only one record the script works, but I can't get more than one record.



If I try the query : "SELECT count(*)" which returns only one record, no

problem. But If I try the query "SELECT * FROM ..." which returns more than

one record (about 12 at the moment), the script returns : "Got an EOF"

 after execute the query, Recordset.EOF is True ).



Why can't I get more than one record?



---Philipp



<%

   Set OurDB = Server.CreateObject("ADODB.Connection")

   Set Res = Server.CreateObject("ADODB.Recordset")



   Const adOpenKeyset = 1

   Const adLockReadOnly = 1

   Const adCmdText = &H0001

   Const adBigInt = 20

   Const adOpenForwardOnly = 0

   Const adOptionUnspecified = -1



   OurDB.Open "dsn=DotTV;database=DotTV;User=root;Pwd="



   Res.Open "SELECT TSecurityKeyID AS '1' FROM SecurityKey", OurDB,

adOpenForwardOnly, adLockReadOnly, adCmdText And adOptionUnspecified

   if Res.EOF Then

     Response.Write("Got an EOF")

   Else

    Res.MoveFirst

    while Res.EOF = False

      Response.Write(Res("1") & "<br>")

      Res.MoveNext

    Wend

   End If



   ' OurDB.Close

   ' Res.Close

   Set OurDB = Nothing

   Set Res = Nothing

%>





---

* Fast, Full-Featured Microsoft® Excel Web Reports & Charts!

A breakthrough in high performance Web application development, SoftArtisans

ExcelWriter 1.1 supports native Excel charting, image insertion, and

advanced functions & formatting. One click generates presentation-quality

Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

Excel Object. Several editions, including ExcelWriterFREE, are available.

http://www.softartisans.com/softartisans/excelwriter.html>

Hi Ken,



> Your SQL statement is not valid:



No, the SQL statement is valid! - That's not the problem I have.



> : Res.Open "SELECT TSecurityKeyID AS '1' FROM

> : SecurityKey", OurDB, adOpenForwardOnly,

> : adLockReadOnly, adCmdText And adOptionUnspecified

>

> When troubleshooting SQL statement problems, always allocate your SQL

> statement to a variable. Then you can Response.Write your variable to see

> exactly what you are sending to the database.



Thank you, but I don't want to output my SQL query ( I know that the query

is right ), I want to retreive data and that's my problem.

If the DB returns only one row ( what a query like "SELECT count(*)" does),

ADO works fine and I can retreive the data, but if the DB returns more than

one row ( what most of SELECT-commands do ) I can't get data. I haven't

changed the program and my SQL queries are right. I don't have structure

errors regarding handling the ADO-Objects.

Could it be that the IIS 5.0 DB Connection Tools fail to retreive mysql-data

correctly ( I hope not! ). Is there a "protocol-structure-missunderstanding"

?



Do someone have experience with mysql and IIS?



regards



---Philipp

> -----Original Message-----

> From: Hasenfratz, Philipp [mailto:maillist@e...]

> Sent: 25 July 2001 03:47

> To: ASP Databases

> Subject: [asp_databases] Re: Can't receive more than one record

> 

> 

> Hi Ken,

> 

> > Your SQL statement is not valid:

> 

> No, the SQL statement is valid! - That's not the problem I have.



Luke Skywalker: Your overconfidence is your weakness.

Emperor Palpatine: Your faith in your [code] is yours.



> > : Res.Open "SELECT TSecurityKeyID AS '1' FROM

> > : SecurityKey", OurDB, adOpenForwardOnly,

> > : adLockReadOnly, adCmdText And adOptionUnspecified

> >

>    Res.Open "SELECT TSecurityKeyID AS '1' FROM SecurityKey", OurDB,

Drop the "AS '1'" 



If you are trying to put a '1' in this field for every record you read, 

then you need to go "SELECT '1' AS TSecurityKeyID, * FROM SecurityKey" 

(i.e. the value on the left, name on the right)



> adOpenForwardOnly, adLockReadOnly, adCmdText And adOptionUnspecified



Drop the "And adOptionUnspecified"



(Note: If you do want multiple options, you need to say OR not AND.



>    if Res.EOF Then

>      Response.Write("Got an EOF")

>    Else

>     Res.MoveFirst

>     while Res.EOF = False

>       Response.Write(Res("1") & "<br>")



Use Res("SecurityKeyID")



>       Res.MoveNext

>     Wend

>    End If

> 





>    ' OurDB.Close

>    ' Res.Close



uncomment these, they are right, although you want to swap their order - 

close the recordset first.



>    Set OurDB = Nothing

>    Set Res = Nothing

> %>

> > Hi Ken,

> >

> > > Your SQL statement is not valid:

> >

> > No, the SQL statement is valid! - That's not the problem I have.

>

> Luke Skywalker: Your overconfidence is your weakness.

> Emperor Palpatine: Your faith in your [code] is yours.



Thank you for my psychological analysis. (you're 53.45% right)



> > > : Res.Open "SELECT TSecurityKeyID AS '1' FROM

> > > : SecurityKey", OurDB, adOpenForwardOnly,

> > > : adLockReadOnly, adCmdText And adOptionUnspecified

> > >

> >    Res.Open "SELECT TSecurityKeyID AS '1' FROM SecurityKey", OurDB,

> Drop the "AS '1'"

>

> If you are trying to put a '1' in this field for every record you read,

> then you need to go "SELECT '1' AS TSecurityKeyID, * FROM SecurityKey"

> (i.e. the value on the left, name on the right)



No, I just want to replace the columne name from "TSecurityKeyID" to "1",

because I hate writing as much. In "normal" mysql-SQL syntax that's

possible. Perhaps not regarding ADO, I don't know, that's why I'm asking.



> > adOpenForwardOnly, adLockReadOnly, adCmdText And adOptionUnspecified

>

> Drop the "And adOptionUnspecified"



done.



> (Note: If you do want multiple options, you need to say OR not AND



Oh, yes. What I am stupid. Thanks.



regards



---Philipp

Sorry, my mistake - when I read you SQL statement, the capital T in

TSecurity ran into the back of SELECT, and I mis-read it. I though you were

missing a space between SELECT and the first field name. I thought I was

seeing SELECTSecurity not SELECT TSecurity.



Apologies!



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Hasenfratz, Philipp" <maillist@e...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 25, 2001 12:46 PM

Subject: [asp_databases] Re: Can't receive more than one record





: Hi Ken,

:

: > Your SQL statement is not valid:

:

: No, the SQL statement is valid! - That's not the problem I have.

:

: > : Res.Open "SELECT TSecurityKeyID AS '1' FROM

: > : SecurityKey", OurDB, adOpenForwardOnly,

: > : adLockReadOnly, adCmdText And adOptionUnspecified

: >

: > When troubleshooting SQL statement problems, always allocate your SQL

: > statement to a variable. Then you can Response.Write your variable to

see

: > exactly what you are sending to the database.

:

: Thank you, but I don't want to output my SQL query ( I know that the query

: is right ), I want to retreive data and that's my problem.

: If the DB returns only one row ( what a query like "SELECT count(*)"

does),

: ADO works fine and I can retreive the data, but if the DB returns more

than

: one row ( what most of SELECT-commands do ) I can't get data. I haven't

: changed the program and my SQL queries are right. I don't have structure

: errors regarding handling the ADO-Objects.

: Could it be that the IIS 5.0 DB Connection Tools fail to retreive

mysql-data

: correctly ( I hope not! ). Is there a

"protocol-structure-missunderstanding"

: ?

:

: Do someone have experience with mysql and IIS?

:

: regards

:

: ---Philipp

:

:

: ---

: * Fast, Full-Featured Microsoft® Excel Web Reports & Charts!

: A breakthrough in high performance Web application development,

SoftArtisans

: ExcelWriter 1.1 supports native Excel charting, image insertion, and

: advanced functions & formatting. One click generates presentation-quality

: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

: Excel Object. Several editions, including ExcelWriterFREE, are available.

: http://www.softartisans.com/softartisans/excelwriter.html>




$subst('Email.Unsub')

> Sorry, my mistake - when I read you SQL statement, the capital T in

> TSecurity ran into the back of SELECT, and I mis-read it. I though you

were

> missing a space between SELECT and the first field name. I thought I was

> seeing SELECTSecurity not SELECT TSecurity.



Ah, I see. No problem.



regards



Philipp