How to call Stored Procedure in ASP.NET
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: How to call Stored Procedure in ASP.NET

  1. #1
    Join Date
    Dec 2005
    Posts
    53

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Sydney, Australia
    Posts
    1,909

    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?
    Best regards,
    Igor Sukhov

    www.sukhov.net

  3. #3
    Join Date
    Dec 2005
    Posts
    53

    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

  4. #4
    Join Date
    Feb 2001
    Location
    Sydney, Australia
    Posts
    1,909

    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
    Best regards,
    Igor Sukhov

    www.sukhov.net

  5. #5
    Join Date
    Dec 2005
    Posts
    53

    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

  6. #6
    Join Date
    Aug 2004
    Location
    Land of sunshine and June Gloom
    Posts
    171

    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.

  7. #7
    Join Date
    Feb 2001
    Location
    Sydney, Australia
    Posts
    1,909

    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.
    Last edited by Igor Soukhov; January 25th, 2006 at 10:41 PM.
    Best regards,
    Igor Sukhov

    www.sukhov.net

  8. #8
    Join Date
    Dec 2005
    Posts
    53

    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

  9. #9
    Join Date
    Apr 2002
    Location
    Haryana, India
    Posts
    198

    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.
    Enjoy,

    Gurdarshan Singh
    L.S.E. (Project Lead)
    InterGlobe Technologies Pvt. Ltd.
    Mobile #: 9891397798 (India)
    gurdarhan.singh@interglobetechnologies.com
    gurdarshan70@hotmail.com

    Always Think Positive whatever may be the Situation.

    Please rate my suggestion/response if you find it suitable or fulfill your requirement.

  10. #10
    Join Date
    Jul 2008
    Posts
    6

    Re: How to call Stored Procedure in ASP.NET

    see the full source code example

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

    suz.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center