Strange behaviour using SQL Server 2000

Question

Strange behaviour using SQL Server 2000

paliz

Newbie

Points: 3
More actions
November 8, 2004 at 7:19 am

#87024

I've come up against a very wierd thing...I'm busy coding a site using ASP and SQL Server 2000 (using Dreamweaver MX 2004).
I did a test page, with just an image and some text, both drawn from the database.
----------------------------------------------------------------------------
<%=(rsProperty.Fields.Item("propertyDetails").Value)%>
<%=(rsProperty.Fields.Item("propertyPhoto1").Value) %>
----------------------------------------------------------------------------
the 'propertyDetails' datatype is set as 'text' and the 'propertyPhoto1' datatype is set as 'varchar'
Now, the above code works perfectly, with the text appearing above the photo. Now, if I move the text below the photo, like:
----------------------------------------------------------------------------
<%= (rsProperty.Fields.Item("propertyPhoto1").Value) %>
<%=(rsProperty.Fields.Item("propertyDetails").Value)%>
----------------------------------------------------------------------------
the photo appears correctly, but the text disappears!
Now, the weird thing...if I change the 'propertyDetails' datatype to 'varchar' instead of 'text', the text appears!!???
Now, why would the text appear correctly above the photo when the datatype is 'text', but not appear below the image, yet appear below the image when the datatype is changed to 'varchar'???
I'm completely stumped!!
Can anyone explain this??
Thanks,
Peter

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

It likely has to do with how ASP is trying to render the photo caption. A text datatype can be very large, and it might not be able to figure out where it ends, so it is just giving up (and not throwing an error). If you don't truly have a need for 2,147,000-odd characters in your photo caption, you should ALTER the base table to make it a varchar. If you are fairly sure that meaningful captions are only 100 characters wide, you might also CAST(Caption AS varchar(100)) to see what it renders with the caption below the photo. Let us know how it goes.

There is no "i" in team, but idiot has two.

paliz Newbie Points: 3 More actions · Answer 2

Okay, makes sense, but does not explain why the text suddenly appears when I remove the photo field...?? As I mentioned above it works fine when the text datatype is VARCHAR - what confuses me is that I have used the same datatypes on earlier websites with no problems...

Luckily the propertyDescription will not be too long, about 800 chars max...this text is not a caption - I just simplified the code above to illustrate my problem - it is all placed within a table on the page.

Thanks for your help

Peter

Jomy John Thomas Mr or Mrs. 500 Points: 550 More actions · Answer 3

Hi

Are you using "Select * from table" to open the recordset and then using

RecordSet.Fields(0),

RecordSet.Fields(1) to access the fields?

If not

How are you accessing the table?

Regards,

Jomy John Thomas

Your Guide to Admin and Design

paliz Newbie Points: 3 More actions · Answer 4

This is the recordset:

--------------------------------------------------------------

<%

Dim rsProperty__MMColParam

rsProperty__MMColParam = "1"

If (Request.QueryString("propCodeID") "") Then

rsProperty__MMColParam = Request.QueryString("propCodeID")

End If

%>

-----------------------------------------------------

Then I access the fields by using

Pretty standard stuff...that is not where the problem lies...it is with the datatypes