-
January 24th, 2006, 11:26 AM
#1
How to call Stored Procedure in ASP.NET
Hi everyone,
I've a Stored Procedure in Oracle which has one input parameter and one output parameter.
How to call this Stored procedure in ASP.NET?
Below is my coding. I get error.
Dim conn As New OleDbConnection(str)
conn.Open()
Dim cmd As New OleDbCommand("GetSalary", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OleDbParameter("empid", OleDbType.Integer))
cmd.Parameters("empid").Value = TextBox1.Text
cmd.Parameters.Add(New OleDbParameter("salary", OleDbType.Integer, 6))
cmd.Parameters("salary").Direction = ParameterDirection.Output
Dim sal As String
sal = cmd.Parameters("salary").Value
cmd.ExecuteReader()
conn.Close()
Am i wrong? Then how to execute it? Can anyone guide me.
-
haifriends
-
January 24th, 2006, 11:52 PM
#2
Re: How to call Stored Procedure in ASP.NET
Originally Posted by haifriends
Hi everyone,
I've a Stored Procedure in Oracle which has one input parameter and one output parameter.
How to call this Stored procedure in ASP.NET?
Below is my coding. I get error.
Dim conn As New OleDbConnection(str)
conn.Open()
Dim cmd As New OleDbCommand("GetSalary", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OleDbParameter("empid", OleDbType.Integer))
cmd.Parameters("empid").Value = TextBox1.Text
cmd.Parameters.Add(New OleDbParameter("salary", OleDbType.Integer, 6))
cmd.Parameters("salary").Direction = ParameterDirection.Output
Dim sal As String
sal = cmd.Parameters("salary").Value
cmd.ExecuteReader()
conn.Close()
Am i wrong? Then how to execute it? Can anyone guide me.
-
haifriends
What error are you getting?
-
January 25th, 2006, 04:21 AM
#3
Re: How to call Stored Procedure in ASP.NET
I get this error when i execute it in ASP.NET
ORA-0122: exact fetch returns more than requested number of rows
But my query returns only single row.
This is my SP
Code:
create or replace procedure GetSalary (empid in number, sal out number)
is
begin
select salary into sal from employees where empid=empid;
end;
Am i wrong in writing Stored Procedure? If i want the query to be like this, then how should i write stored procedure.
Please guide me in solving this problem
-haifriends
-
January 25th, 2006, 04:52 AM
#4
Re: How to call Stored Procedure in ASP.NET
From what I see this error has nothing to do with your ASP .NET code.
The error is in the body of stored procedure.
I'm not familiar with Oracle version of SQL but this line:
Code:
select salary into sal from employees where empid=empid;
could potentially fetch more than one value into sal
parameter which is not supported, because sal is a scalar variable.
Originally Posted by haifriends
I get this error when i execute it in ASP.NET
ORA-0122: exact fetch returns more than requested number of rows
But my query returns only single row.
This is my SP
Code:
create or replace procedure GetSalary (empid in number, sal out number)
is
begin
select salary into sal from employees where empid=empid;
end;
Am i wrong in writing Stored Procedure? If i want the query to be like this, then how should i write stored procedure.
Please guide me in solving this problem
-haifriends
-
January 25th, 2006, 05:04 AM
#5
Re: How to call Stored Procedure in ASP.NET
Originally Posted by Igor Soukhov
could potentially fetch more than one value into sal
parameter which is not supported, because sal is a scalar variable.
How to write a Stored Procedure for this?
It should return salary for the given employee.
-haifriends
-
January 25th, 2006, 11:20 AM
#6
Re: How to call Stored Procedure in ASP.NET
PHP Code:
create or replace procedure GetSalary (empid in number)
is
begin
select salary from employees where empid=empid;
end;
You don't need the output param at all. Use
cmd.ExecuteScalar()
to get the result.
-
January 25th, 2006, 11:36 PM
#7
Re: How to call Stored Procedure in ASP.NET
Originally Posted by JetDeveloper
PHP Code:
create or replace procedure GetSalary (empid in number)
is
begin
select salary from employees where empid=empid;
end;
You don't need the output param at all. Use
cmd.ExecuteScalar()
to get the result.
Thanks JetDeveloper. That looks like the proper solution for the stored procedure which designed to return one value.
Last edited by Igor Soukhov; January 25th, 2006 at 11:41 PM.
-
January 27th, 2006, 09:11 AM
#8
Re: How to call Stored Procedure in ASP.NET
Originally Posted by JetDeveloper
PHP Code:
create or replace procedure GetSalary (empid in number)
is
begin
select salary from employees where empid=empid;
end;
You don't need the output param at all. Use
cmd.ExecuteScalar()
to get the result.
Thank you.
I created procedure in the way you said, but i get an error
ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "GETSALARY
How to rectify this? Should i use cursor or anything else? Please guide me
-haifriends
-
January 31st, 2006, 05:27 AM
#9
Re: How to call Stored Procedure in ASP.NET
Dear haifriends,
In your code following two problem lies:
1. Your Query may be returning more than one record
2. You try to fetch value of out parameter before executing the ExecuteReader method of the command object.
To overcome the above problem change your Stored Procedure with following code
create or replace procedure GetSalary (empid in number)
is
begin
select salary from employees where empid=empid and ROWNUM <= 1;
end;
usage of ROWNUM in your query restrict number of rows returned by your query to one record only.
And Code with following lines of code:
Dim conn As New OleDbConnection(str)
conn.Open()
Dim cmd As New OleDbCommand("GetSalary", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OleDbParameter("empid", OleDbType.Integer))
cmd.Parameters("empid").Value = TextBox1.Text
Dim sal As String
sal = Convert.ToString(cmd.ExecuteScalar())
conn.Close()
Hope this will solve u r problem.
-
November 29th, 2011, 11:54 AM
#10
Re: How to call Stored Procedure in ASP.NET
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|