ASP and ADO Gotcha - Duplicate Field Names in a Query

,

ASP / ADO Gotcha - Duplicate Field Names in a

Query

Recently while working on the Sql Server Central site, I

ran into a problem that amazed me.  Why was I amazed you ask?  Well I

really didn't expect what happened was posible, but sure enough it

did.  I was trying to display the results of the votes for a

particular book.  The field that contains the value is Books.Rating. 

Thus in my display code, I simply typed something like

<H3><%=rsart("BookName")%>  Rating:

<%=rsart("rating")%></H3>. Much to

my dismay, I kept comming up with the wrong

rating.  I checked the database table directly and sure enough the value was

correct.  After some quality debug time this is what I discovered.  ADO will allow

you to use duplicate field names in a query, and when you try

to access the value using the fieldname as the index such as in:

rsart("rating"), ADO will return the first occurance.  I've got to say,

I really would have expected an error but sure enough it works this way! 

 

strSQL="SELECT P.ArticleID,P.Articletype, P.BookID," & _

"P.Headline, P.Rating, P.NumberVotes, " & _

"P.NumberReads, P.PrintableUrl, CONVERT(char(10), " & _

"P.StartDt,101) AS StartDt, A.FirstName, " & _

"A.LastName,A.AuthorFolder, A.ColumnistFg, " & _

"B.Rating,B.BookName,B.ISBN, B.BookPhotoPath FROM P " & _

"INNER JOIN A ON P.AuthorID = A.AuthorID INNER JOIN B " & _

"ON P.BookID = B.BookID WHERE (P.ArticleID = 176)"

rsart.open strSQL,myConn

Rate

Share

Share

Rate