MS SQL RSS MS SQL RSS

Stored procedure and datatype TEXT (ASP)

Reply

•
•
Join Date: Dec 2008
Posts: 5
Reputation: DorsetBloke is an unknown quantity at this point 
Solved Threads: 0
DorsetBloke DorsetBloke is offline Offline
Newbie Poster

Stored procedure and datatype TEXT (ASP)

 
0
  #1
Jul 1st, 2009
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) 
  1. CREATE PROCEDURE gettherecords
  2.        @KeyPhrase varchar(100)
  3. AS
  4. BEGIN
  5.   SELECT * FROM TheTable WHERE KeyPhrase = @KeyPhrase
  6. END


ASP code:

MS SQL Syntax (Toggle Plain Text) 
  1. SET rsResults = Server.CreateObject("ADODB.recordset")
  2.  
  3. rsResults.open "Exec gettherecords '" & varKeyPhrase & "'", connect
  4.  
  5. response.WRITE rsResults("Content")

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


Thanks for your help, chaps.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3,134
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 552
Sponsor
sknake's Avatar
sknake sknake is offline Offline
.NET Enthusiast

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #2
Jul 1st, 2009
Is you recordset using an oledb or odbc connection? I think odbc in classic asp has issues with text/ntext data types.
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 5
Reputation: DorsetBloke is an unknown quantity at this point 
Solved Threads: 0
DorsetBloke DorsetBloke is offline Offline
Newbie Poster

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #3
Jul 1st, 2009
Not sure what you mean. Here's the database connection code if that helps:

MS SQL Syntax (Toggle Plain Text) 
  1. 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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3,134
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 552
Sponsor
sknake's Avatar
sknake sknake is offline Offline
.NET Enthusiast

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #4
Jul 1st, 2009
What is the error then? I don't understand what you need help with. Is the SP blowing up?
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 5
Reputation: DorsetBloke is an unknown quantity at this point 
Solved Threads: 0
DorsetBloke DorsetBloke is offline Offline
Newbie Poster

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #5
Jul 1st, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2009
Posts: 3,134
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 552
Sponsor
sknake's Avatar
sknake sknake is offline Offline
.NET Enthusiast

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #6
Jul 1st, 2009
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.
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 21
Reputation: NextCom is an unknown quantity at this point 
Solved Threads: 0
NextCom NextCom is offline Offline
Newbie Poster

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #7
Jul 1st, 2009
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) 
  1. SET rsResults = Server.CreateObject("ADODB.recordset")
  2. rsResults.open "SELECT * From TheTable WHERE KeyPhrase='" & varKeyPhrase & "'", connect
  3. 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) 
  1. lContent = rsResults("Content")
  2. response.WRITE lContent
Reply With Quote Quick reply to this message  
•
•
Join Date: Aug 2009
Posts: 1
Reputation: tamora is an unknown quantity at this point 
Solved Threads: 0
tamora tamora is offline Offline
Newbie Poster

Re: Stored procedure and datatype TEXT (ASP)

 
0
  #8
Aug 11th, 2009
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop microsoft ms multithreading news permission query server sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC