Hi there,

I am quite puzzled on this one. I have an sp that returns a record from my SQL Server db which is pretty much
Now I have changed this to add an update before the Select statement to just increment a counter in one of the fields.
However, now when I call the sp I am missing the values in two of the fields, which are of type text.
If you are wondering why I posted this here and not in SQL Server, is b/c when I run the sp in Query Analyzer, I get the desired results, of the full recordset, however when I run it in ASP, I get everything but my TEXT fields.

I am really lost on this one.

Originally it was a type NTEXT, but I changed it to TEXT and copied the data to the new field, but it still didn't work.

Thank you for your help in advance.

You are returning multiple recordsets when you run this stored procedure. Since update is first in the sp, you are only looking at that recordset. You have to move to the next recordset to retrieve the select portion.

Thanks rnealejr,

However, it still doesn't work. I tried moving to the next record, but there was none. I then split up my procedure and have one calling the other, but still these fileds don't show up.

Again, here is my whole procedure, not split up
Thanks for your help

Can you post the part of your ASP code, in which the SP is called and executed?

Thank you

You can get all data except the one with TEXT datatype, right?

that is correct

Use the getchunk method. You can also modify your select statement so that the text columns are at the end - this might be a simpler solution but may not work.

What is the longest record that you use for these text fields - I ask because so many people use text that do not need to.

rnealejr is right. If the character number is less than 8000, do not use TEXT datatype.

funart - not to say that you are using a text data type when you should not (especially since you were using ntext) but I see this quite frequently.

funart,

I created a test SP and ASP file and it worked. Here is my SP and ASP code:

---------- SP ----------
CREATE PROCEDURE dbo.USP_DisplayJobTest
@jobID int
AS
SET NOCOUNT ON

-- Increment hitcount for this job by 1
UPDATE Jobs SET Numhits = (NumHits + 1) WHERE JobID = @jobID

-- JobSummaryLong is set to TEXT datatype
SELECT JobTitle, Numhits, JobSummaryLong
FROM Jobs
WHERE JobID = @jobID

SET NOCOUNT OFF
GO

---------- ASP code ----------
<%@ Language = VBScript %>
<% Option Explicit %>
<%
Dim jobID, Conn, RS, SQL, i
jobID = Request.QueryString("jobID")
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
Conn.ConnectionString = Application("ConnStr")
Conn.Open
SQL = "EXECUTE USP_DisplayJobTest " & jobID
Const adOpenStatic = 3
Const adLockOptimistic = 3
RS.Open SQL, Conn, adOpenStatic, adLockOptimistic
If Not RS.EOF Then
%>
<table align="center" width="100%" border="0" cellpadding="0" cellspacing="0">
<% For i = 0 To RS.Fields.Count - 1 %>
<tr>
<td class="Normal"><%= RS.Fields(i).Name %></td>
<td class="Normal"><%= RS.Fields(i).Value %></td>
</tr>
<% Next %>
</table>
<%
End If
RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>

funart,

I found the solution. In the SELECT clause, you need to put the fields with TEXT datatype to the last position, i.e.,

SELECT JobTitle, Numhits, JobSummaryLong
FROM Jobs
WHERE JobID = @jobID

The above one works, but if you use

SELECT JobTitle, JobSummaryLong, Numhits
FROM Jobs
WHERE JobID = @jobID

it does not work. Please do not ask me why since I don't know why. Try it.

The following text is from the book "BEGINNING ASP Databases" written by John Kauffman:

SQL Server has this quirk: if you perform a SELECT SQL statement which includes data from a Text field, the Text field must be the last field referred to in the statement, or you will have problem. There are two solutions to this:

Structure your table so that Text fields are at the end. This allows you to use SELECT * ... without any problems. When you use SELECT * ... all the fields in the table are returned in the physical order in which they appear in the table.

If you can't restructure the table, don't use SELECT * .... Instead, use a fields list in your SELECT SQL statements, and place the Text fields at the end of the fields list.

gyuan - You still may have problems with returning multiple text fields - hence my previous post -

"Use the getchunk method. You can also modify your select statement so that the text columns are at the end - this might be a simpler solution but may not work."