Ad:

MS SQL Discussion Thread
Unsolved
Views: 1596

Jul 1st, 2009

Stored procedure and datatype TEXT (ASP)

Expand Post »

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:

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE gettherecords
       @KeyPhrase varchar(100)
AS
BEGIN
  SELECT * FROM TheTable WHERE KeyPhrase = @KeyPhrase
END
CREATE PROCEDURE gettherecords
       @KeyPhrase varchar(100)
AS
BEGIN
  SELECT * From TheTable WHERE KeyPhrase = @KeyPhrase
END

ASP code:

MS SQL Syntax (Toggle Plain Text)
SET rsResults = Server.CreateObject("ADODB.recordset")
 
rsResults.open "Exec gettherecords '" & varKeyPhrase & "'", connect
 
response.WRITE rsResults("Content")
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.

Similar Threads

alter table stored procedure...???... in MS SQL
Result is not getting from Stored Procedure in ASP.NET
Stored Procedure parameter in Crystal ASP.NET Web in ASP.NET
datatype TEXT in stored procedure in ASP.NET
exec command in stored procedure depends on condition in ASP.NET

DorsetBloke

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Dec 2008

Permalink

Jul 1st, 2009

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 1st, 2009

Re: Stored procedure and datatype TEXT (ASP)

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

MS SQL Syntax (Toggle Plain Text)
SET connect = server.CreateObject ("ADODB.connection")
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.

DorsetBloke

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Dec 2008

Permalink

Jul 1st, 2009

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?

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 1st, 2009

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.

DorsetBloke

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Dec 2008

Permalink

Jul 1st, 2009

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 1st, 2009

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:

MS SQL Syntax (Toggle Plain Text)
SET rsResults = Server.CreateObject("ADODB.recordset")
rsResults.open "SELECT * From TheTable WHERE KeyPhrase='" & varKeyPhrase & "'", connect
response.WRITE rsResults("Content")
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):

MS SQL Syntax (Toggle Plain Text)
lContent = rsResults("Content")
response.WRITE lContent
lContent = rsResults("Content")
response.write lContent

NextCom

Reputation Points: 18

Solved Threads: 0

Light Poster

Offline

26 posts
since Dec 2008

Permalink

Aug 11th, 2009

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

Last edited by tamora; Aug 11th, 2009 at 3:54 am.

tamora

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: two select statements in one insert statement

Next Thread in MS SQL Forum Timeline: Importing csv to MS SQL

DaniWeb Message

Cancel Changes

User Name
Password