Disappearing Column Data

Discussion in 'ASP General' started by dale.zjc@gmail.com, Nov 15, 2006.

  1. Guest

    I've got some bizarre behavior going on with my ASP code below. For
    some strange reason (and I'm a newbie to ASP so it's probably obvious
    to others) I can't display all the rows of data from the query. As an
    example, the Problem Solution column doesn't display and unless I
    comment out another column. Or if I move Problem Solution and it make
    ithe first column, then the Root Cause won't display.

    Here's the code:

    <%
    Option Explicit
    Dim l_name
    Dim prob
    Dim rsCustSurvey
    Dim raction_summary
    Dim Conn
    l_name = Request.QueryString("l_name")
    prob = Request.QueryString("prob")
    ' response.write(l_name)

    set Conn=Server.CreateObject("ADODB.Connection")
    set rsCustSurvey = server.CreateObject("ADODB.Recordset")
    set raction_summary =server.CreateObject("ADODB.Recordset")


    Conn.open "Driver={SQL Server};
    Server=serverL01;Database=mine;UID=u;PWD=p;"

    set rsCustSurvey = conn.Execute ("Select ProblemDescriptionTrunc,
    Action_Summary, ProblemItem, Problem_Solution002, " _
    & " Problem_Description002, RootCause, LastModifiedBy,
    dbo.TTS_Main.AssignedGroup, dbo.TTS_Main.Last_Name,
    dbo.TTS_Main.First_Name, " _
    & " dbo.TTS_Main.Tracker, dbo.TTS_Main.Status,
    dbo.TTS_Main.AssignedGroup, AssignedTechnician, ModificationHistory "_
    & " From tts_main " _
    & " Where ProblemDescriptionTrunc LIKE '%" & prob & "%' " _
    & " And Last_Name LIKE '%" & l_name & "%' ")

    if rsCustSurvey.EOF then 'traps for IF recordset is empty THEN:
    Response.Write "There is no data"
    Response.End
    end if

    %>

    <table border="1">

    <tr>
    <td align="center"><font face="Arial" size="2"><b>Problem
    Description</font></td>
    <td align="center"><font face="Arial" size="2"><b>Action
    Summary</font></td>
    <td align="center"><font face="Arial" size="2"><b>Root
    Cause</font></td>
    <td align="center"><font face="Arial" size="2"><b>Problem
    Solution</font></td>

    </tr>


    <%do while not rsCustSurvey.EOF%></do>
    <tr>


    <td><font face="Arial" size=1>
    <%response.write rsCustSurvey("Action_Summary")%>
    </td>


    <td><font face="Arial" size=1>
    <%response.write rsCustSurvey("Problem_Solution002")%>
    </td>

    <td><font face="Arial" size=1>
    <%response.write rsCustSurvey("RootCause")%>
    </td>

    <td><font face="Arial" size=1>
    <%response.write rsCustSurvey("Action_Summary")%>
    </td>





    </tr>
    <%rsCustSurvey.MoveNext%>
    <%loop%>



    </table>


    <%
    set rsCustSurvey = nothing
    set conn = nothing
    %>

    Suggestions?
     
    , Nov 15, 2006
    #1
    1. Advertising

  2. Mike Brind Guest

    <> wrote in message
    news:...
    > I've got some bizarre behavior going on with my ASP code below. For
    > some strange reason (and I'm a newbie to ASP so it's probably obvious
    > to others) I can't display all the rows of data from the query. As an
    > example, the Problem Solution column doesn't display and unless I
    > comment out another column. Or if I move Problem Solution and it make
    > ithe first column, then the Root Cause won't display.
    >
    > Here's the code:
    >
    > <%
    > Option Explicit
    > Dim l_name
    > Dim prob
    > Dim rsCustSurvey
    > Dim raction_summary
    > Dim Conn
    > l_name = Request.QueryString("l_name")
    > prob = Request.QueryString("prob")
    > ' response.write(l_name)
    >
    > set Conn=Server.CreateObject("ADODB.Connection")
    > set rsCustSurvey = server.CreateObject("ADODB.Recordset")
    > set raction_summary =server.CreateObject("ADODB.Recordset")
    >
    >
    > Conn.open "Driver={SQL Server};
    > Server=serverL01;Database=mine;UID=u;PWD=p;"
    >
    > set rsCustSurvey = conn.Execute ("Select ProblemDescriptionTrunc,
    > Action_Summary, ProblemItem, Problem_Solution002, " _
    > & " Problem_Description002, RootCause, LastModifiedBy,
    > dbo.TTS_Main.AssignedGroup, dbo.TTS_Main.Last_Name,
    > dbo.TTS_Main.First_Name, " _
    > & " dbo.TTS_Main.Tracker, dbo.TTS_Main.Status,
    > dbo.TTS_Main.AssignedGroup, AssignedTechnician, ModificationHistory "_
    > & " From tts_main " _
    > & " Where ProblemDescriptionTrunc LIKE '%" & prob & "%' " _
    > & " And Last_Name LIKE '%" & l_name & "%' ")



    Nothing to do with your problem, but why are you selecting all these columns
    when you are only using 4 of them in your html?


    >
    > if rsCustSurvey.EOF then 'traps for IF recordset is empty THEN:
    > Response.Write "There is no data"
    > Response.End
    > end if
    >
    > %>
    >
    > <table border="1">
    >
    > <tr>
    > <td align="center"><font face="Arial" size="2"><b>Problem
    > Description</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Action
    > Summary</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Root
    > Cause</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Problem
    > Solution</font></td>
    >
    > </tr>
    >
    >
    > <%do while not rsCustSurvey.EOF%></do>
    > <tr>
    >
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Action_Summary")%>
    > </td>
    >
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Problem_Solution002")%>
    > </td>
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("RootCause")%>
    > </td>
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Action_Summary")%>
    > </td>
    > </tr>
    > <%rsCustSurvey.MoveNext%>
    > <%loop%>
    > </table>
    > <%
    > set rsCustSurvey = nothing
    > set conn = nothing
    > %>
    >
    > Suggestions?
    >


    View Source to see if there are any obvious html problems. If you can't see
    any, try posting the html source here. That would be my first place to
    look.

    --
    Mike Brind
     
    Mike Brind, Nov 15, 2006
    #2
    1. Advertising

  3. <> wrote in message
    news:...
    > I've got some bizarre behavior going on with my ASP code below. For
    > some strange reason (and I'm a newbie to ASP so it's probably obvious
    > to others) I can't display all the rows of data from the query. As an
    > example, the Problem Solution column doesn't display and unless I
    > comment out another column. Or if I move Problem Solution and it make
    > ithe first column, then the Root Cause won't display.
    >
    > Here's the code:
    >
    > <%
    > Option Explicit
    > Dim l_name
    > Dim prob
    > Dim rsCustSurvey
    > Dim raction_summary
    > Dim Conn
    > l_name = Request.QueryString("l_name")
    > prob = Request.QueryString("prob")
    > ' response.write(l_name)
    >
    > set Conn=Server.CreateObject("ADODB.Connection")
    > set rsCustSurvey = server.CreateObject("ADODB.Recordset")
    > set raction_summary =server.CreateObject("ADODB.Recordset")
    >
    >
    > Conn.open "Driver={SQL Server};
    > Server=serverL01;Database=mine;UID=u;PWD=p;"
    >
    > set rsCustSurvey = conn.Execute ("Select ProblemDescriptionTrunc,
    > Action_Summary, ProblemItem, Problem_Solution002, " _
    > & " Problem_Description002, RootCause, LastModifiedBy,
    > dbo.TTS_Main.AssignedGroup, dbo.TTS_Main.Last_Name,
    > dbo.TTS_Main.First_Name, " _
    > & " dbo.TTS_Main.Tracker, dbo.TTS_Main.Status,
    > dbo.TTS_Main.AssignedGroup, AssignedTechnician, ModificationHistory "_
    > & " From tts_main " _
    > & " Where ProblemDescriptionTrunc LIKE '%" & prob & "%' " _
    > & " And Last_Name LIKE '%" & l_name & "%' ")
    >
    > if rsCustSurvey.EOF then 'traps for IF recordset is empty THEN:
    > Response.Write "There is no data"
    > Response.End
    > end if
    >
    > %>
    >
    > <table border="1">
    >
    > <tr>
    > <td align="center"><font face="Arial" size="2"><b>Problem
    > Description</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Action
    > Summary</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Root
    > Cause</font></td>
    > <td align="center"><font face="Arial" size="2"><b>Problem
    > Solution</font></td>
    >
    > </tr>
    >
    >
    > <%do while not rsCustSurvey.EOF%></do>
    > <tr>
    >
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Action_Summary")%>
    > </td>
    >
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Problem_Solution002")%>
    > </td>
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("RootCause")%>
    > </td>
    >
    > <td><font face="Arial" size=1>
    > <%response.write rsCustSurvey("Action_Summary")%>
    > </td>
    >
    >
    >
    >
    >
    > </tr>
    > <%rsCustSurvey.MoveNext%>
    > <%loop%>
    >
    >
    >
    > </table>
    >
    >
    > <%
    > set rsCustSurvey = nothing
    > set conn = nothing
    > %>
    >
    > Suggestions?
    >


    Put both fields at the end of the set of fields returned in the query.
    Read them only after you have read all the other fields you need.
    Read them in the order they appear in the recordset
    That may mean you need to read fields into temporary variables so that you
    can output them in the order you prefer in the table output.

    OR

    use a readonly, keyset cursor instead of the forward only 'cursor' you are
    currently using

    OR

    stop using text/ntext field types and use varchar(8000)/nvarchar(4000) field
    types instead for these fields.


    The problem is by default SQL server provider use a forward only recordset
    which actually means no recordset at all. The records are read from a TDS
    stream almost direct from the query. Once read the record is discarded. If
    there are text and other indeterminately long fields ADO needs to move
    further along the stream to read them and once it's done that it will drop
    some of the data for those fields.

    Using a keyset recordset use what is more proper cursor allowing ADO to move
    back and forth in the recordset.

    BTW.

    Don't do this:-

    <%rsCustSurvey.MoveNext%>
    <%loop%>

    Do this:-

    <%
    rsCustSurvey.MoveNext
    loop
    %>

    Also note that the font element is deprecated. Put a CSS style element in
    the header:-

    <style>
    td {font-family:arial; font-size:xx-small}
    th {font-family:arial; font-sixe:x-small}
    </style>

    Also use th elements for column headers (th has centered bold as a default
    style) and use thead and tbody elements

    When sending text content from a DB to a browser you should ensure special
    characters such as < and & are properly encoded. The Server.HTMLEncode
    function does that.

    <table border="1">
    <thead>
    <tr>
    <th>Problem Description</th>
    <th>Action Summary</th>
    <th>Root Cause</th>
    <th>Problem Solution</th>
    </tr>
    </thead>
    <tbody>
    <%

    Do Until rsCustSurvey.EOF

    %>
    <tr>
    <td><%=Server.HTMLEncode(rsCustServer("Action_Summary"))%></td>
    <td><%=Server.HTMLEncode(rsCustServer("Problem_Solution002"))%></td>
    <td><%=Server.HTMLEncode(rsCustServer("RootCause"))%></td>
    <td><%=Server.HTMLEncode(rsCustServer("Action_Summary"))%></td>
    </tr>
    <%

    rsCustSurvey.MoveNext
    Loop

    %>
    </tbody>
    </table>


    HTH,

    Anthony.
     
    Anthony Jones, Nov 15, 2006
    #3
  4. Guest

    Anthony,

    Thanks for all the tips. I'm a newbie to both asp and css so your
    comments are quite helpful.

    Dale


    Anthony Jones wrote:
    > <> wrote in message
    > news:...
    > > I've got some bizarre behavior going on with my ASP code below. For
    > > some strange reason (and I'm a newbie to ASP so it's probably obvious
    > > to others) I can't display all the rows of data from the query. As an
    > > example, the Problem Solution column doesn't display and unless I
    > > comment out another column. Or if I move Problem Solution and it make
    > > ithe first column, then the Root Cause won't display.
    > >
    > > Here's the code:
    > >
    > > <%
    > > Option Explicit
    > > Dim l_name
    > > Dim prob
    > > Dim rsCustSurvey
    > > Dim raction_summary
    > > Dim Conn
    > > l_name = Request.QueryString("l_name")
    > > prob = Request.QueryString("prob")
    > > ' response.write(l_name)
    > >
    > > set Conn=Server.CreateObject("ADODB.Connection")
    > > set rsCustSurvey = server.CreateObject("ADODB.Recordset")
    > > set raction_summary =server.CreateObject("ADODB.Recordset")
    > >
    > >
    > > Conn.open "Driver={SQL Server};
    > > Server=serverL01;Database=mine;UID=u;PWD=p;"
    > >
    > > set rsCustSurvey = conn.Execute ("Select ProblemDescriptionTrunc,
    > > Action_Summary, ProblemItem, Problem_Solution002, " _
    > > & " Problem_Description002, RootCause, LastModifiedBy,
    > > dbo.TTS_Main.AssignedGroup, dbo.TTS_Main.Last_Name,
    > > dbo.TTS_Main.First_Name, " _
    > > & " dbo.TTS_Main.Tracker, dbo.TTS_Main.Status,
    > > dbo.TTS_Main.AssignedGroup, AssignedTechnician, ModificationHistory "_
    > > & " From tts_main " _
    > > & " Where ProblemDescriptionTrunc LIKE '%" & prob & "%' " _
    > > & " And Last_Name LIKE '%" & l_name & "%' ")
    > >
    > > if rsCustSurvey.EOF then 'traps for IF recordset is empty THEN:
    > > Response.Write "There is no data"
    > > Response.End
    > > end if
    > >
    > > %>
    > >
    > > <table border="1">
    > >
    > > <tr>
    > > <td align="center"><font face="Arial" size="2"><b>Problem
    > > Description</font></td>
    > > <td align="center"><font face="Arial" size="2"><b>Action
    > > Summary</font></td>
    > > <td align="center"><font face="Arial" size="2"><b>Root
    > > Cause</font></td>
    > > <td align="center"><font face="Arial" size="2"><b>Problem
    > > Solution</font></td>
    > >
    > > </tr>
    > >
    > >
    > > <%do while not rsCustSurvey.EOF%></do>
    > > <tr>
    > >
    > >
    > > <td><font face="Arial" size=1>
    > > <%response.write rsCustSurvey("Action_Summary")%>
    > > </td>
    > >
    > >
    > > <td><font face="Arial" size=1>
    > > <%response.write rsCustSurvey("Problem_Solution002")%>
    > > </td>
    > >
    > > <td><font face="Arial" size=1>
    > > <%response.write rsCustSurvey("RootCause")%>
    > > </td>
    > >
    > > <td><font face="Arial" size=1>
    > > <%response.write rsCustSurvey("Action_Summary")%>
    > > </td>
    > >
    > >
    > >
    > >
    > >
    > > </tr>
    > > <%rsCustSurvey.MoveNext%>
    > > <%loop%>
    > >
    > >
    > >
    > > </table>
    > >
    > >
    > > <%
    > > set rsCustSurvey = nothing
    > > set conn = nothing
    > > %>
    > >
    > > Suggestions?
    > >

    >
    > Put both fields at the end of the set of fields returned in the query.
    > Read them only after you have read all the other fields you need.
    > Read them in the order they appear in the recordset
    > That may mean you need to read fields into temporary variables so that you
    > can output them in the order you prefer in the table output.
    >
    > OR
    >
    > use a readonly, keyset cursor instead of the forward only 'cursor' you are
    > currently using
    >
    > OR
    >
    > stop using text/ntext field types and use varchar(8000)/nvarchar(4000) field
    > types instead for these fields.
    >
    >
    > The problem is by default SQL server provider use a forward only recordset
    > which actually means no recordset at all. The records are read from a TDS
    > stream almost direct from the query. Once read the record is discarded. If
    > there are text and other indeterminately long fields ADO needs to move
    > further along the stream to read them and once it's done that it will drop
    > some of the data for those fields.
    >
    > Using a keyset recordset use what is more proper cursor allowing ADO to move
    > back and forth in the recordset.
    >
    > BTW.
    >
    > Don't do this:-
    >
    > <%rsCustSurvey.MoveNext%>
    > <%loop%>
    >
    > Do this:-
    >
    > <%
    > rsCustSurvey.MoveNext
    > loop
    > %>
    >
    > Also note that the font element is deprecated. Put a CSS style element in
    > the header:-
    >
    > <style>
    > td {font-family:arial; font-size:xx-small}
    > th {font-family:arial; font-sixe:x-small}
    > </style>
    >
    > Also use th elements for column headers (th has centered bold as a default
    > style) and use thead and tbody elements
    >
    > When sending text content from a DB to a browser you should ensure special
    > characters such as < and & are properly encoded. The Server.HTMLEncode
    > function does that.
    >
    > <table border="1">
    > <thead>
    > <tr>
    > <th>Problem Description</th>
    > <th>Action Summary</th>
    > <th>Root Cause</th>
    > <th>Problem Solution</th>
    > </tr>
    > </thead>
    > <tbody>
    > <%
    >
    > Do Until rsCustSurvey.EOF
    >
    > %>
    > <tr>
    > <td><%=Server.HTMLEncode(rsCustServer("Action_Summary"))%></td>
    > <td><%=Server.HTMLEncode(rsCustServer("Problem_Solution002"))%></td>
    > <td><%=Server.HTMLEncode(rsCustServer("RootCause"))%></td>
    > <td><%=Server.HTMLEncode(rsCustServer("Action_Summary"))%></td>
    > </tr>
    > <%
    >
    > rsCustSurvey.MoveNext
    > Loop
    >
    > %>
    > </tbody>
    > </table>
    >
    >
    > HTH,
    >
    > Anthony.
     
    , Nov 17, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. carlos perez
    Replies:
    0
    Views:
    423
    carlos perez
    Jun 8, 2004
  2. =?Utf-8?B?YzY3NjIyOA==?=

    data disappearing problem on the forms when navigate

    =?Utf-8?B?YzY3NjIyOA==?=, Jan 31, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    405
    Steven Cheng[MSFT]
    Feb 2, 2006
  3. Thea
    Replies:
    1
    Views:
    352
  4. =?Utf-8?B?dmFuZGls?=
    Replies:
    2
    Views:
    1,208
    Eliyahu Goldin
    Jan 25, 2007
  5. steve.falzon@

    Gridview - disappearing data when column is hidden

    steve.falzon@, Sep 20, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    180
    steve.falzon@
    Sep 20, 2006
Loading...

Share This Page