Changing order of recordsets creates non results

[Kevin]

Hi,
I have used ASP for years using MS Access and have used MSSQL quite a lot as
well. I have never came across something like this before.

MSSQL table names and types:

ProductName nvarchar
ShortDescription nText
ThumbNail nvarchar
etc

When I have the recordset in the order as follows, all works well:
(Recordset2.Fields.Item("ProductName").Value)
(Recordset2.Fields.Item("ShortDescription").Value)
(Recordset2.Fields.Item("Thumbnail").Value)

Though if I have the Thumbnail first the description does'nt show.

Anybody got any ideas on this?

Kindest regards,
Kevin

[Bob Barrows [MVP]]

Kevin wrote:

Hi,
I have used ASP for years using MS Access and have used MSSQL quite a
lot as well. I have never came across something like this before.

MSSQL table names and types:

ProductName nvarchar
ShortDescription nText
ThumbNail nvarchar
etc

When I have the recordset in the order as follows, all works well:
What do you mean "have the recordset in order"? Do you mean if you " put
the fields in the select statement that produces the recordset in that
order"?
(Recordset2.Fields.Item("ProductName").Value)
(Recordset2.Fields.Item("ShortDescription").Value)
(Recordset2.Fields.Item("Thumbnail").Value)

Though if I have the Thumbnail first the description does'nt show.

We just had a long thread about this. Here's the aspfaq article:
http://www.aspfaq.com/show.asp?id=2188

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Kevin]

Hi Bob,
Thanks for your speedy reply. What I mean is that depending on what order
the recordesets are used to display data on the page is dependant on whther
all the data is displayed. I will read that article you recommended. from
what i have read so far i am going to try adding all the colums instead of
just SELECT *.
Kindest regards,
Kevin


"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP05.phx.gbl...

Kevin wrote:

Hi,
I have used ASP for years using MS Access and have used MSSQL quite a
lot as well. I have never came across something like this before.

MSSQL table names and types:

ProductName nvarchar
ShortDescription nText
ThumbNail nvarchar
etc

When I have the recordset in the order as follows, all works well:

What do you mean "have the recordset in order"? Do you mean if you " put
the fields in the select statement that produces the recordset in that
order"?

(Recordset2.Fields.Item("ProductName").Value)
(Recordset2.Fields.Item("ShortDescription").Value)
(Recordset2.Fields.Item("Thumbnail").Value)

Though if I have the Thumbnail first the description does'nt show.

We just had a long thread about this. Here's the aspfaq article:
http://www.aspfaq.com/show.asp?id=2188

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [SQL Server MVP]]

> what i have read so far i am going to try adding all the colums instead of

just SELECT *.

Yes, NEVER, EVER use select * in production code.

[Kevin]

Hi Aaron, Bob and everyone,
I just tried the solutions here http://www.aspfaq.com/show.asp?id=2188 and
added all the columns instead of using SELECT * though I still have the same
problem. I also found an old global.asa that used an older Access db rather
than the new mssql which I deleted. Any other thoughts on this would be much
appreciated.
Kindest regards,
Kevin

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraa> wrote in message
news:uG**************@TK2MSFTNGP03.phx.gbl...

what i have read so far i am going to try adding all the colums instead
of just SELECT *.

Yes, NEVER, EVER use select * in production code.

[Bob Barrows [MVP]]

Kevin wrote:

Hi Aaron, Bob and everyone,
I just tried the solutions here http://www.aspfaq.com/show.asp?id=2188 and
added all the columns
instead of using SELECT * though I still have the same problem. I
also found an old global.asa that used an older Access db rather than
the new mssql which I deleted. Any other thoughts on this would be
much appreciated.

We're pretty much in the dark here. You need to provide a small page that
reproduces your problem
("small" is the key word - leave out anything that has no bearing on
reproducing the problem.)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Dave Anderson]

Kevin wrote:

What I mean is that depending on what order the recordesets
are used to display data on the page is dependant on whther
all the data is displayed.

"Displayed" is such a suspicion-inspiring word. What if you HTMLEncode the
values?

<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%>
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

Thanks everyone for your input here is a basic part of the asp page in
question
select productname, shortdescription, thumbnail from product where id = id

body of the page that works and displays the data

here is the product name
<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%><br>
here is the description
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>
here is the thumbnail
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>

In the above order everything works, though if I change the order around as
below the description doesn't display:

here is the thumbnail
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>
here is the product name
<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%><br>
here is the description
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>

Kindest regards,
Kevin



"Dave Anderson" <NY**********@spammotel.com> wrote in message
news:Og**************@TK2MSFTNGP04.phx.gbl...

Kevin wrote:

What I mean is that depending on what order the recordesets
are used to display data on the page is dependant on whther
all the data is displayed.
"Displayed" is such a suspicion-inspiring word. What if you HTMLEncode the
values?

<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%>
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.

Use of this email address implies consent to these terms.

[Bob Barrows [MVP]]

km****@yahoo.com wrote:

Thanks everyone for your input here is a basic part of the asp page in
question
I intended that you provide a complete working page that we could run on our
own web server and reproduce your problem (after creating your table in our
database of course). That is what is meant by "repro script"

Well, let's take a look anyways ...


select productname, shortdescription, thumbnail from product where id
= id

body of the page that works and displays the data

here is the product name
<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%><br>
here is the description
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>
here is the thumbnail
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>

In the above order everything works, though if I change the order
around as below the description doesn't display:

here is the thumbnail
<%=Server.HTMLEncode(Recordset2.Fields("Thumbnail" ).Value)%>
here is the product name
<%=Server.HTMLEncode(Recordset2.Fields("ProductNam e").Value)%><br>
here is the description
<%=Server.HTMLEncode(Recordset2.Fields("ShortDescr iption").Value)%>

I thought you said you followed ALL the reccomendations in the aspfaq
article ... I can see right now that you did not follow the advice to
immediately assign the value contained in the description field to a
variable to be used in subsequent processing. IOW,

' ... open the recordset
dim desc
desc=Recordset2.Fields("ShortDescription").Value
' do your other stuff ...
<%=Server.HTMLEncode(desc)%>
Are you using ODBC or the native OLE DB provider for your database in your
connecton string? This behavior should not be occurring with the OLE DB
provider.

http://www.aspfaq.com/show.asp?id=2126

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hi Bob,
I hope this will suffice, I have been trying to get around the problem by
adding this but the result either way is the same in this order but if I put
the PPrice to the bottom of the list below I get the description to display:
<% PPrice = (Recordset2.Fields.Item("Retail").Value)
title = (Recordset2.Fields.Item("ProductName").Value)
ID = (Recordset2.Fields.Item("ID").Value)
SDesc = (Recordset2.Fields.Item("ShortDescription").Value)
SPIC = (Recordset2.Fields.Item("Thumbnail").Value)
PayOText = (Recordset2.Fields.Item("PayOptionText").Value)
ShipO=(Recordset2.Fields.Item("ShipOptionText").Va lue)
SShipO = (Recordset2.Fields.Item("SpecialShippingText").Val ue)
STrade = (Recordset2.Fields.Item("Trademark").Value)
%>

anyway this is the page, it's getting a bit messy now as i have tried many
things trying to get it to work.

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/sql2k.asp" -->
<%
Dim Recordset2
Dim Recordset2_cmd
Dim Recordset2_numRows
Dim ID, ProductName, ShortDescription, Thumbnail, Logo, Retail ,
PayOptionText, ShipOptionText, SpecialShippingText, BackOrderText, Guarantee
Dim Disclaimer, CSREmail, CategoryID, Category, Trademark, DateLastEdited,
CSRPhone, OrderTypeID, Video, CheckByPhoneYN
Set Recordset2_cmd = Server.CreateObject ("ADODB.Command")
Recordset2_cmd.ActiveConnection = MM_sql2k_STRING
Recordset2_cmd.CommandText = "SELECT ID, ProductName, ShortDescription,
Thumbnail, Logo, Retail , PayOptionText, ShipOptionText,
SpecialShippingText, BackOrderText, Guarantee, Disclaimer, CSREmail,
CategoryID, Category, Trademark, DateLastEdited, CSRPhone, OrderTypeID,
Video, CheckByPhoneYN FROM Product WHERE ID =" &
Request.QueryString("prodid")'"
Recordset2_cmd.Prepared = true

Set Recordset2 = Recordset2_cmd.Execute
Recordset2_numRows = 0
%>
<%
PPrice = (Recordset2.Fields.Item("Retail").Value)
title = (Recordset2.Fields.Item("ProductName").Value)
ID = (Recordset2.Fields.Item("ID").Value)
SDesc = (Recordset2.Fields.Item("ShortDescription").Value)
SPIC = (Recordset2.Fields.Item("Thumbnail").Value)
PayOText = (Recordset2.Fields.Item("PayOptionText").Value)
ShipO=(Recordset2.Fields.Item("ShipOptionText").Va lue)
SShipO = (Recordset2.Fields.Item("SpecialShippingText").Val ue)
STrade = (Recordset2.Fields.Item("Trademark").Value)
%>
<br>
<img src="<%=SPIC%>" alt=""><br>
<%=(Recordset2.Fields.Item("ProductName").Value)%> <br>
<br>
<%=SDesc%><br>
<%=(Recordset2.Fields.Item("Guarantee").Value)%><b r>

<br>
<br>
<%=PayOText%><br>
<%=ShipO%><br>
<%=SShipO%><br>
<p>
Order Online <img src="images/icon-lock.gif" border="0">: <a href =
"order.asp?id=<%=(Recordset2.Fields.Item("ID").Val ue)%>&AID=<%= AID %>"><img
src="images/tv-order.gif" width="127" height="22"></a> For <%= PPrice%>
</p>

<p> Order by Phone <img src="images/icon-phone.gif">: <font size="3"
color="#FF0000"><strong><%=(Recordset2.Fields.Item ("CSRPhone").Value)%></str
ong></font></p>
<br>

<br>
STrade <%=STrade%><br>
ShipC <%=ShipC%><br>
SShipC <%=SShipC%><br>
STrade <%=STrade%><br>
<img src="images/spacer.gif" width="10" height="5"><span class="boxes">
<table width="100%" border="0" cellspacing="0" cellpadding="5">
<tr>
<td valign="top">
<h2><%= title %> Reviews:</h2>
<%
response.write "<p align='center'>Have you tried this product? Share
your experience:<br><br><img src='images/icon-Thumbnail.gif'> <a
href='_reviews_write.asp?ID=" & id & "'><b>Review " & title & " Now</b></a>
<img src='images/icon-Thumbnail.gif'><br>or<br><strong align='center'><img
src='images/icon-glasses.gif'> <a href='reviews.asp?ID=" & id & "'>Read " &
title & " Reviews</a> <img src='images/icon-glasses.gif'></strong><ol>"

%>

</td>
</tr>
</table>
</span> </td>
</tr>
</table>
<p>&nbsp;</p>
<map name="Map">
<area shape="rect" coords="27,222,137,249"
href="detail.asp?prodid=1079&AID=<%= AID %>">
<area shape="rect" coords="74,341,192,366" href="_reviews_write.asp?ID=<%=
id %>">
<area shape="rect" coords="6,384,261,414" href="reviews.asp?ID=<%= id %>">
</map>

</body>
</html>

<%
Recordset2.Close()
Set Recordset2 = Nothing
%>

[Bob Barrows [MVP]]

repro script = small

Your original post only mentioned 3 fields. Now I see 9 ... ? Are all 9
required to reproduce the problem?
How about all that html down there? Is all that stuff really needed to see
the problem occur? Get rid of everything that's not needed to see the
symptoms appear.

I also need to see what's in that include file (censor passwords of course).
I can't attempt to reproduce the problem without knowing what kind of
connection string you are using (of course, if your connection string does
not include the word "SQLOLEDB", then stop right there and read this:
http://www.aspfaq.com/show.asp?id=2126. The problem you are describing only
occurred when using ODBC if I remember correctly. Switch to the recomended
connection string and see if that solves the problem). Please include that
directly in the repro script without using an include file ... I'm sure the
use of the include file has nothing to do with your problem.

Oh, and you need to discontinue this practice (stuff like this is why most
of us react in disgust when we see someone using DW):
Recordset2_cmd.ActiveConnection = MM_sql2k_STRING

Setting ActiveConnection to a string forces ADO to use an implicit
connection object, which can defeat connection pooling, impairing the
performance and scalability of your application, and perhaps causing your
web server to crash. Always create and open an explicit connection object:

<%
dim cn
Set cn = createobject("adodb.connection")
cn.open MM_sql2k_STRING

Also all that other DW bloat is totally unnecessary, as well as inefficient
and unsafe. The command object was unnecessary, but since you've got it, we
might as well use it to open your recordset easily and safely as this:

dim rs,sql, ,arParms, cmd
sql="select ... where ID = ?"
set cmd=createobject("adodb.connection")
cmd.commandtext=sql
cmd.commandtype=1 'adCmdText
arparms = array(Request.QueryString("prodid"))
set cmd.ActiveConnection = cn
set rs = cmd.execute(,arParms)
if not rs.eof then
PPrice = rs("Retail") 'rs(0) would work also
title = rs("ProductName") 'rs(1) - get the idea?
etc.
end if
'now that you have all the values in variables, close the
'recordset now, as well as closing the connection:
rs.close:set rs = nothing
cn.close: set cn=nothing

Oh wait! I just say something else. Scroll down::

km****@yahoo.com wrote:
<SNIP>

<%
PPrice = (Recordset2.Fields.Item("Retail").Value)
title = (Recordset2.Fields.Item("ProductName").Value)
ID = (Recordset2.Fields.Item("ID").Value)
SDesc = (Recordset2.Fields.Item("ShortDescription").Value)
SPIC = (Recordset2.Fields.Item("Thumbnail").Value)
PayOText = (Recordset2.Fields.Item("PayOptionText").Value)
ShipO=(Recordset2.Fields.Item("ShipOptionText").Va lue)
SShipO = (Recordset2.Fields.Item("SpecialShippingText").Val ue)
STrade = (Recordset2.Fields.Item("Trademark").Value)
%>
<br>
<img src="<%=SPIC%>" alt=""><br>
<%=(Recordset2.Fields.Item("ProductName").Value)%> <br>

You have your values in variables. WHY IS YOUR RECORDSET STILL OPEN?
AND WHY ARE YOU STILL TRYING TO PULL THE VALUES OUT OF THE RECORDSET? Ooops,
hit my caps lock by mistake ...

The reason for setting the values to the variables was so you could use the
variables _instead of the recordset_!
Change all of these to variations of:
<img src="<%=SPIC%>" alt=""><br>
<%= title%><br>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hi Bob and everyone,
ok I am narrowing this problem down. Firstly I have taken all the DW code
out, I tried the SQLOLEDB though got told I didnt have permission for my
login. I will try that route more if this next part doesn't come to
anything.

The Data Type for the ShortDescription seems to be THE problem, If I change
it to nvchar everything work as it should except shortdescription gets cut
down to I would guess 255 charactors.

When I change it ntext some of the recordsets will not show.

Thanks again,
Kevin

Thanks everyone for all your help. I changed a lot of the mssql database
fields to varchar and on first glance everything looks good.
thanks again, especially to Bob,
Kevin
<km****@yahoo.com> wrote in message
news:eC**************@TK2MSFTNGP02.phx.gbl...

Hi Bob and everyone,
ok I am narrowing this problem down. Firstly I have taken all the DW code
out, I tried the SQLOLEDB though got told I didnt have permission for my
login. I will try that route more if this next part doesn't come to
anything.

The Data Type for the ShortDescription seems to be THE problem, If I
change it to nvchar everything work as it should except shortdescription
gets cut down to I would guess 255 charactors.

When I change it ntext some of the recordsets will not show.

Thanks again,
Kevin

[Bob Barrows [MVP]]

km****@yahoo.com wrote:

Hi Bob and everyone,
ok I am narrowing this problem down. Firstly I have taken all the DW
code out, I tried the SQLOLEDB though got told I didnt have
permission for my login. I will try that route more if this next part
doesn't come to anything.

The Data Type for the ShortDescription seems to be THE problem, If I
change it to nvchar everything work as it should except
shortdescription gets cut down to I would guess 255 charactors.

When I change it ntext some of the recordsets will not show.

Read the last part of my post. Once you have the data in variables,
close and destroy the recordset. you don't need it anymore. Use the
variables.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.