|
 |
access thread: ASP/Access stored procedure problem
Message #1 by "Gary Routh" <gary.routh2@v...> on Fri, 17 Jan 2003 17:40:27
|
|
Greetings,
I'm using examples from Kauffman's Beginning ASP Databases. Using his
examples and databases, the ASP pages work correctly. Using copies of his
pages, changing field names, etc. and my database, the connection to the
database works using the test page listed below. so it's not a connection
issue.
The problem is when I have an ASP page where the page is trying to use a
stored procedure in the Access database. The page never loads ( using PWS
on my desktop.) This page was copied from the author's DisplayBoats.asp
and only the field names were changed. His page works, mine does not. I'm
convinced there is some difference in how his database is configured.
Perhaps there are some permissions I need to set in my database? If so,
explicit instructions would help this newbie a lot!
Thanks!
Gary
The page that doesn't work is copied below the TEST ASP code.
______________________________
TEST ASP page
<%@Language=VBScript%>
-- text deleted --
<hr>
<p>Next line from ADO will work if a DSN named SOF has been set<br>
for the database which is named SOFIa.MDB.</p>
<%
set oRS=server.createobject("ADODB.recordset")
oRS.open "Select * from Members", "DSN=SOF"
oRS.movefirst
Do While Not oRS.EOF
response.write "<b>" & oRS("LName")   & "</b>"
oRS.MoveNext
Loop
%>
<hr>
Finished test page</p>
___________________________________
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DISPLAYSIRES.ASP
<!-- #include file="adovbs.inc" -->
<!-- #include file="AuthenticationCheck.inc" -->
<!-- #include file="ProductionErrorHandler.inc" -->
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>Sailors Web Site</TITLE>
</HEAD>
<BODY>
<!--Display the page data-->
<div align=center>
<big><big><font color=navy>Horses Case Study</font></big></big>
</div>
<br><br>
<!-- #include file="Connect.inc" -->
<%
'Check for database errors
Call CheckForErrors(objConn)
'Create the recordset object, set the SQL string and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "DistinctSireNums "
' objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc
objRs.Open strSQL, objConn,adOpenForwardOnly,adLockReadOnly,
adCmdStoredProc
'Check for database errors
Call CheckForErrors(objConn)
%>
<!--Build the table header rows-->
<table border=1 cellspacing=1>
<tr>
<td colspan=2 align=center>Registered Boats by Class</td>
</tr>
<tr>
<th bgcolor=navy><font color=white>Boat Name</font></th>
<th bgcolor=navy><font color=white>Boat Class</font></th>
</tr>
<%
'Set a variable to hold the last boatclass
Dim strLastClass
strLastClass = objRS("SireReg")
'Loop through the recordset displaying the last name field
Do While Not objRS.EOF
'If the current boat class is not equal to the last
'boat class then build a seperator row in the table
If objRS("SireReg") <> strLastClass Then
%>
<tr>
<td bgcolor=silver> </td>
<td bgcolor=silver> </td>
</tr>
<%
strLastClass = objRS("SireReg")
End If
%>
<!--Build a row of data in the table-->
<tr>
<td><%=objRS("SireNum")%></td>
<td><%=objRS("SireReg")%></td>
</tr>
<%
objRS.MoveNext
Loop
%>
<!--Build a seperator row in the table-->
<tr>
<td bgcolor=silver> </td>
<td bgcolor=silver> </td>
<tr>
<!--Build the last row of data with a hyper link to the options
page-->
<tr>
<td colspan=2><a href="Options.asp"
onmouseover="window.status='Return to Options Page'"
onmouseout="window.status=''">Return to Options
Page</a></td>
</tr>
</table>
<!-- #include file="Disconnect.inc" -->
</BODY>
</HTML>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #2 by "Haslett, Andrew" <andrew.haslett@i...> on Sun, 19 Jan 2003 07:51:16 +1030
|
|
Access doesn't have stored procedures. The only thing similar would perhaps
be stored queries.
That said, are you sure that this is the correct SQL string that you wish to
send to the database:
strSQL = "DistinctSireNums " ???
Cheers,
Andrew
-----Original Message-----
From: Gary Routh [mailto:gary.routh2@v...]
Sent: Saturday, 18 January 2003 4:10 AM
To: Access
Subject: [access] ASP/Access stored procedure problem
Greetings,
I'm using examples from Kauffman's Beginning ASP Databases. Using his
examples and databases, the ASP pages work correctly. Using copies of his
pages, changing field names, etc. and my database, the connection to the
database works using the test page listed below. so it's not a connection
issue.
The problem is when I have an ASP page where the page is trying to use a
stored procedure in the Access database. The page never loads ( using PWS
on my desktop.) This page was copied from the author's DisplayBoats.asp
and only the field names were changed. His page works, mine does not. I'm
convinced there is some difference in how his database is configured.
Perhaps there are some permissions I need to set in my database? If so,
explicit instructions would help this newbie a lot!
Thanks!
Gary
The page that doesn't work is copied below the TEST ASP code.
______________________________
TEST ASP page
<%@Language=VBScript%>
-- text deleted --
<hr>
<p>Next line from ADO will work if a DSN named SOF has been set<br>
for the database which is named SOFIa.MDB.</p>
<%
set oRS=server.createobject("ADODB.recordset")
oRS.open "Select * from Members", "DSN=SOF"
oRS.movefirst
Do While Not oRS.EOF
response.write "<b>" & oRS("LName")   & "</b>"
oRS.MoveNext
Loop
%>
<hr>
Finished test page</p>
___________________________________
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DISPLAYSIRES.ASP
<!-- #include file="adovbs.inc" -->
<!-- #include file="AuthenticationCheck.inc" -->
<!-- #include file="ProductionErrorHandler.inc" -->
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<TITLE>Sailors Web Site</TITLE>
</HEAD>
<BODY>
<!--Display the page data-->
<div align=center>
<big><big><font color=navy>Horses Case Study</font></big></big>
</div>
<br><br>
<!-- #include file="Connect.inc" -->
<%
'Check for database errors
Call CheckForErrors(objConn)
'Create the recordset object, set the SQL string and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "DistinctSireNums "
' objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc
objRs.Open strSQL, objConn,adOpenForwardOnly,adLockReadOnly,
adCmdStoredProc
'Check for database errors
Call CheckForErrors(objConn)
%>
<!--Build the table header rows-->
<table border=1 cellspacing=1>
<tr>
<td colspan=2 align=center>Registered Boats by Class</td>
</tr>
<tr>
<th bgcolor=navy><font color=white>Boat Name</font></th>
<th bgcolor=navy><font color=white>Boat Class</font></th>
</tr>
<%
'Set a variable to hold the last boatclass
Dim strLastClass
strLastClass = objRS("SireReg")
'Loop through the recordset displaying the last name field
Do While Not objRS.EOF
'If the current boat class is not equal to the last
'boat class then build a seperator row in the table
If objRS("SireReg") <> strLastClass Then
%>
<tr>
<td bgcolor=silver> </td>
<td bgcolor=silver> </td>
</tr>
<%
strLastClass = objRS("SireReg")
End If
%>
<!--Build a row of data in the table-->
<tr>
<td><%=objRS("SireNum")%></td>
<td><%=objRS("SireReg")%></td>
</tr>
<%
objRS.MoveNext
Loop
%>
<!--Build a seperator row in the table-->
<tr>
<td bgcolor=silver> </td>
<td bgcolor=silver> </td>
<tr>
<!--Build the last row of data with a hyper link to the options
page-->
<tr>
<td colspan=2><a href="Options.asp"
onmouseover="window.status='Return to Options Page'"
onmouseout="window.status=''">Return to Options
Page</a></td>
</tr>
</table>
<!-- #include file="Disconnect.inc" -->
</BODY>
</HTML>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.
Message #3 by "Gary Routh" <gary.routh2@v...> on Sat, 18 Jan 2003 22:38:15
|
|
> Greetings,
I> 'm using examples from Kauffman's Beginning ASP Databases. Using his
e> xamples and databases, the ASP pages work correctly. Using copies of
his
p> ages, changing field names, etc. and my database, the connection to the
d> atabase works using the test page listed below. so it's not a
connection
i> ssue.
> The problem is when I have an ASP page where the page is trying to use a
s> tored procedure in the Access database. The page never loads ( using
PWS
o> n my desktop.) This page was copied from the author's DisplayBoats.asp
a> nd only the field names were changed. His page works, mine does not.
I'm
c> onvinced there is some difference in how his database is configured.
P> erhaps there are some permissions I need to set in my database? If so,
e> xplicit instructions would help this newbie a lot!
T> hanks!
G> ary
T> he page that doesn't work is copied below the TEST ASP code.
_> _____________________________
T> EST ASP page
<> %@Language=VBScript%>
> -- text deleted --
<> hr>
<> p>Next line from ADO will work if a DSN named SOF has been set<br>
f> or the database which is named SOFIa.MDB.</p>
<> %
> set oRS=server.createobject("ADODB.recordset")
> oRS.open "Select * from Members", "DSN=SOF"
> oRS.movefirst
> Do While Not oRS.EOF
> response.write "<b>" & oRS("LName")   & "</b>"
> oRS.MoveNext
L> oop
%> >
<> hr>
F> inished test page</p>
_> __________________________________
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
D> ISPLAYSIRES.ASP
<> !-- #include file="adovbs.inc" -->
<> !-- #include file="AuthenticationCheck.inc" -->
<> !-- #include file="ProductionErrorHandler.inc" -->
> <HTML>
<> HEAD>
<> META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
<> TITLE>Sailors Web Site</TITLE>
<> /HEAD>
<> BODY>
> <!--Display the page data-->
<> div align=center>
> <big><big><font color=navy>Horses Case Study</font></big></big>
<> /div>
<> br><br>
> <!-- #include file="Connect.inc" -->
> <%
'> Check for database errors
C> all CheckForErrors(objConn)
> 'Create the recordset object, set the SQL string and open the recordset
S> et objRS = Server.CreateObject("ADODB.Recordset")
s> trSQL = "DistinctSireNums "
'> objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc
o> bjRs.Open strSQL, objConn,adOpenForwardOnly,adLockReadOnly,
a> dCmdStoredProc
>
'> Check for database errors
C> all CheckForErrors(objConn)
%> >
> <!--Build the table header rows-->
<> table border=1 cellspacing=1>
> <tr>
> <td colspan=2 align=center>Registered Boats by Class</td>
> </tr>
> <tr>
> <th bgcolor=navy><font color=white>Boat Name</font></th>
> <th bgcolor=navy><font color=white>Boat Class</font></th>
> </tr>
>
<> %
'> Set a variable to hold the last boatclass
D> im strLastClass
s> trLastClass = objRS("SireReg")
> 'Loop through the recordset displaying the last name field
D> o While Not objRS.EOF
> 'If the current boat class is not equal to the last
> 'boat class then build a seperator row in the table
> If objRS("SireReg") <> strLastClass Then
%> >
> <tr>
> <td bgcolor=silver> </td>
> <td bgcolor=silver> </td>
> </tr>
<> %
> strLastClass = objRS("SireReg")
> End If
%> >
> <!--Build a row of data in the table-->
> <tr>
> <td><%=objRS("SireNum")%></td>
> <td><%=objRS("SireReg")%></td>
> </tr>
<> %
> objRS.MoveNext
L> oop
%> >
> <!--Build a seperator row in the table-->
> <tr>
> <td bgcolor=silver> </td>
> <td bgcolor=silver> </td>
> <tr>
> <!--Build the last row of data with a hyper link to the options
p> age-->
> <tr>
> <td colspan=2><a href="Options.asp"
> onmouseover="window.status='Return to Options Page'"
> onmouseout="window.status=''">Return to Options
P> age</a></td>
> </tr>
> </table>
> <!-- #include file="Disconnect.inc" -->
> </BODY>
<> /HTML>
~> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Gary Routh" <gary.routh2@v...> on Sat, 18 Jan 2003 22:44:24
|
|
Andrew,
Thanks for your reply. I used that terminology because that's how queries
were referred to in the book. It is actually a query:SELECT DISTINCT
horses.SireReg, horses.SireNum FROM horses ORDER BY horses.SireReg,
horses.SireNum;
Whether I remove the trailing space in the strSQL = "DistinctSireNums " or
not has no effect. When I try to use other ASP pages with Access queries,
I have the same problem. That is why I think it must have something to do
with how my Access 2000 database is set up. In fact, if I write a SQL
SELECT string with more than one field, I get the same problem. But I can
list an individual field (or all the records for that one field) without
any difficulty. Does this help clarify what my problem is? Thanks again.
Message #5 by "Haslett, Andrew" <andrew.haslett@i...> on Mon, 20 Jan 2003 09:37:31 +1030
|
|
Is there an actual error message?
-----Original Message-----
From: Gary Routh [mailto:gary.routh2@v...]
Sent: Sunday, 19 January 2003 9:14 AM
To: Access
Subject: [access] Re: ASP/Access stored procedure problem
Andrew,
Thanks for your reply. I used that terminology because that's how queries
were referred to in the book. It is actually a query:SELECT DISTINCT
horses.SireReg, horses.SireNum FROM horses ORDER BY horses.SireReg,
horses.SireNum;
Whether I remove the trailing space in the strSQL = "DistinctSireNums " or
not has no effect. When I try to use other ASP pages with Access queries,
I have the same problem. That is why I think it must have something to do
with how my Access 2000 database is set up. In fact, if I write a SQL
SELECT string with more than one field, I get the same problem. But I can
list an individual field (or all the records for that one field) without
any difficulty. Does this help clarify what my problem is? Thanks again.
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.
Message #6 by "Gary Routh" <gary.routh2@v...> on Mon, 20 Jan 2003 00:23:20
|
|
You asked:
> Is there an actual error message?
Just a timeout html copied below:
Technical Information (for support personnel)
Error Type:
Active Server Pages, ASP 0113 (0x80004005)
The maximum amount of time for a script to execute was exceeded. You can
change this limit by specifying a new value for the property
Server.ScriptTimeout or by changing the value in the IIS administration
tools.
/DisplaySires.asp
Browser Type:
Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
Page:
GET /DisplaySires.asp
Time:
Sunday, January 19, 2003, 5:58:44 PM
~~~~~~~~~~~~~~~~~~~~~~~~
Again, thanks for helping me with this. I'm at wits end.
Gary
Message #7 by "Gary Routh" <gary.routh2@v...> on Mon, 20 Jan 2003 00:40:39
|
|
> Is there an actual error message?
addl info -- this change, putting the SQL in the objRS.Open instead of
assigning it to the strSQL variable works instantly and perfectly!!
THIS works:
'Create the recordset object, set the SQL string and open the recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT DISTINCT horses.SireReg, horses.SireNum FROM horses
ORDER BY horses.SireReg, Horses.SireNum;", "DSN=SOF"
objRS.movefirst
This does NOT work:
'strSQL = "DistinctSireNums"
'objRS.Open strSQL, objConn, adOpenForwardOnly, , adCmdStoredProc
-----Original Message-----
From: Gary Routh [mailto:gary.routh2@v...]
Sent: Sunday, 19 January 2003 9:14 AM
To: Access
Subject: [access] Re: ASP/Access stored procedure problem
Andrew,
Thanks for your reply. I used that terminology because that's how queries
were referred to in the book. It is actually a query:SELECT DISTINCT
horses.SireReg, horses.SireNum FROM horses ORDER BY horses.SireReg,
horses.SireNum;
Whether I remove the trailing space in the strSQL = "DistinctSireNums " or
not has no effect. When I try to use other ASP pages with Access queries,
I have the same problem. That is why I think it must have something to do
with how my Access 2000 database is set up. In fact, if I write a SQL
SELECT string with more than one field, I get the same problem. But I can
list an individual field (or all the records for that one field) without
any difficulty. Does this help clarify what my problem is? Thanks again.
IMPORTANT - PLEASE READ ********************
This email and any files transmitted with it are confidential and may
contain information protected by law from disclosure.
If you have received this message in error, please notify the sender
immediately and delete this email from your system.
No warranty is given that this email or files, if attached to this
email, are free from computer viruses or other defects. They
are provided on the basis the user assumes all responsibility for
loss, damage or consequence resulting directly or indirectly from
their use, whether caused by the negligence of the sender or not.
|
|
|
View Cart
