Welcome to WebmasterWorld Guest from 34.204.203.142

Forum Moderators: ocean10000

Message Too Old, No Replies

ASP - Reading data out of SQL bug!? Crazy.

bug reading data out of sql columns

     
10:59 am on Jan 15, 2009 (gmt 0)

New User

10+ Year Member

joined:Aug 21, 2005
posts:23
votes: 0


Hi all,

Ive just been done by the craziest bug ive come across in my 3 years of coding ASP websites and im wondering if anyone knows of whats caused my issue (its taken me 4 hours or so to figure the damn thing out!)....

Basically i have a stored proc calling from an asp page, which returns the dataset into a recordset called ors. this then loops through the data and concatenates a string with it..basic stuff.

The problem i had was that almost half of the data coming from the columns was blank! I could manaually run the stored proc on the SQL and see the data was there and correct, i could also see that half my data was showing as expected.
I knew there was no errors with my code as i never use error resume next.

Basically my bug has boiled down to the fact that i have to access each column of data in the exact order that it is output in the SQL...

so if the SQL responds Col1,Col2,Col3 i have to access that data in the exact same order!

Trying to write out Col3&" "&Col2&" "&Col1 would result in only the Col2 data being wrote to the screen as the other 2 are out of order.

Anyone ever come across this before or know what cocktail is needed to trigger the bug? Its really infuriated me and feel i need to know the cause of the issue heh. I cant find any info on this.

some other facts are using ors(0) instead of ors("col1") makes no difference. Dumping the data into a variable or array makes no difference.

The server runs Win Server 2003 R2 - SP2 with SQL Server 2005.

Cheers for reading my rant :-D

Again this isnt a cry for help as its all figured out - i just want to know what causes this :(

2:39 pm on Jan 15, 2009 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:864
votes: 3


This might have to do with ADO version of asp you are using. I last seen this behavior 7+ years ago, when dealing with Binary Blobs and TEXT data field types. The bug was it did a separate fetch for each blob type as they where requested, but something was wrong in the ado code where it would fail if you didn't fetch the blob data type last. This may or may not be the problem.
8:56 pm on Jan 16, 2009 (gmt 0)

New User

10+ Year Member

joined:Oct 20, 2008
posts:9
votes: 0


Curious, how did you fix it? Can you post the code by chance?
9:04 pm on Jan 16, 2009 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:864
votes: 3


How I fixed it was coding practices on my end when I knew I would be dealing with Blob types (text,image). I solved it by reading the other fields first, before I access the fields which contained the blob data types for that row in the record. And that is how I worked around it then.

Basically boiled down to reading all the small fields first then the big ones last.

9:53 am on Jan 26, 2009 (gmt 0)

New User

10+ Year Member

joined:Aug 21, 2005
posts:23
votes: 0


does varchar(max) count as a blob type? i didnt think it did? - i have no text or binary columns in my table and am still having to combat this issue none stop.

On a side note, my ADO version is 2.8 - you can google that statement to get the code to check ur version.

This is still driving me nuts! :(:(

7:56 pm on Jan 26, 2009 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:864
votes: 3


Yes I would think it is treated as a blob type, since in theory it can return a vary large amount of text.