Problem with 'text' data type in SQL Server

[webboy1]

Hi,

I am having a problem with columns in my Database which are data type 'text'.

I am basically trying to pull info from the DB to an ASP page. I have done this loads of times, but this is the first time i have used Data type 'Text'.

The reason i am using text is because i am wanting to allow users to enter quite a bit of text.

The problem i am having is this:

When running the site off my local copy of SQL Server, all works fine. All data is inserted and selected from the Database without a problem. However, since putting the Database onto the the live SQL Server on our host server, all works, except columns of data type 'text'. For some reason, the info from these columns is not displayed on my ASP page, although every other column is.

Has anyone experienced this before?

Any help/advice is greatly appreciated!

Cheers
Webboy

[Digga]

Can you post your code?

[jamie]

hi webboy,

might it be something as silly as having the variable between the <textarea>$variable</textarea> tags, as opposed to as a value <textarea value=$variable>?

i just had this recently... and thought i'd better go back to html classes ;-)

[sullen]

I've experienced this before, and there is an issue with the text datatype in SQL Server.

On some of our projects, we found that it doesn't seem to be able to return two text variables (I know you say you can't get any at all, but it does seem to be related).

Our workaround is to have a separate SQL lookup for every text field, and to use VARCHAR 8000 instead wherever possible.

[webboy1]

The code i have used to pull the info is:

==================================================
<table width="230" border="0" cellspacing="0" cellpadding="0">
<%While not rs.eof%>
<tr>
<td><span class="bodyBOLD">
<% =rs("name") %> from <% =rs("fromwhere")%>
</span></td>
</tr>
<tr>
<td><span class="date"><% =rs("date_entered") %>
</span></td>
</tr>
<tr>
<td><span class="body"><% =rs("comments") %>
</span></td>
</tr>
<tr>
<td height="15"></td>
</tr>
<%
rs.MoveNext
wend %>

</table>
======================================================

All <% =rs()%> work apart from the <% =rs("comments") %>, which is the column with data type 'text'. I have tried varchar, but it seems to limit what people can enter.

What i am trying to create is a mini comment forum.....almost like an online football phone-in type thingy. Which means i cannot really judge how long peoples comments will be.

Surely this has been done before? any ideas how i can fix it?

Again, all help appreciated.

Regards
Webboy

[Digga]

Hi,

this may help:

[support.microsoft.com...]

And I normally use the VARCHAR 8000 too.

[webboy1]

I have set the column to Varchar 8000 and it seems to have worked. I will look into all the other offerings, but this at least allows me to continue building the site!

Cheers
Webboy

[musicales]

webboy1 I've come across this problem too. The solution is to position the text field at the right place in the query - sounds stupid but it works. It's either at the end, or the beginning (I think the end)
so if you've got three field

product_id
product_url
product_description

and the description is the text field then do

select product_id,product_url,product_description from products

Also, don't use select * because that will include the description somewhere in the middle.

Hope that works for you!

[duckhunter]

I think the end

musicales is right. That fixes it. We've had this problem before and that corrects it everytime.

Select numberfield, datefield, textfield from table1....

[davegerard]

I think musicales is right. I ran into the same thing and for some reason you need to keep the text fields at the end of your select statement. You may also want to try assigning a variable to the text value right after you start your loop. In other words...

rs.open sql, cn
if not rs.eof then
do while not rs.eof

Comment = rs("Comment")

----other stuff here----

rs.movenext
loop
rs.close

so on and so forth...