Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Unanswered: can anyone help?

    I'm totally new to asp so any help with this would be greatly appreciated. I have a database table with around 8000 cards, at any one time there is about 5000 cards in stock, which means there is 3000 cards that are not in stock. The problem I have is that when a person search my web site all the cards that are'nt in stock show up in the search with the ones that are in stock, which is quite frustrating for people who don't have broadband.
    The search consists of three dropdown boxes that let the user use different criteria to search for the cards that they are looking for. What I thought about doing was using the "WHERE" statement to select item where "Units in Stock" was more than 1
    Code:
    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock = '>1';"
    But as you may have gathered this didn't work!
    Below is the original code and you can view the search at the following address http://www.themazecomicstore.com/htm..._gathering.asp
    Code:
    strQuery = "SELECT DISTINCT Description FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(2) = "<SELECT SIZE=1 NAME=colour>"
    	While NOT objRS.EOF
    		dropdown(2) = dropdown(2) & "<OPTION>" & objRS("Description") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(2) = dropdown(2) & "</SELECT>"
    
    	strQuery = "SELECT DISTINCT ProductType FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(1) = "<SELECT SIZE=1 NAME=producttype>"
    	While NOT objRS.EOF
    		dropdown(1) = dropdown(1) & "<OPTION>" & objRS("ProductType") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(1) = dropdown(1) & "</SELECT>"
    
    	strQuery = "SELECT DISTINCT ProductGroup FROM Cards;"
    	Set objRS = objConn.Execute(strQuery)
    	dropdown(0) = "<SELECT SIZE=1 NAME=productset>"
    	dropdown(0) = dropdown(0) & "<OPTION></OPTION>"
    	While NOT objRS.EOF
    		dropdown(0) = dropdown(0) & "<OPTION>" & objRS("ProductGroup") & "</OPTION>"
    		objRS.MoveNext
    	Wend
    	dropdown(0) = dropdown(0) & "</SELECT>"

  2. #2
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    Code:
    Code:
    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock = '>1';"
    no.
    that isnt going to work.
    this might
    Code:
    Code:
    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock > '1'"
    also.. that method of adding the string onto itslef is rather slow for your page to process...
    dropdown(2) = dropdown(2) & "<OPTION>" & objRS("Description")

    are you running this agains an Access database ?
    if so.. then consider migrating over to MS-SQL and using stored procedures for something like this..
    a LOT faster....
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  3. #3
    Join Date
    Aug 2005
    Posts
    31
    I'm beginning to think that the script might need a total over-haul, I tried a couple of different things including the code that you gave me, but they all fail to exclude out of stock items. I also tried adding an extra column to the database to say if an item was in stock or not, and created an extra dropdown menu which I could set on yes and hide, but this also didn't work.

    Code:
    strQuery = "SELECT DISTINCT Onsale FROM Cards;"
    I think that the problem might that there is too many options for the search, and that they are somehow comprimising each other. I hope this makes sense.
    Thanks for your input.

  4. #4
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85
    Access is probably fine for your needs unless you have a LOT of customers accessing your site. And if you're new to SQL you probably don't want to be messing around with stored procedures.

    Consider this bit of code that only displays the record if inventory is NOT zero:

    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock <> '0'"

    or in Access it might be written like this (can't recall off the top of my head -- sorry):

    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock != '0'"

  5. #5
    Join Date
    Mar 2006
    Location
    Boston, MA
    Posts
    3
    I agree that Access should be enough to handle your needs for a small website. You can always upsize to MS SQL later (and before making the big investment).

    One note on why the *where* query isn't working. What datatype is the quantity? In the table design view of Access, is it numeric, text? If it's numeric, then it should be something like this...

    strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock > 1"

    Notice I've removed the single quote from the criteria. '1' is a text string and may be holding an actual value of zero...hence it's interpreting it as >0, and everything is showing up!

    Also, you may have done this already but a good tactic (if you're novice in SQL as a language) is to draw the query using the QBE grid in Access. Then make sure the results show up as you want them. Next switch to the SQL view in Access (arrow next to the button in the upper left) of the query design screen. Then just copy and paste the code into your ASP page.

    Best of luck.

Posting Permissions

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