Member Avatar for DorsetBloke

Hi all,

I have searched high and low for this answer but turned up nothing concrete.

I am using MS SQL Server and ASP.

I have a database with a table which includes a field with a TEXT datatype.

I have created a simple stored procedure to select all the fields in the table into a recordset.

I have been able to output these on a web page using ASP, EXCEPT the TEXT field named "Content".

Can anyone tell me how I output the field to the web page?

The stored procedure:

CREATE PROCEDURE gettherecords
       @KeyPhrase varchar(100)
AS
BEGIN
  SELECT * From TheTable WHERE KeyPhrase = @KeyPhrase
END

ASP code:

Set rsResults = Server.CreateObject("ADODB.recordset")

rsResults.open "Exec gettherecords '" & varKeyPhrase & "'", connect

response.write rsResults("Content")

As I say, the other fields output fine...


Thanks for your help, chaps.

  • 4 Contributors
  • 7 Replies
  • 377 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by tamora

Recommended Answers

All 7 Replies

Member Avatar for sknake

Is you recordset using an oledb or odbc connection? I think odbc in classic asp has issues with text/ntext data types.

Member Avatar for DorsetBloke

Not sure what you mean. Here's the database connection code if that helps:

set connect = server.CreateObject ("ADODB.connection")

I have no problem usually reading the Content field from the database. It is only now I am trying stored procedures that the issue has arisen.

Thanks for your reply.

Member Avatar for sknake

What is the error then? I don't understand what you need help with. Is the SP blowing up?

Member Avatar for DorsetBloke

There is no error that I can see on screen.

The problem I have is that all the other fields in the table (integer, varchar, decimal) are output fine into HTML, using ASP to retrieve the data through the stored procedure into a recordset; however the one field "Content" that is datatype TEXT does not output.

Therefore I am assuming that the TEXT datatype cannot be read by a stored procedure but I thought I'd ask you guys for any guidance or confirmation.

Thanks.

Member Avatar for sknake

No .. the problem here lies within ASP. A store procedure can handle any datatype. The "text" datatype can be used for large amounts of data and binary data so it is typically treated differently than normal string data. ex: you can't call Len(TextField) in MSSQL, you have to call DataLength(TextField) . But as far as answering your question I have no idea. You should probably ask that in a classic ASP forum which I don't think daniweb has :(

Sorry I couldn't be of more help.

Member Avatar for NextCom

Classic ASP and text/ntext is pain, but if your running SQL 2005 then you can use varchar(max) insted, i have not experienced any problems i had with ntext after i switched over to varchar(max).

If your handling datatype TEXT then you should drop using it with the trigger, and change your ASP file to:

Set rsResults = Server.CreateObject("ADODB.recordset")
rsResults.open "SELECT * From TheTable WHERE KeyPhrase='" & varKeyPhrase & "'", connect
response.write rsResults("Content")

Just for fun you could try this in your ASP file (think this is the work-around):

lContent = rsResults("Content")
response.write lContent
Member Avatar for tamora

Hi DorsetBloke,
Not sure if this is still a problem for you, but here is my workaround. In the select statement make sure you specify all the fields you wish to retrieve and make sure the last one in the list is the TEXT field. This should get around the problem.
Tamora (Also a Dorset Bloke!)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.