Avoid Errors Procedure

macduff · #1 (**permalink**) 09-10-11, 09:44

Please help. I have a simple bit of code (below) that produces exactly what I want, i.e. output in tabular form that keeps data together and avoids errors. What I want to do, is use it as a procedure and call several instances of it on the same page, so populating a webpage table. Can this be done by calling a new instance of one parameter? My working code is...

Code:

<%
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("bling.mdb")
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
 
    DIM mySQL
    mySQL = "SELECT caption, piece, price, image FROM goodies WHERE catnum = 4"   

    DIM objRS
	Set objRS = Server.CreateObject("ADODB.Recordset")
	
	objRS.Open mySQL, objConn


response.write("<table class='tool' width='200px'>")

response.write("<tr><td colspan='2'>" & "<img src='")
response.write objRS("image")
response.write("'>" & "</td></tr>")

response.write("<tr><td colspan='2'>")
response.write objRS("caption")
response.write("</td></tr>")

response.write("<tr><td style='text-align:left;'>")
response.write objRS("piece")
response.write("</td><td style='text-align:right;'>" & "&pound;")
response.write objRS("price")
response.write("</td></tr>")

response.write("</table>")
%>

And I want to call instances something like

Code:

 <%call 'procedure' WHERE catnum='6'%>

I have searched high and low but cannot find an answer. Lots of full explanations that are way above my experience/knowledge. But I get lost just telling the difference between types of sql.

rokslide · #2 (**permalink**) 09-11-11, 20:18

so something like this??

Code:

<%
call getCatItem(4)
call getCatItem(6)

sub getCatItem(catId)
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("bling.mdb")
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
 
    DIM mySQL
    mySQL = "SELECT caption, piece, price, image FROM goodies WHERE catnum = " + catID  

    DIM objRS
    Set objRS = Server.CreateObject("ADODB.Recordset")
	
    objRS.Open mySQL, objConn


    response.write("<table class='tool' width='200px'>")

    response.write("<tr><td colspan='2'>" & "<img src='")
    response.write objRS("image")
    response.write("'>" & "</td></tr>")

    response.write("<tr><td colspan='2'>")
    response.write objRS("caption")
    response.write("</td></tr>")

    response.write("<tr><td style='text-align:left;'>")
    response.write objRS("piece")
    response.write("</td><td style='text-align:right;'>" & "&pound;")
    response.write objRS("price")
    response.write("</td></tr>")

    response.write("</table>")
end sub
%>

You would want to tidy up your recordsets and database connections etc but essentially that would be it

macduff · #3 (**permalink**) 09-12-11, 03:50

Thanks Rokslide for helping with this although having employed your method I get 'Internal Server Error'.

Also, wht did you mean by 'clean up' recordsets and connections?

rokslide · #4 (**permalink**) 09-12-11, 16:37

Have you got any more details on that internal server error such as what line it occurs on?

For cleaning up recordsets and connections, at the end of the sub routine you should be setting them to nothing eg

Code:

    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    <do some stuff>
    objConn.Close()
    objConn = nothing

This way you will release the connection cleanly and tidy up any memory that is being used.
Not sure if that syntax is 100% correct though.

macduff · #5 (**permalink**) 09-12-11, 17:41

Thankyou for the clarification Rokslide. I do close connections and set recordsets to nothing in the actual page but thanks for reminding me.

With respect to the line where the problem lies, I don't know how to discover that. I have tried commenting out various lines and nothing removes the server error.

rokslide · #6 (**permalink**) 09-12-11, 17:54

Are you getting a http 500 error?

If you are and you are testing using IE go into Tools -> Options -> Advanced....

Have a look for the Show friendly HTTP error messages option and make sure it is unticked.

You could also try removing the call from the start of the sub routine call statement, it is not always needed.

macduff · #7 (**permalink**) 09-12-11, 18:34

Hi Rokslide. It is a 500 Internal Server Error. I have done as you suggested and unticked the friendly errors option, applied etc..
I have also removed the calls from inside the sub procedure.
Still no joy and still get the error.

rokslide · #8 (**permalink**) 09-12-11, 19:13

Are you debugging locally or is the code running on the server? Sometimes your server will feed http 500's if you are now browsing locally.

For the commenting/debugging...

Comment out the call to the subroutine and see what happens. If you still get a http 500 it must be something in the structure of the sub routine and if you post verbatim what you have I will review and see if I can spot the error.

macduff · #9 (**permalink**) 09-13-11, 16:22

Hi Rokslide. I do appreciate your help but am afraid that I
have grown despondent after todays frustrating tests.

Firstly, let me assure you that I load each attempt up to my
website so as to get server delivered results.

Now to explain my melancholy. I noticed that one simpler page
delivered the correct result while very similar code in another
page delivered a 500 server error. To be absolutely certain, I
saved the 'successful' page as the same file name as the other,
'unsuccessful' page and it still did not deliver successfully
from the server.

What am I to make of that? Two different pages, identical content,
one delivers correctly, the other does not. I see no point
continuing until this conundrum is solved. If it is permitted and
you would like to check these pages yourself, I can post the URLs.

rokslide · #10 (**permalink**) 09-13-11, 16:50

Not sure if posting the URL's will help, I won't be able to see the code behind those URL's, just the results, but if you want to post all the same and I will see if I can spot anything.

You say you took a working page, renamed it to the name of the non working page and it stopped working, is that correct? If so can you confirm....

1. What at the names of the two files.
2. Are they on the same server
3. Are they on the same site and/or virtual directory on the server
4. Are there any include files that they use

These would be the key things that would mean a working page suddenly doesn't work when you rename it...

macduff · #11 (**permalink**) 09-13-11, 18:10

Hi. The two files are absolutely identical in content, do not include
other files or objects, one called dbforum.asp and the other index2.asp
They both use the same database bling.mdb and all three items reside in
the same folder, in the same website.

The entire content of each file is

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%
    DIM objConn
    Set objConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("bling.mdb")
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
 
    DIM mySQL
    mySQL = "SELECT caption, object, price, image FROM goodies WHERE catnum = 4"   

    DIM objRS
	Set objRS = Server.CreateObject("ADODB.Recordset")
	
	objRS.Open mySQL, objConn
  %>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
table.tool  {
    border:1px;
	border-style:solid;
	border-color:#0000ff;
	}
table.tool tr td {
    border:0px;
	border-style:solid;
	border-color:green;
	}
table.tool td  {
    padding:10px;
    font-family:arial;
	font-size:8pt;
	color:#0000ff;
	}
</style>
</head>

<body>
<%
response.write("<table class='tool' width='200px'>")

response.write("<tr><td colspan='2'>" & "<img src='")
response.write objRS("image")
response.write("'>" & "</td></tr>")

response.write("<tr><td colspan='2'>")
response.write objRS("caption")
response.write("</td></tr>")

response.write("<tr><td style='text-align:left;'>")
response.write objRS("object")
response.write("</td><td style='text-align:right;'>" & "&pound;")
response.write objRS("price")
response.write("</td></tr>")

response.write("</table>")
%>
</body>

</html>
<%
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

The content of both is identical because I took index2.asp which
produces an image, caption, etc in a tabulated box, then re-saved
the file as dbforum.asp

Both loaded up onto the website, index2.asp works, dbforum.asp
produces '500 internal server error'

rokslide · #12 (**permalink**) 09-13-11, 18:50

Okie, now that is really weird....

It's not IE caching the results or something similar is it?

Can we try all the following in order...

Close down all instances of IE that you have open.
Open one instance and clear all the temporary files
Close IE
On the server open a command window and type iisreset (this will stop and restart IIS so if this is a production server beware)
Open IE and try to go to the dbforums.asp page

In theory those steps should clear any caching that might be going on....

macduff · #13 (**permalink**) 09-14-11, 07:24

Hi Rokslide,

I studied your reply and satisfied myself that it wasn't a cache problem. I cannot reset IIS because the main server is one from whom I rent several GBs of space and it would affect a lot of other clients websites. But I spoke to the Support team there and their suggestion was a possible 'permissions' issue with the mdb being in the same folder as the docs. I made a new folder to hold the mdb and changed the connection filepath to suit. Tested the offending files and all works well. The result is now that at least documents either turn good or bad on their merit and not on a permissions whim.
Knowing now, that I can properly test the server output, I tried again, your suggestion and the result is still 500 server error. This was the suggestion of using + catID etc.
I really don't know where to go from here. I continue to code my webages of stock items in the laborious fashion knowing full well that no matter how careful I am with the proof reading, I will eventually sell a clients piece for hundreds less than it is worth by a data-record slip-up.

I'll keep at it though because the righ result would be so cool. Thankyou for all your help.

rokslide · #14 (**permalink**) 09-14-11, 16:30

That's weird that one page would work and the other not simply because of the DB location... ah well, not to worry.

With the 500 error... change this...

Code:

    mySQL = "SELECT caption, piece, price, image FROM goodies WHERE catnum = " + catID

to this

Code:

    mySQL = "SELECT caption, piece, price, image FROM goodies WHERE catnum = " & catID

Basically this line is going to concatenate the base sql with the catID. The + means you need to convert it to a string first, the & will take care of this automatically.

macduff · #15 (**permalink**) 09-14-11, 18:47

Well here we are back on the treadmill of try, fail, try, fail.I think I need to investigate some methods of troubleshooting asp code because just commenting out different lines still leaves me in the dark.
I have of course tired the ampersand but still get 500 internal server error.
Could I be calling it incorrectly? I used

Code:

<% call getCatItem(3) %>

and I've tried commenting that out.

Internet Services

Web Development

Digital Marketing

Consumer Tech