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
ShortDescriptio n nText
ThumbNail nvarchar
etc

When I have the recordset in the order as follows, all works well:
(Recordset2.Fie lds.Item("Produ ctName").Value)
(Recordset2.Fie lds.Item("Short Description").V alue)
(Recordset2.Fie lds.Item("Thumb nail").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]]

Re: Changing order of recordsets creates non results

Kevin wrote:[color=blue]
> 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
> ShortDescriptio n nText
> ThumbNail nvarchar
> etc
>
> When I have the recordset in the order as follows, all works well:[/color]

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"?
[color=blue]
> (Recordset2.Fie lds.Item("Produ ctName").Value)
> (Recordset2.Fie lds.Item("Short Description").V alue)
> (Recordset2.Fie lds.Item("Thumb nail").Value)
>
> Though if I have the Thumbnail first the description does'nt show.
>[/color]
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]

Re: Changing order of recordsets creates non results

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]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:eVkZ9RXlGH A.4468@TK2MSFTN GP05.phx.gbl...[color=blue]
> Kevin wrote:[color=green]
>> 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
>> ShortDescriptio n nText
>> ThumbNail nvarchar
>> etc
>>
>> When I have the recordset in the order as follows, all works well:[/color]
>
> 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"?
>[color=green]
>> (Recordset2.Fie lds.Item("Produ ctName").Value)
>> (Recordset2.Fie lds.Item("Short Description").V alue)
>> (Recordset2.Fie lds.Item("Thumb nail").Value)
>>
>> Though if I have the Thumbnail first the description does'nt show.
>>[/color]
> 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"
>[/color]

[Aaron Bertrand [SQL Server MVP]]

Re: Changing order of recordsets creates non results

> what i have read so far i am going to try adding all the colums instead of[color=blue]
> just SELECT *.[/color]

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

[Kevin]

Re: Changing order of recordsets creates non results

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]" <ten.xoc@dnartr eb.noraa> wrote in message
news:uGj5tdXlGH A.3924@TK2MSFTN GP03.phx.gbl...[color=blue][color=green]
>> what i have read so far i am going to try adding all the colums instead
>> of just SELECT *.[/color]
>
> Yes, NEVER, EVER use select * in production code.
>[/color]

[Bob Barrows [MVP]]

Re: Changing order of recordsets creates non results

Kevin wrote:[color=blue]
> 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.[/color]

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]

Re: Changing order of recordsets creates non results

Kevin wrote:[color=blue]
> 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.[/color]

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

<%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %>
<%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>
<%=Server.HTMLE ncode(Recordset 2.Fields("Thumb nail").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.

[Guest]

Re: Changing order of recordsets creates non results

Thanks everyone for your input here is a basic part of the asp page in
question


select productname, shortdescriptio n, thumbnail from product where id = id

body of the page that works and displays the data

here is the product name
<%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %><br>
here is the description
<%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>
here is the thumbnail
<%=Server.HTMLE ncode(Recordset 2.Fields("Thumb nail").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.HTMLE ncode(Recordset 2.Fields("Thumb nail").Value)%>
here is the product name
<%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %><br>
here is the description
<%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>

Kindest regards,
Kevin











"Dave Anderson" <NYRUMTPELVWH@s pammotel.com> wrote in message
news:OgEV0$XlGH A.2128@TK2MSFTN GP04.phx.gbl...[color=blue]
> Kevin wrote:[color=green]
> > 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.[/color]
>
> "Displayed" is such a suspicion-inspiring word. What if you HTMLEncode the
> values?
>
> <%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %>
> <%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>
> <%=Server.HTMLE ncode(Recordset 2.Fields("Thumb nail").Value)%>
>
>
>
> --
> Dave Anderson
>
> Unsolicited commercial email will be read at a cost of $500 per message.[/color]
Use[color=blue]
> of this email address implies consent to these terms.
>
>[/color]

[Bob Barrows [MVP]]

Re: Changing order of recordsets creates non results

kmickl@yahoo.co m wrote:[color=blue]
> Thanks everyone for your input here is a basic part of the asp page in
> question[/color]

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 ...
[color=blue]
>
>
> select productname, shortdescriptio n, thumbnail from product where id
> = id
>
> body of the page that works and displays the data
>
> here is the product name
> <%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %><br>
> here is the description
> <%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>
> here is the thumbnail
> <%=Server.HTMLE ncode(Recordset 2.Fields("Thumb nail").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.HTMLE ncode(Recordset 2.Fields("Thumb nail").Value)%>
> here is the product name
> <%=Server.HTMLE ncode(Recordset 2.Fields("Produ ctName").Value) %><br>
> here is the description
> <%=Server.HTMLE ncode(Recordset 2.Fields("Short Description").V alue)%>
>[/color]
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("ShortD escription").Va lue
' do your other stuff ...
<%=Server.HTMLE ncode(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"

[Guest]

Re: Changing order of recordsets creates non results

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.Fie lds.Item("Retai l").Value)
title = (Recordset2.Fie lds.Item("Produ ctName").Value)
ID = (Recordset2.Fie lds.Item("ID"). Value)
SDesc = (Recordset2.Fie lds.Item("Short Description").V alue)
SPIC = (Recordset2.Fie lds.Item("Thumb nail").Value)
PayOText = (Recordset2.Fie lds.Item("PayOp tionText").Valu e)
ShipO=(Recordse t2.Fields.Item( "ShipOptionText ").Value)
SShipO = (Recordset2.Fie lds.Item("Speci alShippingText" ).Value)
STrade = (Recordset2.Fie lds.Item("Trade mark").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="VB SCRIPT"%>
<!--#include file="Connectio ns/sql2k.asp" -->
<%
Dim Recordset2
Dim Recordset2_cmd
Dim Recordset2_numR ows
Dim ID, ProductName, ShortDescriptio n, Thumbnail, Logo, Retail ,
PayOptionText, ShipOptionText, SpecialShipping Text, BackOrderText, Guarantee
Dim Disclaimer, CSREmail, CategoryID, Category, Trademark, DateLastEdited,
CSRPhone, OrderTypeID, Video, CheckByPhoneYN
Set Recordset2_cmd = Server.CreateOb ject ("ADODB.Command ")
Recordset2_cmd. ActiveConnectio n = MM_sql2k_STRING
Recordset2_cmd. CommandText = "SELECT ID, ProductName, ShortDescriptio n,
Thumbnail, Logo, Retail , PayOptionText, ShipOptionText,
SpecialShipping Text, BackOrderText, Guarantee, Disclaimer, CSREmail,
CategoryID, Category, Trademark, DateLastEdited, CSRPhone, OrderTypeID,
Video, CheckByPhoneYN FROM Product WHERE ID =" &
Request.QuerySt ring("prodid")' "
Recordset2_cmd. Prepared = true

Set Recordset2 = Recordset2_cmd. Execute
Recordset2_numR ows = 0
%>


<%
PPrice = (Recordset2.Fie lds.Item("Retai l").Value)
title = (Recordset2.Fie lds.Item("Produ ctName").Value)
ID = (Recordset2.Fie lds.Item("ID"). Value)
SDesc = (Recordset2.Fie lds.Item("Short Description").V alue)
SPIC = (Recordset2.Fie lds.Item("Thumb nail").Value)
PayOText = (Recordset2.Fie lds.Item("PayOp tionText").Valu e)
ShipO=(Recordse t2.Fields.Item( "ShipOptionText ").Value)
SShipO = (Recordset2.Fie lds.Item("Speci alShippingText" ).Value)
STrade = (Recordset2.Fie lds.Item("Trade mark").Value)
%>
<br>
<img src="<%=SPIC%>" alt=""><br>
<%=(Recordset2. Fields.Item("Pr oductName").Val ue)%><br>


<br>
<%=SDesc%><br >
<%=(Recordset2. Fields.Item("Gu arantee").Value )%><br>

<br>
<br>
<%=PayOText%><b r>
<%=ShipO%><br >
<%=SShipO%><b r>
<p>
Order Online <img src="images/icon-lock.gif" border="0">: <a href =
"order.asp?id=< %=(Recordset2.F ields.Item("ID" ).Value)%>&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><%=(Re cordset2.Fields .Item("CSRPhone ").Value)%> </str
ong></font></p>
<br>

<br>
STrade <%=STrade%><b r>
ShipC <%=ShipC%><br >
SShipC <%=SShipC%><b r>
STrade <%=STrade%><b r>


<img src="images/spacer.gif" width="10" height="5"><spa n 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>Revie w " & title & " Now</b></a>
<img src='images/icon-Thumbnail.gif'> <br>or<br><stro ng align='center'> <img
src='images/icon-glasses.gif'> <a href='reviews.a sp?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.as p?prodid=1079&A ID=<%= AID %>">
<area shape="rect" coords="74,341, 192,366" href="_reviews_ write.asp?ID=<% =
id %>">
<area shape="rect" coords="6,384,2 61,414" href="reviews.a sp?ID=<%= id %>">
</map>

</body>
</html>

<%
Recordset2.Clos e()
Set Recordset2 = Nothing
%>

[Bob Barrows [MVP]]

Re: Changing order of recordsets creates non results

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. ActiveConnectio n = MM_sql2k_STRING

Setting ActiveConnectio n 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("a dodb.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=createobjec t("adodb.connec tion")
cmd.commandtext =sql
cmd.commandtype =1 'adCmdText
arparms = array(Request.Q ueryString("pro did"))
set cmd.ActiveConne ction = cn
set rs = cmd.execute(,ar Parms)
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::

kmickl@yahoo.co m wrote:
<SNIP>[color=blue]
> <%
> PPrice = (Recordset2.Fie lds.Item("Retai l").Value)
> title = (Recordset2.Fie lds.Item("Produ ctName").Value)
> ID = (Recordset2.Fie lds.Item("ID"). Value)
> SDesc = (Recordset2.Fie lds.Item("Short Description").V alue)
> SPIC = (Recordset2.Fie lds.Item("Thumb nail").Value)
> PayOText = (Recordset2.Fie lds.Item("PayOp tionText").Valu e)
> ShipO=(Recordse t2.Fields.Item( "ShipOptionText ").Value)
> SShipO = (Recordset2.Fie lds.Item("Speci alShippingText" ).Value)
> STrade = (Recordset2.Fie lds.Item("Trade mark").Value)
> %>
> <br>
> <img src="<%=SPIC%>" alt=""><br>
> <%=(Recordset2. Fields.Item("Pr oductName").Val ue)%><br>
>[/color]

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"

[Guest]

Re: Changing order of recordsets creates non results

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 ShortDescriptio n seems to be THE problem, If I change
it to nvchar everything work as it should except shortdescriptio n gets cut
down to I would guess 255 charactors.

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

Thanks again,
Kevin

[Guest]

Re: Changing order of recordsets creates non results

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


<kmickl@yahoo.c om> wrote in message
news:eCxV25ilGH A.4444@TK2MSFTN GP02.phx.gbl...[color=blue]
> 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 ShortDescriptio n seems to be THE problem, If I
> change it to nvchar everything work as it should except shortdescriptio n
> gets cut down to I would guess 255 charactors.
>
> When I change it ntext some of the recordsets will not show.
>
> Thanks again,
> Kevin
>[/color]

[Bob Barrows [MVP]]

Re: Changing order of recordsets creates non results

kmickl@yahoo.co m wrote:[color=blue]
> 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 ShortDescriptio n seems to be THE problem, If I
> change it to nvchar everything work as it should except
> shortdescriptio n gets cut down to I would guess 255 charactors.
>
> When I change it ntext some of the recordsets will not show.
>[/color]
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.