Stored procedure and datatype TEXT (ASP)
 

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.

Re: Stored procedure and datatype TEXT (ASP)
 

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

Re: Stored procedure and datatype TEXT (ASP)
 

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.

Re: Stored procedure and datatype TEXT (ASP)
 

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

Re: Stored procedure and datatype TEXT (ASP)
 

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.

Re: Stored procedure and datatype TEXT (ASP)
 

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.

Re: Stored procedure and datatype TEXT (ASP)
 

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

Re: Stored procedure and datatype TEXT (ASP)
 

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!)