How to call Stored Procedure in ASP.NET

[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

[Igor Soukhov]

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?

[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

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

[Igor Soukhov]

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:

 
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.

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

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

[haifriends]

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

[JetDeveloper]

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.

[Igor Soukhov]

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.

[haifriends]

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

[ITGURU]

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.

[suzett]

see the full source code example

http://asp.net-informations.com/dbpr...-parameter.htm

suz.