Disappearing Column Data

[dale.zjc]

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?

 

[Mike Brind]

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.

 

[Anthony Jones]

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.

 

[dale.zjc]

Anthony,

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

Dale