Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2003
    Posts
    7

    Question Unanswered: Missing Values from specific fields

    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
    Code:
    SELECT *
    FROM table
    WHERE id=@id
    Now I have changed this to add an update before the Select statement to just increment a counter in one of the fields.
    Code:
    UPDATE table
    SET NumViews = NumViews + 1
    WHERE id = @id
    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.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  3. #3
    Join Date
    Jul 2003
    Posts
    7
    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
    Code:
    ALTER     PROCEDURE sp_showMessage2
    @mid	INT
    AS
    SET NOCOUNT ON
    
    --when I split it up, I made an sp UpdateViews, and had the lines below included
    --EXEC UpdateViews @mid
    UPDATE forum
    SET NumViews = NumViews + 1
    WHERE messageID = @mid
    
    SELECT	message_author,
    	message_email_show,
    	message_author_email,
    	message_timestamp,
    	message_subject,
    	message_body,
    	message_body_new,
    	reply,
    	MessageID,
    	numViews
    FROM	forum
    WHERE	messageID =  @mid
    Thanks for your help

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    Can you post the part of your ASP code, in which the SP is called and executed?

  5. #5
    Join Date
    Jul 2003
    Posts
    7
    Code:
    Dim rsForum, query, userID, mid, x
    
    mid=Request.QueryString("mid")
    set rsForum = server.CreateObject("ADODB.Recordset")
    query = "EXECUTE sp_showMessage2 " & mid
    rsForum.Open query, cnnForumDC, adOpenStatic, adLockOptimistic
    If rsForum.EOF Then Response.Redirect("./")
    
    Response.Write "<table>"
    For x=0 To rsForum.Fields.Count - 1
    	Response.Write "<tr><td>" & rsForum.Fields(x).Name
    	Response.Write "</td><td>" & rsForum.Fields(x).Value & "</td></tr>"
    Next
    Response.Write "</table>"
    Thank you

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    You can get all data except the one with TEXT datatype, right?

  7. #7
    Join Date
    Jul 2003
    Posts
    7
    that is correct

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  10. #10
    Join Date
    Dec 2003
    Posts
    454
    rnealejr is right. If the character number is less than 8000, do not use TEXT datatype.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  12. #12
    Join Date
    Dec 2003
    Posts
    454
    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
    %>

  13. #13
    Join Date
    Dec 2003
    Posts
    454

    found the solution

    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.

  14. #14
    Join Date
    Dec 2003
    Posts
    454
    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.

  15. #15
    Join Date
    Feb 2002
    Posts
    2,232
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •