Changing order of recordsets creates non results

Discussion in 'ASP General' started by Bob Barrows [MVP], Jun 21, 2006.

  1. 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"
     
    Bob Barrows [MVP], Jun 21, 2006
    #1
    1. Advertising

  2. > 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.
     
    Aaron Bertrand [SQL Server MVP], Jun 21, 2006
    #2
    1. Advertising

  3. 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"
     
    Bob Barrows [MVP], Jun 21, 2006
    #3
  4. 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("ProductName").Value)%>
    <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").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.
     
    Dave Anderson, Jun 21, 2006
    #4
  5. Bob Barrows [MVP]

    Kevin Guest

    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
     
    Kevin, Jun 21, 2006
    #5
  6. Bob Barrows [MVP]

    Kevin Guest

    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]" <> wrote in message
    news:...
    > 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, Jun 22, 2006
    #6
  7. Bob Barrows [MVP]

    Guest Guest

    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("ProductName").Value)%><br>
    here is the description
    <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").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("ProductName").Value)%><br>
    here is the description
    <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").Value)%>

    Kindest regards,
    Kevin











    "Dave Anderson" <> wrote in message
    news:OgEV0$...
    > 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("ProductName").Value)%>
    > <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").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.
    >
    >
     
    Guest, Jun 22, 2006
    #7
  8. Bob Barrows [MVP]

    Kevin Guest

    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]" <> wrote in message
    news:...
    >> 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, Jun 22, 2006
    #8
  9. 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("ProductName").Value)%><br>
    > here is the description
    > <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").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("ProductName").Value)%><br>
    > here is the description
    > <%=Server.HTMLEncode(Recordset2.Fields("ShortDescription").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"
     
    Bob Barrows [MVP], Jun 22, 2006
    #9
  10. Bob Barrows [MVP]

    Guest Guest

    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").Value)
    SShipO = (Recordset2.Fields.Item("SpecialShippingText").Value)
    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").Value)
    SShipO = (Recordset2.Fields.Item("SpecialShippingText").Value)
    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)%><br>

    <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").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><%=(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
    %>
     
    Guest, Jun 22, 2006
    #10
  11. 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::

    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").Value)
    > SShipO = (Recordset2.Fields.Item("SpecialShippingText").Value)
    > 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"
     
    Bob Barrows [MVP], Jun 22, 2006
    #11
  12. Bob Barrows [MVP]

    Guest Guest

    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
     
    Guest, Jun 22, 2006
    #12
  13. Bob Barrows [MVP]

    Guest Guest

    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


    <> wrote in message
    news:...
    > 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
    >
     
    Guest, Jun 22, 2006
    #13
  14. 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.
     
    Bob Barrows [MVP], Jun 22, 2006
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. AFN
    Replies:
    5
    Views:
    418
    dilipdotnet at apdiya.com
    Feb 11, 2004
  2. Sky
    Replies:
    2
    Views:
    375
  3. Larry
    Replies:
    0
    Views:
    196
    Larry
    May 12, 2004
  4. Aaron Bertrand [SQL Server MVP]

    Changing order of recordsets creates non results

    Aaron Bertrand [SQL Server MVP], Jun 21, 2006, in forum: ASP General
    Replies:
    1
    Views:
    122
    Kevin
    Jun 21, 2006
  5. Mark Volkmann

    Struct creates non-standard classes

    Mark Volkmann, Feb 12, 2006, in forum: Ruby
    Replies:
    7
    Views:
    133
    David Vallner
    Feb 13, 2006
Loading...

Share This Page