Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Dropdowns (values from a database): Choice of category must correspond to subcategory

Hi

I have an access database (CMS system) with 3 tables:

1: tblCat
catID (pk, autonumber)
strCat (text)

2: tblSubCat
subCatID (pk, autonumber)
strSubCat (text)
intCatoNo (foreign key to catID from tblCat)

3: tblBody
bodyID (pk, autonumber)
intCatNo (foreign key to catID from tblCat)
intSubCat (foreign key to subCatID from tblSubCat)
strHead (text - headline)
strBody (memo)

I have created a system that lets users create news like this:
1: choose_cat.asp: Choose a category from a dropdown - and click on submit (form).
2: create_news.asp: A page where the chosen category is shown along with the matching subcategories

This works just fine!

My problem is:

How do I create an edit module?

My dream is to create an edit page where the choice of a category will change to the matching subcategories.

I could use just a clue because I'm at a loss here.

I have made an edit module - I can change the category but I don't know how to autoupdate the subcategories.

Is this totally weird?
0
jensjakobsen
Asked:
jensjakobsen
  • 4
  • 3
1 Solution
 
RouchieCommented:
Do you mean that when you make a change to the Categories menu, the sub-categories menu changes to show the appropriate values?
If you, so need to populate the subcategories menu using a select statement that references the categories menu, like this -

  SELECT DISTINCT subCatID from tblSubCat WHERE intCatoNo = <<integer parameter passed from asp>>

I don't know how you pass the values back to the database, but presuming it's via a post method, it would be like this:

  mySQLStatement = "SELECT DISTINCT subCatID from tblSubCat WHERE intCatoNo = " & CInt(request.form("categoryMenuFormItem"))
0
 
jensjakobsenAuthor Commented:
I have a HUGE HUGE problem - I think it's basic that I embarressed to ask for help here. The problem is related to my question:

I just can't figure out to pass an ID (catID) from the table tblCat (see below)

1: tblCat
catID (pk, autonumber) <-- THIS ONE!!!!!!
strCat (text)

I've created a separate page just to do a single dynamic jump menu form. The code is like this:
************************************************************************
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connAccessDB.asp" -->

<%
Dim RSCat
Dim RSCat_numRows

Set RSCat = Server.CreateObject("ADODB.Recordset")
RSCat.ActiveConnection = MM_connAccessDB_STRING
RSCat.Source = "SELECT * FROM tblCat ORDER BY intShow ASC"
RSCat.CursorType = 0
RSCat.CursorLocation = 2
RSCat.LockType = 1
RSCat.Open()

RSCat_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RSCat_numRows = RSCat_numRows + Repeat1__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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script type="text/JavaScript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
  if (restore) selObj.selectedIndex=0;
}
//-->
</script>
</head>

<body>
<form method="post" name="form1" id="form1">
<select name="category" id="category" onchange="MM_jumpMenu('parent',this,0)">
<%
While ((Repeat1__numRows <> 0) AND (NOT RSCat.EOF))
%>
<option value="get_jump.asp?intCatNo=<%=(RSCat.Fields.Item("catID").Value)%>"><%=(RSCat.Fields.Item("strCat").Value)%></option>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RSCat.MoveNext()
Wend
%>

</select>
</form>
</body>
</html>
<%
RSCat.Close()
Set RSCat = Nothing
%>
************************************************************************
Now when I try to catch the result from the jump menu I can't use:

request("category")
request.form("category")
request.querystring("category")

NADA!!!!!

I feel like a 100% fool here - please help me. I've spent my entire Sunday trying to catch the passed parameter - but it's not passing anything.

What am I doing wrong?

Any help would be deeply appreciated.
0
 
RouchieCommented:
What you've done here is completely correct, however, you've confused yourself 8-)

You are building the form so that when the loop runs, a link menu is generated which redirects the browser to the following URL

   .../get_jump.asp?intCatNo=XX

where XX is the number from the database passed originally.  Your problem is that you're then trying to get the querystring value in the next page by using

   request.querystring("category")

but remember you've crafted the querystring name manually to be ?intCatNo and not ?category

Therefore the correct way to retrieve what you're setting up is like this

<%
passedID = request.QueryString("intCatNo")
%>
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
jensjakobsenAuthor Commented:
OK - so - just to clarify (and YES I have confused myself):

I have made 2 files:

jump.asp (dropdown with jumpmenu) -> http://www.jensjakobsen.com/adminacc/jump.asp
get_jump.asp (resultpage) -> http://www.jensjakobsen.com/adminacc/get_jump.asp

The following code is from JUMP.ASP
************************************************************************************************
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connAccessDB.asp" -->

<%
Dim RSCat
Dim RSCat_numRows

Set RSCat = Server.CreateObject("ADODB.Recordset")
RSCat.ActiveConnection = MM_connAccessDB_STRING
RSCat.Source = "SELECT * FROM tblCat ORDER BY intShow ASC"
RSCat.CursorType = 0
RSCat.CursorLocation = 2
RSCat.LockType = 1
RSCat.Open()

RSCat_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RSCat_numRows = RSCat_numRows + Repeat1__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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script type="text/JavaScript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
  if (restore) selObj.selectedIndex=0;
}
//-->
</script>
</head>

<body>
<form method="post" name="form1" id="form1">
<select name="category" id="category" onchange="MM_jumpMenu('parent',this,0)">
<%
While ((Repeat1__numRows <> 0) AND (NOT RSCat.EOF))
%>
<option value="get_jump.asp?intCatNo=<%=(RSCat.Fields.Item("catID").Value)%>"><%=(RSCat.Fields.Item("strCat").Value)%></option>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RSCat.MoveNext()
Wend
%>

</select>
</form>
</body>
</html>
<%
RSCat.Close()
Set RSCat = Nothing
%>
************************************************************************************************




The following code is from GET_JUMP.ASP
************************************************************************************************
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connAccessDB.asp" -->

<%
Dim rsJumpResult__MMColParam
rsJumpResult__MMColParam = "1"
If (Request.QueryString("catID") <> "") Then
  rsJumpResult__MMColParam = Request.QueryString("catID")
End If
%>
<%
Dim rsJumpResult
Dim rsJumpResult_numRows

Set rsJumpResult = Server.CreateObject("ADODB.Recordset")
rsJumpResult.ActiveConnection = MM_connAccessDB_STRING
rsJumpResult.Source = "SELECT * FROM tblCat WHERE catID = " + Replace(rsJumpResult__MMColParam, "'", "''") + ""
rsJumpResult.CursorType = 0
rsJumpResult.CursorLocation = 2
rsJumpResult.LockType = 1
rsJumpResult.Open()

rsJumpResult_numRows = 0
%>
<%
Dim RSCat
Dim RSCat_numRows

Set RSCat = Server.CreateObject("ADODB.Recordset")
RSCat.ActiveConnection = MM_connAccessDB_STRING
RSCat.Source = "SELECT * FROM tblCat ORDER BY intShow ASC"
RSCat.CursorType = 0
RSCat.CursorLocation = 2
RSCat.LockType = 1
RSCat.Open()

RSCat_numRows = 0
%>
<%
Dim rsSubCat
Dim rsSubCat_numRows

Set rsSubCat = Server.CreateObject("ADODB.Recordset")
rsSubCat.ActiveConnection = MM_connAccessDB_STRING
'rsSubCat.Source = "SELECT * FROM tblSubCat"
rsSubCat.Source = "SELECT DISTINCT subCatID, strSubCat FROM tblSubCat WHERE intCatNo = " & CInt(Request.Querystring("category"))
rsSubCat.CursorType = 0
rsSubCat.CursorLocation = 2
rsSubCat.LockType = 1
rsSubCat.Open()

rsSubCat_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RSCat_numRows = RSCat_numRows + Repeat1__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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Get Jump</title>
<script type="text/JavaScript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
  if (restore) selObj.selectedIndex=0;
}
//-->
</script>
</head>

<body>
<a href="jump.asp">Jump</a><br />
<p>Resultatet af din jump menu var: <%=request.querystring("category")%> </p>
<form name="form1" id="form1">
      <p>
            <select name="category" id="category" onchange="MM_jumpMenu('parent',this,0)">
                  <%
            While ((Repeat1__numRows <> 0) AND (NOT RSCat.EOF))
            %>
                  <option value="get_jump.asp?catID=<%=(RSCat.Fields.Item("catID").Value)%>"><%=(RSCat.Fields.Item("strCat").Value)%></option>
                  <%
              Repeat1__index=Repeat1__index+1
              Repeat1__numRows=Repeat1__numRows-1
              RSCat.MoveNext()
            Wend
            %>
            </select>
</p>
      <p>
            <select name="subCat" id="subCat">
                  <%
While (NOT rsSubCat.EOF)
%><option value="<%=(rsSubCat.Fields.Item("subCatID").Value)%>"><%=(rsSubCat.Fields.Item("strSubCat").Value)%></option>
                  <%
  rsSubCat.MoveNext()
Wend
If (rsSubCat.CursorType > 0) Then
  rsSubCat.MoveFirst
Else
  rsSubCat.Requery
End If
%>
            </select>
</p>
</form>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>
<%
rsJumpResult.Close()
Set rsJumpResult = Nothing
%>
<%
RSCat.Close()
Set RSCat = Nothing
%>
<%
rsSubCat.Close()
Set rsSubCat = Nothing
%>
************************************************************************************************

I'm going mad :(
0
 
RouchieCommented:
I see the problem but I could do with sitting next to you and pointing it out!  On "jump.asp" you are looping through the database values and writing this out:

   <option value="get_jump.asp?intCatNo=<%=(RSCat.Fields.Item("catID").Value)%>"><%=(RSCat.Fields.Item("strCat").Value)%></option>

which when executed produces this (one of many):

   <option value="get_jump.asp?intCatNo=3">Dorthe</option>

When I click on Dorthe's name my browser jumps to the URL that you put in the first line above:

   http://www.jensjakobsen.com/adminacc/get_jump.asp?intCatNo=3



This all happens because you have manually called the query string value INTCATNO in your code:

   Your code:
   <option value="get_jump.asp?intCatNo=<%=(RSCat.Fields.Item("catID").Value)%>"><%=(RSCat.Fields.Item("strCat").Value)%></option>
                                               ^^^^^
   Outputs in browser as:
   option value="get_jump.asp?intCatNo=3">Dorthe</option>
                                             ^^^^^
   which jumps my browser to:
   http://www.jensjakobsen.com/adminacc/get_jump.asp?intCatNo=3
                                                                                   ^^^^^


Now, in the second page "get_jump.asp" , you are trying to read the querystring from the first page that has been fired off through the form.  
You have told ASP to look for the querystring called CATID

  rsJumpResult__MMColParam = Request.QueryString("catID")
                                                                               ^^^

but it's not actually called CATID because you have manually called it INTCATNO in the previous page.  Therefore ASP isn't seeing what you're telling it to see!  

So you need to change any reference of CATID to say INTCATNO instead!
0
 
jensjakobsenAuthor Commented:
Thank you again - will look into it tomorrow night :)
0
 
jensjakobsenAuthor Commented:
Working on it - very promising results so far :)

I'll finish it within days and get back if I have questions.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now