• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

Create submenu based on page name

Hi Experts

I'm reusing the idea from netsmithcentral (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_22727207.html#a19589884) to try to create some menus depending on the page name.

The SQL for the script is:
SELECT tblStories.StoryID, tblStories.Menu_Item, tblStories.Title, tblStories.CategoryID, tblCategories.Category FROM tblStories INNER JOIN tblCategories ON tblStories.CategoryID = tblCategories.CategoryID WHERE tblStories.Menu_Item = True

and the code on the page is:
<%
      Dim pageName
      pageName = Request.ServerVariables("SCRIPT_NAME")
%>

<% If InStr(pageName, (rsMenu.Fields.Item("Category").Value)) > 0 Then %>
      <%=(rsMenu.Fields.Item("Category").Value)%><br />
<%
      While ((sideMenu__numRows <> 0) AND (NOT rsMenu.EOF))
%>
      <%=(rsMenu.Fields.Item("Title").Value)%><br />
<%
      sideMenu__index=sideMenu__index+1
      sideMenu__numRows=sideMenu__numRows-1
      rsMenu.MoveNext()
      Wend
%>
<% End If %>

My problem is that it's not working.

If I alter the line '<% If InStr(pageName, (rsMenu.Fields.Item("Category").Value)) > 0 Then %>' to '<% If InStr(pageName, "Stories") > 0 Then %>' (to reflect the page name is 'Stories.asp') the line '<%=(rsMenu.Fields.Item("Category").Value)%>' displays the last record in the table and the repeated region just lists all the records in the table.

How can I alter the code to:

a) reflect that '<%=(rsMenu.Fields.Item("Category").Value)%>' will display the correct category name based on the page filename without hard coding the page name into the code?

and

b) list only those records associated with the relevant category?

Many thanks in advance for your help.

Regards

Martin
0
Martin Cotterill
Asked:
Martin Cotterill
  • 8
  • 8
1 Solution
 
WMIFCommented:
your sql statement appears to be requesting all the info from your db.  the only filter you have is if the menu_item is true.  i would think that you need to feed some values about the requesting info to filter down your results.
0
 
Martin CotterillDabblerAuthor Commented:
Hi WMIF

I thought there might be a way to use Request.ServerVariables("SCRIPT_NAME") to get the page name then, using the returned value, use (rsMenu.Fields.Item("Category").Value) to look through the recordset and display all the records that matched.

Sort of, if Request.ServerVariables("SCRIPT_NAME") returned a value of '/myDirectory/myFile.asp' then if 'myFile' was in the 'Category' field, all records relating to 'myFile' would be displayed.

Does that make more sense?

Regards

Martin
0
 
WMIFCommented:
it does, thanks.  your plan sounds good, but the way you have executed it could use a little bit of work.  so lets do it!

i would start off stripping out the page name from the script_name variable.  we can use a instrrev() to find the first / from the right side.  then grab everything to the right of it.  then you can just replace off the ".asp" from the end.

pagename = Request.ServerVariables("SCRIPT_NAME")
pagename = right(pagename, instrrev(pagename, "/") - len(pagename))
pagename = replace(pagename, ".asp", "")


now that we have the pagename without all that other stuff, it can be used directly in the sql query.  

"SELECT tblStories.StoryID, tblStories.Menu_Item, tblStories.Title, tblStories.CategoryID, tblCategories.Category FROM tblStories INNER JOIN tblCategories ON tblStories.CategoryID = tblCategories.CategoryID WHERE tblStories.Menu_Item = True and tblCategories.Category = '" & pagename & "' "


this way you are getting only the data that you need from the database.  it should return your results much faster to you as well.  it also reduces the amount of time that your asp engine has to loop through those records looking for that data.  all around improvements!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Martin CotterillDabblerAuthor Commented:
Hi again WMIF

Many thanks for the reply.

I've made the alterations as you suggested but I'm getting an error.

It says 'Invalid procedure call or argument: 'right'' on line 92 which is 'pagename = right(pagename, instrrev(pagename, "/") - len(pagename))'.

I'm afraid I don't know enough ASP/VBScript to know how to correct the problem.

Regards

Martin
0
 
WMIFCommented:
ah, i put the values backwards.  we need to subtract the length of the string from the position of the slash.  how it is, we are getting a negative number, which is what is giving the error for the right() function.  this should fix that error.

pagename = right(pagename, len(pagename) - instrrev(pagename, "/"))
0
 
Martin CotterillDabblerAuthor Commented:
Hi W

Ok, that got rid of the 'right' problem but now I can't make the rest of it doesn't work.

When I run the page I get: 'Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.'. The line it references is '<% If InStr(pagename, (rsMenu.Fields.Item("Category").Value)) > 0 Then %>'.

I have tried various combinations, including stripping down the code and removing the 'Repeated Region' but then I get other errors. So I've put it back to the way it was and come to you for help.

Regards

Martin

0
 
WMIFCommented:
well, this isnt the cause of the error, but we dont need that line anymore because we know that any records returned are already equal to the pagename value that we gave the sql statement.

the error is returning because the recordset that we requested is empty.  let check out the sql query before it gets executed to make sure we have proper values.  you dont have the section of code that opens your recordset in the code you gave above, so hopefully you can understand my general code and what to modify.  you should have something that is somewhat similar to this, but without my added lines.

sqlstatement = "select ..."
response.write sqlstatement   '  add this line
response.end                           ' and this one too
recordset.open sqlstatement, connection

this is going to print out the sql statement to the webpage and then stop the execution of the page so we can view what printed out.  load the page with these changes and copy/paste, back here, the statement that writes to the page.
0
 
Martin CotterillDabblerAuthor Commented:
Hi WMIF

Here's the code that calls the SQL statement. This is generated automatically by Dreamweaver which is the package I use:

<%
Dim rsMenu
Dim rsMenu_cmd
Dim rsMenu_numRows

Set rsMenu_cmd = Server.CreateObject ("ADODB.Command")
rsMenu_cmd.ActiveConnection = MM_RedShift_STRING
rsMenu_cmd.CommandText = "SELECT tblStories.StoryID, tblStories.Menu_Item, tblStories.Title, tblStories.CategoryID, tblCategories.Category FROM tblStories INNER JOIN tblCategories ON tblStories.CategoryID = tblCategories.CategoryID WHERE tblStories.Menu_Item = True and tblCategories.Category = '" & pagename & "'"
rsMenu_cmd.Prepared = true

Set rsMenu = rsMenu_cmd.Execute
rsMenu_numRows = 0
%>

I added you parts to the code and this is what is printed out on screen:

SELECT tblStories.StoryID, tblStories.Menu_Item, tblStories.Title, tblStories.CategoryID, tblCategories.Category FROM tblStories INNER JOIN tblCategories ON tblStories.CategoryID = tblCategories.CategoryID WHERE tblStories.Menu_Item = True and tblCategories.Category = ''

Regards

Martin
0
 
WMIFCommented:
>>This is generated automatically by Dreamweaver

be careful how you throw that statement around on this site. ;)  some of the experts are VERY anti-dreamweaver.


did you catch the problem there in the sql statement?  we are looking for a category of '', which is giving us nothing back, as it should.  lets step back some more in the code and figure out where the problem is.  use the same technique to print out values while we are building that pagename value.

pagename = Request.ServerVariables("SCRIPT_NAME")
response.write "step 1: " & pagename & "<br>"
pagename = right(pagename, len(pagename) - instrrev(pagename, "/"))
response.write "step 2: " & pagename & "<br>"
pagename = replace(pagename, ".asp", "")
response.write "step 3: " & pagename & "<br>"
response.end


unfortunately working with asp doesnt give us any formal debugging console, so we have to work with what weve got.  run that and lets check out what comes back from it.
0
 
Martin CotterillDabblerAuthor Commented:
Hello again WMIF

>> some of the experts are VERY anti-dreamweaver.

I can understand why. When I used to code my HTML by hand I didn't like (and still don't) the way Dreamweaver bloats the code for simple tasks. ie: rollovers, etc. I learnt to use it so I could create database driven sites.

Any road up. Here's what comes back with your code. It is as it should be.

step 1: /redshift/Stories.asp
step 2: Stories.asp
step 3: Stories

Regards

Martin
0
 
WMIFCommented:
sorry for the delay.  the weeks that i teach are very limited in time because of the number of hours.  anyways...

so the variable is getting the correct value, but something is happening between this code and the code that builds the sql statement because that pagename is not showing up.  can you spot any part of the code that is touching that variable?
0
 
Martin CotterillDabblerAuthor Commented:
Hi W

No worries about the delay! Please take as much time as you need, I know what it's like. The day job comes first.

OK. I found the problem and it wasn't your code. I'm afraid it was me.

I had put the 'pagename...' coding in the body of the document.  Once I tried putting it into the head of the doc it showed up as I think it should have done.

So please accept my apologies for messing you around.

Now just to get the menus working.

Talk to you soon. Hope you have / had a good day.

Regards

Martin
0
 
WMIFCommented:
>>So please accept my apologies for messing you around.

dont worry about it.  you are here for the help, and i am here to help you.  sometimes helping someone find the problem is as easy as saying something very simple and triggering a thought in the asker's mind.  are you getting the results you want now?  let me know if you need some more help.
0
 
Martin CotterillDabblerAuthor Commented:
Hi again WMIF

Many thanks for your reply.

I've been playing around most of today and I've now got it sorted.

As I've got two pages where the names are 'hard coded' I was able to use the 'pagename' variable to check if it was that page, so using an 'If... Then... Else' statement the correct menu shows up depending on the page/category.

Couldn't have done this without your excellent help and if I put your code in the right place, it would have been sorted earlier.

Once agian, many, many thanks for your help and patience.

Warm regards

Martin
0
 
WMIFCommented:
i am glad to hear this.  thanks for the points.
0
 
Martin CotterillDabblerAuthor Commented:
My pleasure W.

Once again, many thanks for your time and help.

Warm regards

Martin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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