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.QuerySt ring("l_name")
prob = Request.QuerySt ring("prob")
' response.write( l_name)

set Conn=Server.Cre ateObject("ADOD B.Connection")
set rsCustSurvey = server.CreateOb ject("ADODB.Rec ordset")
set raction_summary =server.CreateO bject("ADODB.Re cordset")
Conn.open "Driver={SQ L Server};
Server=serverL0 1;Database=mine ;UID=u;PWD=p;"

set rsCustSurvey = conn.Execute ("Select ProblemDescript ionTrunc,
Action_Summary, ProblemItem, Problem_Solutio n002, " _
& " Problem_Descrip tion002, RootCause, LastModifiedBy,
dbo.TTS_Main.As signedGroup, dbo.TTS_Main.La st_Name,
dbo.TTS_Main.Fi rst_Name, " _
& " dbo.TTS_Main.Tr acker, dbo.TTS_Main.St atus,
dbo.TTS_Main.As signedGroup, AssignedTechnic ian, ModificationHis tory "_
& " From tts_main " _
& " Where ProblemDescript ionTrunc LIKE '%" & prob & "%' " _
& " And Last_Name LIKE '%" & l_name & "%' ")

if rsCustSurvey.EO F 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>Pro blem
Description</font></td>
<td align="center"> <font face="Arial" size="2"><b>Act ion
Summary</font></td>
<td align="center"> <font face="Arial" size="2"><b>Roo t
Cause</font></td>
<td align="center"> <font face="Arial" size="2"><b>Pro blem
Solution</font></td>

</tr>
<%do while not rsCustSurvey.EO F%></do>
<tr>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_Summary") %>
</td>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("P roblem_Solution 002")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("R ootCause")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_Summary") %>
</td>

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

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

Suggestions?

[Mike Brind]

<da******@gmail .comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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.QuerySt ring("l_name")
prob = Request.QuerySt ring("prob")
' response.write( l_name)

set Conn=Server.Cre ateObject("ADOD B.Connection")
set rsCustSurvey = server.CreateOb ject("ADODB.Rec ordset")
set raction_summary =server.CreateO bject("ADODB.Re cordset")
Conn.open "Driver={SQ L Server};
Server=serverL0 1;Database=mine ;UID=u;PWD=p;"

set rsCustSurvey = conn.Execute ("Select ProblemDescript ionTrunc,
Action_Summary, ProblemItem, Problem_Solutio n002, " _
& " Problem_Descrip tion002, RootCause, LastModifiedBy,
dbo.TTS_Main.As signedGroup, dbo.TTS_Main.La st_Name,
dbo.TTS_Main.Fi rst_Name, " _
& " dbo.TTS_Main.Tr acker, dbo.TTS_Main.St atus,
dbo.TTS_Main.As signedGroup, AssignedTechnic ian, ModificationHis tory "_
& " From tts_main " _
& " Where ProblemDescript ionTrunc 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.EO F 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>Pro blem
Description</font></td>
<td align="center"> <font face="Arial" size="2"><b>Act ion
Summary</font></td>
<td align="center"> <font face="Arial" size="2"><b>Roo t
Cause</font></td>
<td align="center"> <font face="Arial" size="2"><b>Pro blem
Solution</font></td>

</tr>
<%do while not rsCustSurvey.EO F%></do>
<tr>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_Summary") %>
</td>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("P roblem_Solution 002")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("R ootCause")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_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

[Anthony Jones]

<da******@gmail .comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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.QuerySt ring("l_name")
prob = Request.QuerySt ring("prob")
' response.write( l_name)

set Conn=Server.Cre ateObject("ADOD B.Connection")
set rsCustSurvey = server.CreateOb ject("ADODB.Rec ordset")
set raction_summary =server.CreateO bject("ADODB.Re cordset")
Conn.open "Driver={SQ L Server};
Server=serverL0 1;Database=mine ;UID=u;PWD=p;"

set rsCustSurvey = conn.Execute ("Select ProblemDescript ionTrunc,
Action_Summary, ProblemItem, Problem_Solutio n002, " _
& " Problem_Descrip tion002, RootCause, LastModifiedBy,
dbo.TTS_Main.As signedGroup, dbo.TTS_Main.La st_Name,
dbo.TTS_Main.Fi rst_Name, " _
& " dbo.TTS_Main.Tr acker, dbo.TTS_Main.St atus,
dbo.TTS_Main.As signedGroup, AssignedTechnic ian, ModificationHis tory "_
& " From tts_main " _
& " Where ProblemDescript ionTrunc LIKE '%" & prob & "%' " _
& " And Last_Name LIKE '%" & l_name & "%' ")

if rsCustSurvey.EO F 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>Pro blem
Description</font></td>
<td align="center"> <font face="Arial" size="2"><b>Act ion
Summary</font></td>
<td align="center"> <font face="Arial" size="2"><b>Roo t
Cause</font></td>
<td align="center"> <font face="Arial" size="2"><b>Pro blem
Solution</font></td>

</tr>
<%do while not rsCustSurvey.EO F%></do>
<tr>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_Summary") %>
</td>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("P roblem_Solution 002")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("R ootCause")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_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.Mo veNext
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.HTMLEnco de
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.EO F

%>
<tr>
<td><%=Server.H TMLEncode(rsCus tServer("Action _Summary"))%></td>
<td><%=Server.H TMLEncode(rsCus tServer("Proble m_Solution002") )%></td>
<td><%=Server.H TMLEncode(rsCus tServer("RootCa use"))%></td>
<td><%=Server.H TMLEncode(rsCus tServer("Action _Summary"))%></td>
</tr>
<%

rsCustSurvey.Mo veNext
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
Anthony Jones wrote:

<da******@gmail .comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.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.QuerySt ring("l_name")
prob = Request.QuerySt ring("prob")
' response.write( l_name)

set Conn=Server.Cre ateObject("ADOD B.Connection")
set rsCustSurvey = server.CreateOb ject("ADODB.Rec ordset")
set raction_summary =server.CreateO bject("ADODB.Re cordset")
Conn.open "Driver={SQ L Server};
Server=serverL0 1;Database=mine ;UID=u;PWD=p;"

set rsCustSurvey = conn.Execute ("Select ProblemDescript ionTrunc,
Action_Summary, ProblemItem, Problem_Solutio n002, " _
& " Problem_Descrip tion002, RootCause, LastModifiedBy,
dbo.TTS_Main.As signedGroup, dbo.TTS_Main.La st_Name,
dbo.TTS_Main.Fi rst_Name, " _
& " dbo.TTS_Main.Tr acker, dbo.TTS_Main.St atus,
dbo.TTS_Main.As signedGroup, AssignedTechnic ian, ModificationHis tory "_
& " From tts_main " _
& " Where ProblemDescript ionTrunc LIKE '%" & prob & "%' " _
& " And Last_Name LIKE '%" & l_name & "%' ")

if rsCustSurvey.EO F 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>Pro blem
Description</font></td>
<td align="center"> <font face="Arial" size="2"><b>Act ion
Summary</font></td>
<td align="center"> <font face="Arial" size="2"><b>Roo t
Cause</font></td>
<td align="center"> <font face="Arial" size="2"><b>Pro blem
Solution</font></td>

</tr>
<%do while not rsCustSurvey.EO F%></do>
<tr>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_Summary") %>
</td>
<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("P roblem_Solution 002")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("R ootCause")%>
</td>

<td><font face="Arial" size=1>
<%response.writ e rsCustSurvey("A ction_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.Mo veNext
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.HTMLEnco de
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.EO F

%>
<tr>
<td><%=Server.H TMLEncode(rsCus tServer("Action _Summary"))%></td>
<td><%=Server.H TMLEncode(rsCus tServer("Proble m_Solution002") )%></td>
<td><%=Server.H TMLEncode(rsCus tServer("RootCa use"))%></td>
<td><%=Server.H TMLEncode(rsCus tServer("Action _Summary"))%></td>
</tr>
<%

rsCustSurvey.Mo veNext
Loop

%>
</tbody>
</table>
HTH,

Anthony.