Weird bugs in ASP/SQL

<i><b>Originally posted by : Hamish (beolin@hotmail.com)</b></i><br />I'm finding some really dodgy behaviour using ASP to talk to an SQL database - I was wondering if anyone has run into this before... <br /><br />===============================================<br />dim MM<br />Set MM = Server.CreateObject("ADODB.Connection")<br />dim RS<br />Set RS = Server.CreateObject("ADODB.Recordset")<br />MM.Open "DSN=Something;UID=user;PWD=pass;"<br /><br />RS.Open "SELECT * FROM Items WHERE S_Deleted = 0 AND S_Date = '" & FormatDateTime(thedate, 1) & "'", MM<br />While Not RS.EOF<br /> 'begin section<br /> response.write RS("S_Title")<br /> response.write RS("S_Listing")<br /> 'end section<br /> RS.MoveNext<br />wend<br />RS.Close<br />================================================<br />Now this doesn't work. For some reason it returns Null for both fields, despite there being data in the database. Now, substitute the following for the bit between the section comments...<br /><br />================================================<br /> x = RS("S_Title")<br /> y = RS("S_Listing")<br /> response.write x<br /> response.write y<br />================================================<br />Now this actually writes out S_Title successfully, but not S_Listing (still null). At this stage I'm finding it hard to believe my eyes, but it gets worse when I did this:<br /><br />================================================<br /> y = RS("S_Listing")<br /> x = RS("S_Title")<br /> response.write x<br /> response.write y<br />================================================<br />Astoundingly, this actually works. Yes, I just changed the order of the assignments of the variables and suddenly data appears.<br /><br />So, has anyone else run into this? Is it a huge confusing bug in ASP/ODBC/SQL, or is there a patch to stop this kind of nonsense?<br /><br />Thanks<br />Hamish

<i><b>Originally posted by : Jason Bankhead (bankhj@fgwilson.com)</b></i><br /><br />It is a bug within ASP where you must referenece the columns in a table in the correct order<br /><br />If you used an SQL statement like this :<br /><br />Select Address, Name from Users<br /><br />and referenced the name before the address the address value would be returned as a null.<br /><br />In your case you were using the Select *, this just means that you have to access them in the order that they appear in the table.<br /><br />I don't know if there is a patch out to fix this, and i am amazed that more people haven't come across this really confusing bug.<br /><br />------------<br />Hamish at 12/6/2000 10:05:43 AM<br /><br />I'm finding some really dodgy behaviour using ASP to talk to an SQL database - I was wondering if anyone has run into this before... <br /><br />===============================================<br />dim MM<br />Set MM = Server.CreateObject("ADODB.Connection")<br />dim RS<br />Set RS = Server.CreateObject("ADODB.Recordset")<br />MM.Open "DSN=Something;UID=user;PWD=pass;"<br /><br />RS.Open "SELECT * FROM Items WHERE S_Deleted = 0 AND S_Date = '" & FormatDateTime(thedate, 1) & "'", MM<br />While Not RS.EOF<br /> 'begin section<br /> response.write RS("S_Title")<br /> response.write RS("S_Listing")<br /> 'end section<br /> RS.MoveNext<br />wend<br />RS.Close<br />================================================<br />Now this doesn't work. For some reason it returns Null for both fields, despite there being data in the database. Now, substitute the following for the bit between the section comments...<br /><br />================================================<br /> x = RS("S_Title")<br /> y = RS("S_Listing")<br /> response.write x<br /> response.write y<br />================================================<br />Now this actually writes out S_Title successfully, but not S_Listing (still null). At this stage I'm finding it hard to believe my eyes, but it gets worse when I did this:<br /><br />================================================<br /> y = RS("S_Listing")<br /> x = RS("S_Title")<br /> response.write x<br /> response.write y<br />================================================<br />Astoundingly, this actually works. Yes, I just changed the order of the assignments of the variables and suddenly data appears.<br /><br />So, has anyone else run into this? Is it a huge confusing bug in ASP/ODBC/SQL, or is there a patch to stop this kind of nonsense?<br /><br />Thanks<br />Hamish