Text datatype not showing using ASP/VBScript and SQL Server

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

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

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
>