...

View Full Version : can anyone help?



mazer
02-11-2006, 06:48 PM
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

strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock = '>1';"
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/html/magic_the_gathering.asp

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>"

BaldEagle
02-11-2006, 07:09 PM
// This is wrong
strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock = '>1';"
// this should work
strQuery = "SELECT DISTINCT Description FROM Cards WHERE UnitsinStock > 1;"

Only problem is that any card stock that has only 1 unit in stock will not be queried either so change it >= 1 or > 0 to get all in stock items.

BaldEagle

mazer
02-11-2006, 07:39 PM
I got an error when I used the code >= 1 & >0
but when I used >= '1' the search page loaded but it didn't exclude the items that aren't in stock. is there another way to do this without the WHERE statement?
Thanks for the help

BaldEagle
02-11-2006, 08:12 PM
I got an error when I used the code >= 1 & >0
but when I used >= '1' the search page loaded but it didn't exclude the items that aren't in stock. is there another way to do this without the WHERE statement?
Thanks for the help

Couple of things: What was the error? And what datatype is UnitsInStock?

BaldEagle

BaldEagle
02-11-2006, 09:07 PM
Using your SQL statement to filter your recordset is the most efficient and least coding option. However if you want to play around with recordset filtering then check out this little tutorial:

http://www.stardeveloper.com/articles/display.html?article=2000070701&page=1

BaldEagle

BaldEagle
02-12-2006, 08:34 PM
Just following up. Is this resolved?

BaldEagle

mazer
02-13-2006, 07:33 PM
I'm still trying to figure out how to use sql to sort and filter, it all new to me so it may take a while, thank for all the help, the tutorial was very helpful.

Swazi
02-13-2006, 07:34 PM
asp looks hard. you shud try php with me. its hard at 1st but looks like its alot easyer than asp. want to learn with me?

BaldEagle
02-13-2006, 07:54 PM
Ok. Good Luck.

BaldEagle



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum