?
Solved

Create submenu based on page name

Posted on 2007-08-11
16
Medium Priority
?
588 Views
Last Modified: 2012-05-05
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
Comment
Question by:Martin Cotterill
  • 8
  • 8
16 Comments
 
LVL 22

Expert Comment

by:WMIF
ID: 19676054
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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19676549
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
 
LVL 22

Accepted Solution

by:
WMIF earned 2000 total points
ID: 19677153
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19677729
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19678177
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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19678563
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19679765
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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19680297
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19680816
>>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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19681851
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19705437
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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19706217
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19708998
>>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
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19710081
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
 
LVL 22

Expert Comment

by:WMIF
ID: 19710195
i am glad to hear this.  thanks for the points.
0
 
LVL 1

Author Comment

by:Martin Cotterill
ID: 19710711
My pleasure W.

Once again, many thanks for your time and help.

Warm regards

Martin
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question