Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 5th, 2006, 07:59 PM
Registered User
 
Join Date: Feb 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL ASP Nightmare...

I know there is a simple answer to this but I seriously need help.

Basically I have a shopping site which has categories of different items. Fine, I can get the code to display a picture of each category and open up the category from each seperate picture, my problem is the sub categories.

For example, The category for clocks has sub categories for wall clocks and alarm clocks. Although the code opens up the sub categories when you click on the main clock picture, it also opens up ALL sub categories.

How do I get it to stop and only open the too clock subcats?

Here is the code:

Main category page, When you click on a product from the main page, this is the code to make it display categories with no sub catergories... wasn't sure about the If Then statements...

Code:
<%
Dim mySQL, myRS, CategoryID
CategoryID = CInt(Request.QueryString("CategoryID"))
mySQL = "SELECT ProductID, Image, Title, Price, Description " &_
"FROM Categories INNER JOIN Products " &_
"ON Categories.CategoryID = Products.CategoryID " &_
"WHERE Products.CategoryID = " & CategoryID & " " &_
"ORDER BY ProductID"

If CategoryID = "4" Then
  Response.Redirect("Clocks.asp") 
End If
If CategoryID = "7" Then
  Response.Redirect("Garden.asp")
End If
If CategoryID = "12" Then
  Response.Redirect("Kitchen.asp")
End If
If CategoryID = "19" Then
  Response.Redirect("Lighting.asp")
End If
If CategoryID = "22" Then
  Response.Redirect("Telephones.asp")
End If

Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, myConn

Do While Not myRS.EOF
  Response.Write RecToTable(myRS)
  myRS.MoveNext
Loop
Response.Write "</p>"  

myRS.Close
Set myRS = Nothing
%>
Then, so far I have been working on Clocks, so if a user clicks on the clock link they are redirected to clocks.asp which contains this code...

Code:
<%
Dim mySQL, myRS
Set myRS = Server.CreateObject("ADODB.Recordset")
mySQL = "SELECT Category2ID, Pic2, Category2 FROM Categories2 WHERE CatergoryID = "& CategoryID &""
myRS.Open mySQL, myConn

Do While Not myRS.EOF
  Response.Write RecToTable(myRS)
myRS.MoveNext
Loop
Response.Write "</p>"
myRS.Close
Set myRS = Nothing
%>
what am I doing wrong?

Please help. It's driving me nuts!

 
Old October 5th, 2006, 10:00 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Ok a couple of things.

First, you may want to change those ifs into a select case

SELECT CASE categoryID
  Case "4"
     Response.Redirect("clocks.asp")
  Case....
End Select

Also, i am not sure why you would want to redirect when 1) You have an open DB Connection and 2) your loop may or may not execute depending on the if. Always close your database connections before moving to a new page.

Now this is where things can get confusing.

You have categories and sub categories so that, in itself, is a relationship. Now please bare with me.

Lets say Clocks has a category of 4 and Wall Clocks has a sub category of 1 and Alarm Clocks a sub category of 2.

So your Table may look like this:

categoryID subcategoryID categoryLabel
4 0 Clocks
4 1 Wall Clocks
4 2 Alarm Clocks

So now here is what *I* would.

When you redirect do something like this clocks.asp?catID=<%=rs("categoryID")%>

On the clocks.asp page your sql query would be something like this to write links for the sub cat

SELECT categoryID, categoryLabel, subCategoryID from table where categoryID = " & CINT(Request.QueryString("catID") &" AND subCategoryID <> 0

you could then do this:

<a href="alarmclocks.asp?sCatID=<%=rs("subCategoryID" )%>&catID=<%=rs("CategoryID")%>"><%=rs("categoryLa bel")%></a>

Then on the corrosponding pages (I assume you have a table of images) add a category and subcategory column then do this:

SELECT image from table where categoryID = " & CINT(Request.QueryString("catID") &" AND subCategoryID = " & CINT(Request.QueryString("sCatID")

Also, to make management simpler, you may just want to put the acutal pagename in the database so your href could be something like this:

<a href="<%=rs("targetPage")%>?sCatID=<%=rs("subCateg oryID")%>&catID=<%=rs("CategoryID")%>"><%=rs("cate goryLabel")%></a>

Then you can dynamically write everything out!

hth

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Query Nightmare gregd SQL Server ASP 1 September 20th, 2006 05:33 PM
Template nightmare xgenerator Dreamweaver (all versions) 2 February 4th, 2005 03:24 PM
ASP.NET using Crytsal Reports printing nightmare badgolfer ASP.NET 1.0 and 1.1 Basics 0 October 25th, 2004 07:58 AM
Yet Another JavaScript Nightmare Ben Horne Javascript 3 October 15th, 2003 03:56 PM
Yet another insert nightmare. danielh Dreamweaver (all versions) 1 August 13th, 2003 09:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.