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
Re: How to call Stored Procedure in ASP.NET
Quote:
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?
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
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.
Quote:
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
Re: How to call Stored Procedure in ASP.NET
Quote:
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
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.
Re: How to call Stored Procedure in ASP.NET
Quote:
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.
Re: How to call Stored Procedure in ASP.NET
Quote:
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
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
Quote:
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:
Quote:
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.
Re: How to call Stored Procedure in ASP.NET
