Text datatype not showing using ASP/VBScript and SQL Server

[captcashew]

Maybe someone help me with this since I normally use PHP/MySQL but for this one
instance I am using Classic ASP/VBScript and MS SQL Server and I don't know
that much. The scenario is I have an old site that was designed using html
tables and I'm doing a CSS/XHTML upgrade on the visual design. As long as we
are in there, there are some pages that are dynamic and use a MS Access
database and we thought it is a good time to upgrade from that to MS SQL Server
(2005, to be exact). No time/money to upgrade from classic ASP VBScript to ASP
.NET though.

Overall, the upgrade has gone pretty well, mostly just required creating a new
connection for the SQL Server DB in Dreamweaver after all the tables and data
were migrated to SQL Server. Almost everything works as it should. One problem
though.

For some reason, data that is of the data type "text" in SQL Server isn't
showing up (or occasionally showing up). I even tried recreating recordsets and
binding the data fresh and the code is more or less identical to what we had
before. Anyone know what is going on or how I can get this data to show?

I'm thinking that maybe there are some single and double quotes in the text
data and that might be screwing things up. I was hoping there is just a simple
function I could apply to the code, but like I said, I don't know.

Thanks in advance for any help you can provide.

-Bill

[Jon Spivey]

A couple of things to try
1/ List text fields last in your sql, eg select carcharfield1,
varcharfield2, textfield from table

2/ Presumably when you upgraded you converted memo fields in Access to text
fields in SQL Server? This isn't always necessary as a SQL Server varchar
field can hold up to 5000 charcaters - so unless you know you'll need more
than this a varchar field would be a better choice than a text field.

Cheers,
Jon

"captcashew" <webfor...@macromedia.com> wrote in message
news:gn3niv$m7c$1...@forums.macromedia.com...

[Dooza]

Your text field needs to be the last one in your select statement, as
its not stored in the table with the other data, its stored elsewhere,
as a binary object, so has to be treated differently. Its a common
problem when upsizing from Access.

If you need in excess of 8000 characters stick with it, otherwise use
varchar(8000) or nvarchar(4000) if you need unicode characters (limited
to 4000 characters cos of the double byte)

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

[Dooza]

Also, something to consider if your doing updates or inserts, sql2000
can't take more than 8060 bytes, so if your combined data length of the
insert/update is great than that it will fail.

[Julian Roberts]

Aside from other suggestions, one might want to consider using
nvarchar(max) as the datatype. Text is effectively deprecated in SQL 2005

--

Julian Roberts

http://www.charon.co.uk

[captcashew]

Hey, thanks everyone! These are all the exact answers I was looking for. I'll
see what I can find, but I know that I didn't have the text data types last in
my SQL query. I also didn't realize that I could have a varchar over 255 (I'm
used to PHP/MySQL), so I think I should be fine changing it to varchar 8000, it
is a SQL Server 2005 and that seems to be the limit for that server. Even if it
isn't, I'm pretty sure 4000 would be more than enough.

I did try reordering the SQL query on the page with trouble and still didn't
work, but that was a complex query with some inner joins, so maybe that is the
cause. Either way, changing data types should do the trick.

Thanks again everyone!

-Bill

[Dooza]

With 2005/2008 you can go further with varchar(max) as David suggested.

Good luck!

[captcashew]

Maybe I am just being thick or still waking up, but I have sort of a dummy
question about unicode. Would I most likely want to use nvarchar then because
there is a possibility that some html code is in this data that has been in
memo columns in Access and now is in the text columns in SQL Server 2005?

I assume that is the reason I would most likely reason I would want to use
nvarchar instead of varchar is because of the other characters that would be in
the html that aren't supported with just varchar. Is this accurate? And could
someone show me some text that would need to be in nvarchar instead of varchar?
Just so I have it straight.

Thanks again

-Bill

[Dooza]

When you use nvarchar each character will use 2 bytes, whether it needs
it or not. Any foreign local characters, ie non english, will be double
byte, ie 2 bytes.

This might explain it:
http://geekswithblogs.net/vivek/archive/2007/05/04/112237.aspx