SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: sql help

  1. #1
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql help

    I'm working on an e-commerce website and I'm having some trouble with my category page.
    Categories are defined by a table in MySQL called prodcategory that contains CatID and CatName as fields.
    My template for all users contains a recordset called rsCategories that reads
    Code:
    SELECT *
    Code:
    FROM prodcategory
    ORDER BY CatName ASC
    I then have CatName bound to my navigation with a repeat region command and a link to category.asp that passes a URL Parameter CatID1.
    Then it messes up my navigation on category.asp and for some reason pulls all products onto the page - I only want to display products where they are in that category.
    The site itself is on www.themensstore.com/test
    This is the rs for index.asp
    Code:
    <%
    Code:
    Dim rsCategories
    Dim rsCategories_cmd
    Dim rsCategories_numRows
    Set rsCategories_cmd = Server.CreateObject ("ADODB.Command")
    rsCategories_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategories_cmd.CommandText = "SELECT * FROM themensstore.prodcategory" 
    rsCategories_cmd.Prepared = true
    Set rsCategories = rsCategories_cmd.Execute
    rsCategories_numRows = 0
    %>
    and this is the rs for the category.asp page
    Code:
    <%
    Code:
    Dim rsCategories
    Dim rsCategories_cmd
    Dim rsCategories_numRows
    Set rsCategories_cmd = Server.CreateObject ("ADODB.Command")
    rsCategories_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategories_cmd.CommandText = "SELECT * FROM themensstore.prodcategory ORDER BY CatName ASC" 
    rsCategories_cmd.Prepared = true
    Set rsCategories = rsCategories_cmd.Execute
    rsCategories_numRows = 0
    %>
    <%
    Dim rsCategoriesurl__MMColParam
    rsCategoriesurl__MMColParam = "1"
    If (Request.QueryString("CatID1") <> "") Then 
     rsCategoriesurl__MMColParam = Request.QueryString("CatID1")
    End If
    %>
    <%
    Dim rsCategoriesurl
    Dim rsCategoriesurl_cmd
    Dim rsCategoriesurl_numRows
    Set rsCategoriesurl_cmd = Server.CreateObject ("ADODB.Command")
    rsCategoriesurl_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategoriesurl_cmd.CommandText = "SELECT * FROM themensstore.prodcategory WHERE CatID = ?" 
    rsCategoriesurl_cmd.Prepared = true
    rsCategoriesurl_cmd.Parameters.Append rsCategoriesurl_cmd.CreateParameter("param1", 5, 1, -1, rsCategoriesurl__MMColParam) ' adDouble
    Set rsCategoriesurl = rsCategoriesurl_cmd.Execute
    rsCategoriesurl_numRows = 0
    %>
    Any help is most appreciated.
    Thanks,
    S

  2. #2
    SitePoint Guru SSJ's Avatar
    Join Date
    Jan 2007
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    change the following line
    rsCategoriesurl_cmd.CommandText = "SELECT * FROM themensstore.prodcategory WHERE CatID = ?"
    To

    rsCategoriesurl_cmd.CommandText = "SELECT * FROM themensstore.prodcategory WHERE CatID = rsCategoriesurl__MMColParam"
    Hope this helps...

    -SSJ

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no it didn't like that at all - don't even get the page showing now.

    This is the code for the entire page:

    Code:
    <
    Code:
    " CODEPAGE="1252"%>
    <!--#include file="Connections/connTMS.asp" -->
    <%
    Dim rsCategories
    Dim rsCategories_cmd
    Dim rsCategories_numRows
    Set rsCategories_cmd = Server.CreateObject ("ADODB.Command")
    rsCategories_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategories_cmd.CommandText = "SELECT * FROM themensstore.prodcategory ORDER BY CatName ASC" 
    rsCategories_cmd.Prepared = true
    Set rsCategories = rsCategories_cmd.Execute
    rsCategories_numRows = 0
    %>
    <%
    Dim rsCategoryProducts
    Dim rsCategoryProducts_cmd
    Dim rsCategoryProducts_numRows
    Set rsCategoryProducts_cmd = Server.CreateObject ("ADODB.Command")
    rsCategoryProducts_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategoryProducts_cmd.CommandText = "SELECT products.*, productbrand.brandName FROM themensstore.products, themensstore.productbrand WHERE products.ProdBrandID = productbrand.brandID AND products.ProdIsActive = 1" 
    rsCategoryProducts_cmd.Prepared = true
    Set rsCategoryProducts = rsCategoryProducts_cmd.Execute
    rsCategoryProducts_numRows = 0
    %>
    <%
    Dim rsCategoriesurl__MMColParam
    rsCategoriesurl__MMColParam = "1"
    If (Request.QueryString("CatID1") <> "") Then 
      rsCategoriesurl__MMColParam = Request.QueryString("CatID1")
    End If
    %>
    <%
    Dim rsCategoriesurl
    Dim rsCategoriesurl_cmd
    Dim rsCategoriesurl_numRows
    Set rsCategoriesurl_cmd = Server.CreateObject ("ADODB.Command")
    rsCategoriesurl_cmd.ActiveConnection = MM_connTMS_STRING
    rsCategoriesurl_cmd.CommandText = "SELECT * FROM themensstore.prodcategory WHERE CatID = rsCategoriesurl__MMColParam"  
    rsCategoriesurl_cmd.Prepared = true
    rsCategoriesurl_cmd.Parameters.Append rsCategoriesurl_cmd.CreateParameter("param1", 5, 1, -1, rsCategoriesurl__MMColParam) ' adDouble
    Set rsCategoriesurl = rsCategoriesurl_cmd.Execute
    rsCategoriesurl_numRows = 0
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    Repeat1__numRows = -1
    Repeat1__index = 0
    rsCategories_numRows = rsCategories_numRows + Repeat1__numRows
    %>
    <%
    Dim Repeat2__numRows
    Dim Repeat2__index
    Repeat2__numRows = 20
    Repeat2__index = 0
    rsCategoryProducts_numRows = rsCategoryProducts_numRows + Repeat2__numRows
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="Templates/main.dwt.asp" codeOutsideHTMLIsLocked="false" -->
    <head> 
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <!-- InstanceBeginEditable name="doctitle" -->
    <title>Male Grooming : Mens Grooming : The Men's Store : Glasgow</title>
    <!-- InstanceEndEditable -->
    <!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
    <style type="text/css" media="screen">
    <!--
    @import url("_css/style.css");
    -->
    </style>
    </head>
    <body>
    <div id="wrapper"><div id="topheader"><!-- #BeginLibraryItem "/Library/very_top_header.lbi" --><img src="_img/header.jpg" alt="male grooming by the men's store - header image" width="776" height="185" border="0" usemap="#Map" />
    <map name="Map" id="Map">
    <area shape="rect" coords="602,34,623,180" href="#" alt="go to checkout" />
    <area shape="rect" coords="639,34,659,139" href="#" alt="go to shopping basket" />
    <area shape="rect" coords="677,34,696,162" href="#" alt="members section" />
    <area shape="rect" coords="713,32,734,166" href="#" alt="new member registration" />
    </map><!-- #EndLibraryItem --><map name="Map" id="Map"><area shape="rect" coords="597,34,620,180" href="#" /><area shape="rect" coords="636,34,656,138" href="#" /><area shape="rect" coords="674,34,694,162" href="#" /><area shape="rect" coords="711,34,731,164" href="#" /></map></div>
      <div id="header">
        <div id="nav"><!-- #BeginLibraryItem "/Library/main_nav.lbi" -->
    <a href="#">Home</a> | <a href="#">Glasgow Store</a> | <a href="#">Debenhams Glasgow</a> |&nbsp;<a href="#">About Us</a>&nbsp;| <a href="#">Promotions</a><!-- #EndLibraryItem --></div>
      </div>
      <div id="main-content">
        <div id="left-column">
          <div id="logo"><span class="logotxt1">UK Orderline 0141 331 2844</span><br />
          <span class="logotxt2" style="margin-left:15px;">search for 
          <label>
          <input name="search" type="text" id="search" />
          </label>
          </span>
          <label>
          <input name="search" type="submit" class="button" id="search" value="@ The Men's Store" />
          </label>
          </div>
          <!-- InstanceBeginEditable name="content" -->
          <h2><%=(rsCategories.Fields.Item("CatName").Value)%></h2>
       
          <% 
    While ((Repeat2__numRows <> 0) AND (NOT rsCategoryProducts.EOF)) 
    %>
            <div class="categories">
              <h3><%=(rsCategoryProducts.Fields.Item("brandName").Value)%> - <%=(rsCategoryProducts.Fields.Item("ProdName").Value)%></h3>
              <p><img align="left" alt="<%=(rsCategoryProducts.Fields.Item("ProdThumbAlt").Value)%>" src="<%=(rsCategoryProducts.Fields.Item("ProdThumb").Value)%>" /></p>
              <p><%=(rsCategoryProducts.Fields.Item("ProdShortDesc").Value)%></p>
              <p class="price"><%=(rsCategoryProducts.Fields.Item("ProdRRP").Value)%></p>
            </div>
            <% 
      Repeat2__index=Repeat2__index+1
      Repeat2__numRows=Repeat2__numRows-1
      rsCategoryProducts.MoveNext()
    Wend
    %>
    <!-- InstanceEndEditable --></div>
        <div id="right-column">
          <div class="sidebar">
                    <h3>Contents</h3>
            <div class="box">
              <ul>
                <% 
    While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF)) 
    %>
                  <li><a href="category.asp?CatID1=<%=(rsCategories.Fields.Item("CatID").Value)%>"><%=(rsCategories.Fields.Item("CatName").Value)%></a></li>
                  <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      rsCategories.MoveNext()
    Wend
    %>
              </ul>
            </div><h3>Customer Services</h3>
            <div class="box"><!-- #BeginLibraryItem "/Library/customer_services_nav.lbi" -->
    <ul>
      <li><a href="#">Customer Services</a></li>
      <li><a href="#">Delivery Rates</a></li>
      <li><a href="#">Sale of Goods Act</a></li>
      <li><a href="#">Customer Testimonials</a> </li>
    </ul>
    <!-- #EndLibraryItem --></div>
          </div>
        </div>
      </div>
      <div id="footer">Copyright &copy; 2007 The Men's Store, All rights reserved | <a href="#">Terms and Conditions</a> | <a href="#">Privacy Policy</a> </div>
    </div>
    
    </body>
    <!-- InstanceEnd --></html>
    <%
    rsCategories.Close()
    Set rsCategories = Nothing
    %>
    <%
    rsCategoryProducts.Close()
    Set rsCategoryProducts = Nothing
    %>
    <%
    rsCategoriesurl.Close()
    Set rsCategoriesurl = Nothing
    %>
    


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •