Go Back   Access World Forums > Apps and Windows > Web Design and Development > ASP and ASP.NET

Thread Tools Rate Thread Display Modes
Old 02-11-2006, 10:46 AM   #1
Registered User
Join Date: Aug 2005
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
mazer is on a distinguished road
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
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/htm..._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>"
	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>"
	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>"
	dropdown(0) = dropdown(0) & "</SELECT>"

mazer is offline   Reply With Quote
Old 02-11-2006, 12:57 PM   #2
"The Shoe"
Kodo's Avatar
Join Date: Jan 2004
Location: Syracuse NY
Posts: 707
Thanks: 0
Thanked 1 Time in 1 Post
Kodo is on a distinguished road
Send a message via MSN to Kodo
ok, the big problem here is that your database is not normalized. By this I mean that there is repetetive data in your cards table.
I'm assume your table is something like:

cardid (Primary Key)
cardtype (string)
producttype (string)
ProductGroup (string)
description (string)

All of those can be be broken down into their parts using a bridge entity.
Without knowing your business requirements, I mocked up a DB structure that is probably close to what you're looking for. It's my best guess in other words. Take note of the Relationship Diagram.

Remember, this is a rough guess.
Attached Files
File Type: zip cards.zip (12.1 KB, 120 views)
Message boards: The only place where multiple personality disorders are treated with a "welcome" post.
Kodo is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 10:49 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World