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

[Newsgroup_User]

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" <webforumsuser@macromedia.com> wrote in message
news:gn3niv$m7c$1@forums.macromedia.com...
> 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
>

[Newsgroup_User · Accepted solution]

captcashew wrote:
> 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.

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

[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

[Newsgroup_User]

captcashew wrote:
> 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.

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

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

[Newsgroup_User]

Dooza wrote:
> captcashew wrote:
>> 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.
>
> 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

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.

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

[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

[Newsgroup_User]

captcashew wrote:
> 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!

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

Good luck!

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

[Newsgroup_User]

captcashew wrote:
> 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
>
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