Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Blank Columns in query results?

Status
Not open for further replies.
jgoodman00

jgoodman00

Programmer
Jan 23, 2001
1,510
0
0
I have the following in an asp page:

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include file=&quot;cabinet/DatabaseConnect.asp&quot;-->
<!--#include virtual=&quot;/asp/15/adovbs.inc&quot;-->
<html>
<%
Dim objRS, strSQL
Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)
strSQL = &quot;SELECT * FROM qryItemData_Outgoing&quot;
objRS.Open strSQL, objConn
%>

<body>

<table border=&quot;1&quot; cellpadding=&quot;0&quot; cellspacing=&quot;0&quot; style=&quot;border-collapse: collapse&quot; bordercolor=&quot;#111111&quot; width=&quot;100%&quot; id=&quot;AutoNumber1&quot;>
<tr>
<td width=&quot;9%&quot;>Recipient</td>
<td width=&quot;6%&quot;>Outgoing ID</td>
<td width=&quot;24%&quot;>ToFor</td>
<td width=&quot;17%&quot;>ActionDate</td>
<td width=&quot;14%&quot;>OutgoingMethod</td>
<td width=&quot;15%&quot;>Sender</td>
<td width=&quot;15%&quot;>Comments</td>
</tr>


<%
'Display the contents of the table
Do While Not objRS.EOF
Response.Write &quot;<TR>&quot;
Response.Write &quot;<td width='9%'>&quot; & objRS(&quot;RecipientID&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='6%'>&quot; & objRS(&quot;OutgoingID&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='24%'>&quot; & objRS(&quot;ToFor&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='17%'>&quot; & objRS(&quot;ActionDate&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='14%'>&quot; & objRS(&quot;OutgoingMethod&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='15%'>&quot; & objRS(&quot;Sender&quot;) & &quot;</td>&quot;
Response.Write &quot;<td width='15%'>&quot; & objRS(&quot;Comments&quot;) & &quot;</td>&quot;
Response.write &quot;</TR>&quot;
'Move to the next row in the table
objRS.MoveNext
Loop

'Clean up our ADO objects
objRS.Close
%>
</table>

</body>

</html>

If I open the query in Query Analyzer, every column of every record has data.
However, when this page is loaded in IE the last three columns are empty.
If I change one of the working columns to a comment, the next column is output.


Any ideas why this is happening?


James Goodman MCSE, MCDBA
 
Sort by date Sort by votes
Not sure why that is happening but,

You may try putting it all together into a string and then outputing it. It should improve your performance as well:

like this:

OutLine = &quot;<TR><td width='9%'>&quot; & objRS(&quot;RecipientID&quot;) & &quot;</td><td width='6%'>&quot; & objRS(&quot;OutgoingID&quot;) & &quot;</td><td width='24%'>&quot; & objRS(&quot;ToFor&quot;) & &quot;</td><td width='17%'>&quot; & objRS(&quot;ActionDate&quot;) & &quot;</td><td width='14%'>&quot; & objRS(&quot;OutgoingMethod&quot;) & &quot;</td><td width='15%'>&quot; & objRS(&quot;Sender&quot;) & &quot;</td><td width='15%'>&quot; & objRS(&quot;Comments&quot;) & &quot;</td></TR>&quot;

response.write OutLine

I may have missed a quote somewhere in that long string. Take a look at it in your editor and make sure I did it right.




Travis Hawkins
BeachBum Software
travis@cfm2asp.com
 
Upvote 0 Downvote
I'm glad you found it.

Just for reference what kind of database is it that doesn't let you use a plain old text field?
 
Upvote 0 Downvote
Its a SQL Server 2000 DB. The text data type is very different to the Access text data type.
An Access Text field is similar to a SQL Server varchar field, whereby a limit is specified.
In SQL Server, a text data type has a maximum size of 2^31 characters.

James Goodman MCSE, MCDBA
 
Upvote 0 Downvote
I found the same thing. Obviously, the downside of VARCHAR is the limited characters allowed whilst TEXT can hold more.

There is 2 solutions I found:
1. Make sure the TEXT column is last on your SELECT statement.

2.OR Change the recordset location from Server side to client side.
eg.
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
set rs.ActiveConnection = objConn
rs.cursorLocation = 3 ' clientside
rs.cursorType = 0 ' forward
rs.LockType = 1 ' readonly
rs.open strSQL

I also believe this is an MDAC problem and might be fixed in the latest MDAC release....not 100% sure of this though.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
76
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
216
GriffMG
GriffMG
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
78
leifoet
leifoet
Swi
  • Locked
  • Question
PDF Interrogation
Replies
0
Views
54
Swi
Swi
DiDYRO_
  • Locked
  • Question
How do I make a VBS file that can collect dialog-type answers in a local .log file?
Replies
0
Views
73
DiDYRO_
DiDYRO_

Part and Inventory Search

Sponsor

Back
Top