ASP.NET

Calling Stored Procedure in asp.net RSS

23 replies

Last post Jun 02, 2012 05:12 AM by majarajaking

‹ Previous Thread|Next Thread ›

- Reply
Peter2010

Member

10 Points

38 Posts

Calling Stored Procedure in asp.net

Feb 17, 2010 11:35 AM|LINK

Hi!

I am trying to insert a row via button event that should call an Oracle Stored Procedure, but the insert never happens.

Please see code below:

C#-code

{code}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OracleClient;

using System.Data;

public partial class new_user : System.Web.UI.Page

{

    protected void button1_click(object sender, EventArgs e)

    {

        OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();

        b.UserID = "Test";

        b.Password = "xxx";

        b.DataSource = "ora11";

        OracleConnection con = new OracleConnection(b.ToString());

        try

        {

            con.Open();

            OracleCommand spcmd = new OracleCommand();

            spcmd.CommandType = CommandType.StoredProcedure;

            spcmd.CommandText = @"NEW_USER";

            spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output;

            spcmd.Parameters.Add("p_salutation", OracleType.VarChar, 10, "salutation").Value = txtSalutation.Text;

            spcmd.Parameters.Add("p_academic_title", OracleType.VarChar, 20, "academic_title").Value = txtTitle.Text;

            spcmd.Parameters.Add("p_first_name", OracleType.VarChar, 70, "first_name").Value = txtFirstname.Text;

            spcmd.Parameters.Add("p_last_name", OracleType.VarChar, 70, "last_name").Value = txtLastname.Text;

            spcmd.Parameters.Add("p_dob", OracleType.DateTime, 12, "dob").Value = txtDOB.Text;

            spcmd.Parameters.Add("p_language", OracleType.VarChar, 30, "language").Value = txtLanguage.Text;

            spcmd.Parameters.Add("p_country_code", OracleType.VarChar, 3, "country_code").Value = txtCountrycode.Text;

            spcmd.Parameters.Add("p_prefix", OracleType.Number, 3, "prefix").Value = txtPrefix.Text;

            spcmd.Parameters.Add("p_home_phone", OracleType.VarChar, 20, "home_phone").Value = txtPhonenumber.Text;

            spcmd.Parameters.Add("p_company_name", OracleType.VarChar, 40, "company_name").Value = txtCompanyname.Text;

            spcmd.Parameters.Add("p_street_address", OracleType.VarChar, 70, "street_address").Value = txtStreet.Text;

            spcmd.Parameters.Add("p_street_address_2", OracleType.VarChar, 70, "street_address_2").Value = txtCompanystreet.Text;

            spcmd.Parameters.Add("p_postal_code", OracleType.VarChar, 10, "postal_code").Value = txtZip.Text;

            spcmd.Parameters.Add("p_city", OracleType.VarChar, 50, "city").Value = txtCity.Text;

          spcmd.Parameters.Add("p_country", OracleType.VarChar, 50, "country").Value = txtCountry.Text;

            spcmd.Parameters.Add("p_mobile_phone", OracleType.VarChar, 20, "mobile_phone").Value = txtMobilephone.Text;

            spcmd.Parameters.Add("p_email_address", OracleType.VarChar, 50, "email_address").Value = txtEmailaddress.Text;

            spcmd.Parameters.Add("p_username", OracleType.VarChar, 16, "username").Value = txtUsername.Text;

            spcmd.Parameters.Add("p_password", OracleType.VarChar, 16, "password").Value = txtPassword.Text;

            spcmd.Parameters.Add("p_remarks", OracleType.VarChar, 1000, "remarks").Value = txtRemarks.Text;

            spcmd.ExecuteNonQuery();

            // now insert a new record and return user id

            OracleString rowId;

            con.Open();

            int rowsAffected = spcmd.ExecuteOracleNonQuery(out rowId);

            con.Close();

            // Object new_user = insSQL.ExecuteScalar();

            lblMsg.Text = String.Format("Rows affected: " + rowsAffected);

            lblMsg.Text = String.Format("Your ID = {0}", spcmd.Parameters["user_id"].Value);

        }

        catch (Exception ex)

        {

            lblMsg.Text = "Error --> " + ex.Message;

        }

        finally

        {

            con.Close();

        }

    }

}

{code}

Any help greatly appreciated!
- Moved by mbanavige on Feb 17, 2010 11:53 AM
- Reply
Yousef_Jadal...

Star

8812 Points

1502 Posts

Re: Calling Stored Procedure in asp.net

Feb 17, 2010 12:13 PM|LINK

May be because you used both ExecuteNonQuery and ExecuteOracleNonQuery.

try to avoid this row : spcmd.ExecuteNonQuery()

Welcome To My Blog
- Reply
ubk1004

Member

534 Points

117 Posts

Re: Calling Stored Procedure in asp.net

Feb 18, 2010 11:56 PM|LINK

First of all omit the line 'int rowsAffected = spcmd.ExecuteOracleNonQuery(out rowId);'

If you really need to see rows affected then try to pass another out put param and do the stuff in your Proc.

If the code does not work after this then you have to look at your insert query.
- Reply
Peter2010

Member

10 Points

38 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 10:56 AM|LINK

Hi!

Actually, I do not need the row count. So, I have removed that line. After testing the application again,

I noticed that the exception is being catched and outputed via lblMsg, but the insert is not working and the

user id is not returned. When I run the procedure within the Oracle DB and via Visual Studio, the insert works.

So, the SP is ok. Maybe my code is missing access to the Oracle Sequence for the user id?

Here is the latest version:

{code}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OracleClient;

using System.Data;

public partial class new_user : System.Web.UI.Page

{

    protected void button1_click(object sender, EventArgs e)

    {

        OracleConnection con = new OracleConnection("Data Source=DEV01;Persist Security Info=True;User;Password=xxx;Unicode=True");





        try

        {

            con.Open();

            OracleCommand spcmd = new OracleCommand("NEW_USER");

            spcmd.CommandType = CommandType.StoredProcedure;



            spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output;

            spcmd.Parameters.Add("p_username", OracleType.VarChar, 16, "username").Value = txtUsername.Text;

            spcmd.Parameters.Add("p_password", OracleType.VarChar, 16, "password").Value = txtPassword.Text;

            spcmd.Parameters.Add("p_salutation", OracleType.VarChar, 10, "salutation").Value = txtSalutation.Text;

            spcmd.Parameters.Add("p_academic_title", OracleType.VarChar, 20, "academic_title").Value = txtTitle.Text;

            spcmd.Parameters.Add("p_first_name", OracleType.VarChar, 70, "first_name").Value = txtFirstname.Text;

            spcmd.Parameters.Add("p_last_name", OracleType.VarChar, 70, "last_name").Value = txtLastname.Text;

            spcmd.Parameters.Add("p_dob", OracleType.DateTime, 12, "dob").Value = txtDOB.Text;

            spcmd.Parameters.Add("p_street_address", OracleType.VarChar, 70, "street_address").Value = txtStreet.Text;

            spcmd.Parameters.Add("p_company_name", OracleType.VarChar, 40, "company_name").Value = txtCompanyname.Text;

            spcmd.Parameters.Add("p_street_address_2", OracleType.VarChar, 70, "street_address_2").Value = txtCompanystreet.Text;

            spcmd.Parameters.Add("p_country", OracleType.VarChar, 50, "country").Value = txtCountry.Text;

            spcmd.Parameters.Add("p_postal_code", OracleType.VarChar, 10, "postal_code").Value = txtZip.Text;

            spcmd.Parameters.Add("p_city", OracleType.VarChar, 50, "city").Value = txtCity.Text;

            spcmd.Parameters.Add("p_email_address", OracleType.VarChar, 50, "email_address").Value = txtEmailaddress.Text;

            spcmd.Parameters.Add("p_country_code", OracleType.VarChar, 3, "country_code").Value = txtCountrycode.Text;

            spcmd.Parameters.Add("p_prefix", OracleType.Number, 3, "prefix").Value = txtPrefix.Text;

            spcmd.Parameters.Add("p_mobile_phone", OracleType.VarChar, 20, "mobile_phone").Value = txtMobilephone.Text;

            spcmd.Parameters.Add("p_home_phone", OracleType.VarChar, 20, "home_phone").Value = txtPhonenumber.Text;

            spcmd.Parameters.Add("p_language", OracleType.VarChar, 30, "language").Value = txtLanguage.Text;

            spcmd.Parameters.Add("p_newsletter", OracleType.Char, 1, "newsletter").Value = txtNewsletter.Text;

            spcmd.Parameters.Add("p_specials", OracleType.Char, 1, "specials").Value = txtSpecials.Text;

            spcmd.Parameters.Add("p_membership", OracleType.Char, 1, "membership").Value = txtMembership.Text;

            spcmd.Parameters.Add("p_remarks", OracleType.VarChar, 1000, "remarks").Value = txtRemarks.Text;

            spcmd.ExecuteNonQuery();

            // now insert a new record and return user id

            lblMsg.Text = String.Format("Your ID = {0}", spcmd.Parameters["user_id"].Value));

        }

        catch (Exception ex)

        {

            lblMsg.Text = "Error --> " + ex.Message;

        }

        finally

        {

            con.Close();

        }

    }

}

{code}
- Reply
tpeczek

Contributor

2112 Points

260 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 12:01 PM|LINK

Peter2010

...

I noticed that the exception is being catched and outputed via lblMsg ...

Can you please give us the exception?

Please indicate "Mark as Answer" if a post has answered the question.
Yet another developer blog <-- visit my blog
- Reply
Peter2010

Member

10 Points

38 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 03:28 PM|LINK

For example: Error --> Connection to Oracle is still open. Connection must be closed! Or

Your ID = user_id

So, messages are being outputed, but no insert.
- Edited by Peter2010 on Feb 19, 2010 03:29 PM
- Edited by Peter2010 on Feb 19, 2010 03:30 PM
- Reply
Yousef_Jadal...

Star

8812 Points

1502 Posts
Re: Calling Stored Procedure in asp.net

Feb 19, 2010 03:43 PM|LINK
Try to add this code before con.Open;

<div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> if (con.State == ConnectionState.Open)</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> {</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> con.Close();</div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> }</div>
```
if (con.State == ConnectionState.Open)


        {


            con.Close();


        }
```
Welcome To My Blog

tpeczek

Contributor

2112 Points

260 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 03:44 PM|LINK

The problem migth be caused by lack of transaction (by default Oracle perform transaction rollback when connection is closed). Try this code:

protected void button1_click(object sender, EventArgs e)
{
    using (OracleConnection con = new OracleConnection("Data Source=DEV01;Persist Security Info=True;User;Password=xxx;Unicode=True"))
    {
        try
        {
            con.Open();
            OracleTransaction trans = con.BeginTransaction();


            OracleCommand spcmd = new OracleCommand("NEW_USER");
            spcmd.CommandType = CommandType.StoredProcedure;
            spcmd.Transaction = trans;
            spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output;
            spcmd.Parameters.Add("p_username", OracleType.VarChar, 16, "username").Value = txtUsername.Text;
            spcmd.Parameters.Add("p_password", OracleType.VarChar, 16, "password").Value = txtPassword.Text;
            spcmd.Parameters.Add("p_salutation", OracleType.VarChar, 10, "salutation").Value = txtSalutation.Text;
            spcmd.Parameters.Add("p_academic_title", OracleType.VarChar, 20, "academic_title").Value = txtTitle.Text;
            spcmd.Parameters.Add("p_first_name", OracleType.VarChar, 70, "first_name").Value = txtFirstname.Text;
            spcmd.Parameters.Add("p_last_name", OracleType.VarChar, 70, "last_name").Value = txtLastname.Text;
            spcmd.Parameters.Add("p_dob", OracleType.DateTime, 12, "dob").Value = txtDOB.Text;
            spcmd.Parameters.Add("p_street_address", OracleType.VarChar, 70, "street_address").Value = txtStreet.Text;
            spcmd.Parameters.Add("p_company_name", OracleType.VarChar, 40, "company_name").Value = txtCompanyname.Text;
            spcmd.Parameters.Add("p_street_address_2", OracleType.VarChar, 70, "street_address_2").Value = txtCompanystreet.Text;
            spcmd.Parameters.Add("p_country", OracleType.VarChar, 50, "country").Value = txtCountry.Text;
            spcmd.Parameters.Add("p_postal_code", OracleType.VarChar, 10, "postal_code").Value = txtZip.Text;
            spcmd.Parameters.Add("p_city", OracleType.VarChar, 50, "city").Value = txtCity.Text;
            spcmd.Parameters.Add("p_email_address", OracleType.VarChar, 50, "email_address").Value = txtEmailaddress.Text;
            spcmd.Parameters.Add("p_country_code", OracleType.VarChar, 3, "country_code").Value = txtCountrycode.Text;
            spcmd.Parameters.Add("p_prefix", OracleType.Number, 3, "prefix").Value = txtPrefix.Text;
            spcmd.Parameters.Add("p_mobile_phone", OracleType.VarChar, 20, "mobile_phone").Value = txtMobilephone.Text;
            spcmd.Parameters.Add("p_home_phone", OracleType.VarChar, 20, "home_phone").Value = txtPhonenumber.Text;
            spcmd.Parameters.Add("p_language", OracleType.VarChar, 30, "language").Value = txtLanguage.Text;
            spcmd.Parameters.Add("p_newsletter", OracleType.Char, 1, "newsletter").Value = txtNewsletter.Text;
            spcmd.Parameters.Add("p_specials", OracleType.Char, 1, "specials").Value = txtSpecials.Text;
            spcmd.Parameters.Add("p_membership", OracleType.Char, 1, "membership").Value = txtMembership.Text;
            spcmd.Parameters.Add("p_remarks", OracleType.VarChar, 1000, "remarks").Value = txtRemarks.Text;
            spcmd.ExecuteNonQuery();
            trans.Commit();
            

            lblMsg.Text = String.Format("Your ID = {0}", spcmd.Parameters["user_id"].Value));
        }
        catch (Exception ex)
        {
            lblMsg.Text = "Error --> " + ex.Message;
        }
    }
}

Please indicate "Mark as Answer" if a post has answered the question.
Yet another developer blog <-- visit my blog

- Reply
Peter2010

Member

10 Points

38 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 04:39 PM|LINK

I modified the code according to your suggestion, but now the message is saying:

Error --> Invalid operation. The connection is closed.
- Reply
tpeczek

Contributor

2112 Points

260 Posts

Re: Calling Stored Procedure in asp.net

Feb 19, 2010 04:46 PM|LINK

Can you debug your code and see on which line the exception is being thrown?

Please indicate "Mark as Answer" if a post has answered the question.
Yet another developer blog <-- visit my blog

Prev Next

1 2 3 Last

‹ Previous Thread|Next Thread ›

Calling Stored Procedure in asp.net RSS

23 replies

Peter2010

Calling Stored Procedure in asp.net

Yousef_Jadal...

Re: Calling Stored Procedure in asp.net

ubk1004

Re: Calling Stored Procedure in asp.net

Peter2010

Re: Calling Stored Procedure in asp.net

tpeczek

Re: Calling Stored Procedure in asp.net

Peter2010

Peter2010

Re: Calling Stored Procedure in asp.net

Yousef_Jadal...

Re: Calling Stored Procedure in asp.net

tpeczek

Re: Calling Stored Procedure in asp.net

Peter2010

Re: Calling Stored Procedure in asp.net

tpeczek

Re: Calling Stored Procedure in asp.net

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft