|
 |
asp_databases thread: Error in Stored Procedure - ASP
Message #1 by "N.Rajesh" <narasimh@v...> on Wed, 25 Apr 2001 02:14:37
|
|
Hi List
I have created a stored procedure as follows
--------------------------------------------------------------------------
-
----------
create procedure sp_mema
@mempno varchar(20)
as
select lname, job_id from employee
where emp_id = @mempno
--------------------------------------------------------------------------
-
---------------
i call it in a file called "new.asp". The content of new.asp is as
follows
--------------------------------------------------------------------------
-
---------------------
<%@ language="vbscript" %>
<%
emp_id=Request.Form("empno")
%>
<html>
<head>
<title>Employee Details</title>
<style>
b {color:"000099";font-family:arial;font-size:10pt}
</style>
<script language="javascript">
<!--
function vChange(){
document.emp.submit()
}
-->
</script>
</head>
<%
Set conn = Server.CreateObject("ADODB.Connection")
set rs = server.CreateObject ("adodb.recordset")
conn.Open "Provider=SQLOLEDB.1;persist security info=false;user id=sa;
initial catalog=pubs"
Response.Write "Connection made"
rs.Open "select emp_id from employee",conn,adOpenDynamic
%>
<body>
<form name="emp" action="new1.asp" method="post">
<input type=hidden name="hdntxt" >
<table border=0 width="100%" bgcolor="#ccccff">
<td colspan=2 bgcolor="#330099" align=left><b><font
color="white">Employee
Details</font></b></td>
<tr>
<td width=40% align=right> <b>Select the Emp.No : </b>
</td>
<%
set rst = server.CreateObject ("adodb.recordset")
rst = conn.Execute ("EXEC sp_mema '" & empno & "'", ,adcmdtext)
emp_id=Request.Form("empno")
%>
<td>
<b><Select name="empno" onchange="vChange()" >
<% do while not rs.eof %>
<% if emp_id=rs("emp_id") then%>
<option selected><%=rs("emp_id")%></option>
<%else%>
<option ><%=rs("emp_id")%></option>
<%end if%>
<%
rs.movenext
loop
%>
</Select>
</b>
</td>
<tr>
<td width=40% align=right><b><font face="arial" size=2>L.Name
:</font></b></td>
<td> <input type="text" name="txtlname" value=<%=rst("lname")%>
readonly></td>
<tr>
<td width=40% align=right><b><font face="arial" size=2>Job Id
</font></b></td>
<td> <input type="text" name="txtjobid" value=<%=rst("job_id")%>
readonly></td>
</table>
<br>
</form>
</body>
</html>
--------------------------------------------------------------------------
-
--------------------------------------------
It gives me the following error
ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted; the
operation requested by the application requires a current record.
Kindly let me know what has to be done and what coding has to be changed
With regards,
N.Rajesh
Message #2 by "Dallas Martin" <dmartin@z...> on Wed, 25 Apr 2001 08:09:21 -0400
|
|
are you using SQL70?
If so, change the stored procedure to
Create Procedure sp_mema
(@mempno varchar(20))
as
select lname, job_id from employee HOLDLOCK
where emp_id = @mempno
RETURN 0
Change the call to sp_mema
SET rst = conn.Execute ("EXEC sp_mema @mempno = " & empno)
This should work. (Assuimh emp_id is an integer).
Dallas
----- Original Message -----
From: "N.Rajesh" <narasimh@v...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, April 25, 2001 2:14 AM
Subject: [asp_databases] Error in Stored Procedure - ASP
> Hi List
>
> I have created a stored procedure as follows
>
> -------------------------------------------------------------------------
-
> -
> ----------
> create procedure sp_mema
> @mempno varchar(20)
> as
> select lname, job_id from employee
> where emp_id = @mempno
> -------------------------------------------------------------------------
-
> -
> ---------------
>
> i call it in a file called "new.asp". The content of new.asp is as
> follows
>
>
> -------------------------------------------------------------------------
-
> -
> ---------------------
> <%@ language="vbscript" %>
> <%
> emp_id=Request.Form("empno")
> %>
> <html>
> <head>
> <title>Employee Details</title>
> <style>
> b {color:"000099";font-family:arial;font-size:10pt}
> </style>
> <script language="javascript">
> <!--
> function vChange(){
> document.emp.submit()
> }
> -->
> </script>
> </head>
> <%
> Set conn = Server.CreateObject("ADODB.Connection")
> set rs = server.CreateObject ("adodb.recordset")
> conn.Open "Provider=SQLOLEDB.1;persist security info=false;user id=sa;
> initial catalog=pubs"
> Response.Write "Connection made"
> rs.Open "select emp_id from employee",conn,adOpenDynamic
> %>
> <body>
> <form name="emp" action="new1.asp" method="post">
> <input type=hidden name="hdntxt" >
> <table border=0 width="100%" bgcolor="#ccccff">
> <td colspan=2 bgcolor="#330099" align=left><b><font
> color="white">Employee
> Details</font></b></td>
> <tr>
> <td width=40% align=right> <b>Select the Emp.No : </b>
> </td>
> <%
> set rst = server.CreateObject ("adodb.recordset")
> rst = conn.Execute ("EXEC sp_mema '" & empno & "'", ,adcmdtext)
> emp_id=Request.Form("empno")
> %>
> <td>
> <b><Select name="empno" onchange="vChange()" >
> <% do while not rs.eof %>
> <% if emp_id=rs("emp_id") then%>
> <option selected><%=rs("emp_id")%></option>
> <%else%>
> <option ><%=rs("emp_id")%></option>
> <%end if%>
> <%
> rs.movenext
> loop
> %>
> </Select>
> </b>
> </td>
> <tr>
> <td width=40% align=right><b><font face="arial" size=2>L.Name
> :</font></b></td>
> <td> <input type="text" name="txtlname" value=<%=rst("lname")%>
> readonly></td>
> <tr>
> <td width=40% align=right><b><font face="arial" size=2>Job Id
> </font></b></td>
> <td> <input type="text" name="txtjobid" value=<%=rst("job_id")%>
> readonly></td>
> </table>
> <br>
> </form>
> </body>
> </html>
> -------------------------------------------------------------------------
-
> -
> --------------------------------------------
>
> It gives me the following error
>
> ADODB.Field error '80020009'
>
> Either BOF or EOF is True, or the current record has been deleted; the
> operation requested by the application requires a current record.
>
> Kindly let me know what has to be done and what coding has to be changed
>
> With regards,
> N.Rajesh
>
|
|
|
View Cart
