«

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?

<> wrote in message
news: oups.com...
> 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

<> wrote in message
news: oups.com...
> 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-sizex-small}
th {font-family:arial; font-sixe-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_Summ ary"))%></td>
<td><%=Server.HTMLEncode(rsCustServer("Problem_Sol ution002"))%></td>
<td><%=Server.HTMLEncode(rsCustServer("RootCause") )%></td>
<td><%=Server.HTMLEncode(rsCustServer("Action_Summ ary"))%></td>
</tr>
<%

rsCustSurvey.MoveNext
Loop

%>
</tbody>
</table>


HTH,

Anthony.

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: oups.com...
> > 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-sizex-small}
> th {font-family:arial; font-sixe-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_Summ ary"))%></td>
> <td><%=Server.HTMLEncode(rsCustServer("Problem_Sol ution002"))%></td>
> <td><%=Server.HTMLEncode(rsCustServer("RootCause") )%></td>
> <td><%=Server.HTMLEncode(rsCustServer("Action_Summ ary"))%></td>
> </tr>
> <%
>
> rsCustSurvey.MoveNext
> Loop
>
> %>
> </tbody>
> </table>
>
>
> HTH,
>
> Anthony.