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
