locked
Calling Stored Procedure in asp.net RRS feed

  • Question

  • User-2020794970 posted

    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!

    Wednesday, February 17, 2010 7:35 AM

All replies

  • User-1161841047 posted

    May be because you used both ExecuteNonQuery and ExecuteOracleNonQuery.

    try to avoid this row : spcmd.ExecuteNonQuery()

    Wednesday, February 17, 2010 8:13 AM
  • User233475489 posted

    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.


     

    Thursday, February 18, 2010 7:56 PM
  • User-2020794970 posted

    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}

     

    Friday, February 19, 2010 6:56 AM
  • User-1935546128 posted

    ...

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

     

     

    Can you please give us the exception?

    Friday, February 19, 2010 8:01 AM
  • User-2020794970 posted

    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.

    Friday, February 19, 2010 11:28 AM
  • User-1161841047 posted

    Try to add this code before con.Open;

     if (con.State == ConnectionState.Open)
            {
                con.Close();
            }

     if (con.State == ConnectionState.Open)
    
            {
    
                con.Close();
    
            }




    Friday, February 19, 2010 11:43 AM
  • User-1935546128 posted

    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;
            }
        }
    }


     

    Friday, February 19, 2010 11:44 AM
  • User-2020794970 posted

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

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

    Friday, February 19, 2010 12:39 PM
  • User-1935546128 posted

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

    Friday, February 19, 2010 12:46 PM
  • User-1161841047 posted

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

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

    Do you mean that occur when you check the connection state if it's opened with my previous suggestion?

    let us see the last code!

    Friday, February 19, 2010 1:30 PM
  • User233475489 posted

    I think I got the problem in your code. See the line below:

     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;


    First of all, you have not tested for the connection at all and the most important part is there is no realltion between your ORacleCommand and opened connection! Smile


    Try like this:

    if (con.State == ConnectionState.Closed)
    {
          dbConnection.Open();
     }
    
    OracleCommand spcmd = con.CreateCommand();
    spcmd.CommandType = CommandType.StoredProcedure;
    spcmd.CommandText = "YOUR-SP-NAME";
    
    //Now add the parameters as you like
    
    //Then execute it. 
    //Either use 
    //spcmd.ExecuteScalar();
    //OR 
    //spcmd.ExecuteNonQuery();


    Hope this helps.


    BTW, the code will be much more flexible if you use a connection state checking at the finaly block before closing the connection right away.



    Friday, February 19, 2010 10:13 PM
  • User-2020794970 posted
    I have modified the code once again, but now I am receiving another error message: Error --> The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Probably this line! C# - code {code} string str = "DD-MMM-YY"; DateTime dt = Convert.ToDateTime(str); spcmd.Parameters.Add("p_dob", OracleType.DateTime, 12, "txtDOB").Value = "txtDOB"; {code}
    Monday, February 22, 2010 1:31 PM
  • User233475489 posted

    First of all, you need to cast the text to DateTime and for that you need to use the ParseExact method.

    Modify your code like this

    OracleParameter parameter1 = new OracleParameter(); 
    //You can do these steps in the spcmd.Parameters.Add() also. 
    parameter1.ParameterName = "p_dob";
    parameter1.OracleType = OracleType.DateTime;
    
    if (txtDOB.Text.Trim().Length ==0)
                    {
                        parameter1.Value = DBNull.Value;
                    }
                    else
                    {
                        parameter1.Value = DateTime.ParseExact(txtDOB.Text.Trim(), "dd-MMM-yy", 
    					new System.Globalization.CultureInfo("en-US", true)); 
            	    //You can use dd/MM/yyyy also, I am using that format. 
    		    //Check for other formats too.
                    }
    
    spcmd.Parameters.Add(parameter1);


    --------------------------

    Don't forget to 'Mark as Answer' if the solution satisfies your need.

    Monday, February 22, 2010 9:30 PM
  • User-2020794970 posted

    Thanks for the code! Problem still not solved. Same error message. Forgot how to format the code part?

    Tuesday, February 23, 2010 9:09 AM
  • User233475489 posted

    Have you chekced if the text box contains valid date time or not? You have to first enter a valid date time and make a valid DateTime object out of it by using ParseExact method. Visit MSDN or Try Google for more details.

    If your DateTime object is correct then only the stored procedure will accept it as you have set the OracleType to DateTime.

    You can check the validity of your date time object using the TryParseExact Method also:

           
            DateTime testDateTime;
    
            if (DateTime.TryParseExact(txtDOB.Text.Trim(), "dd-MMM-yyyy", 
                               CultureInfo.InvariantCulture, DateTimeStyles.None, out testDateTime))
            {
                // testDateTime Contains Valid Date Time object;
            }else{
                 //txtDOB.Text does not contain a valid date time 
            }


    So check for the DateTime object creation first before executing the Stored Procedure code.

    The alternate way is to do it in Oracle Stored Procedure. Like, pass the date time as a string (VARCHAR2) in your procedure (like 'dd/mm/yyyy') and then in side the stored proedure make the string a date time with to_date function.

    Like:

    to_date('2010/02/22', 'yyyy/mm/dd') Or,
    to_date('02/22/2010', 'mm/dd/yyyy') Or,
    to_date('022210', 'MMDDYY') Or,
    to_date('20100222', 'yyyymmdd')

    Please let me know if this solution helps.

    Tuesday, February 23, 2010 12:30 PM
  • User233475489 posted

    Also, have you checked your code? The portion you have posted, definitely contains a lot of error!

    spcmd.Parameters.Add("p_dob", OracleType.DateTime, 12, <strike>"txtDOB"</strike>).Value =<strike> "txtDOB"</strike>

    The code should be:<strike>
    </strike>


    spcmd.Parameters.Add("p_dob", OracleType.DateTime, 8, "dob").Value =  DateTime.ParseExact(txtDOB.Text.Trim(),  "dd-MMM-yy",  
                                                                                                                        new System.Globalization.CultureInfo("en-US", true));
    

    Note: DateTime is 8 bytes.

    Also, very important point, make sure you are inserting the values in correct order in your table inside the Stored Procedure.

    Meaninng, if you are doing :


    INSERT INTO TABLE_NAME VALUES(@name, @dob, @val1, @val2) 


    Then please make sure that the columns are in order. Or else, you can specify the column order in your SQL itself:


    INSERT INTO TABLE_NAME(name, dob, col1, col2) VALUES(@name, @dob, @val1, @val2) 


    This is the most common mistake that we do in practical life :)


    Tuesday, February 23, 2010 12:53 PM
  • User-2020794970 posted

    Hi! Thanks for your help!

    I am still fighting with my application. The code has been modified and the parameters are in the order of the table columns. When testing the app, the following message appears: Failed to convert parameter value from a String to a Decimal There are some null value columns, but the message comes, even when all columns are filled out. The code that I want to insert is not being formatted?
    Friday, March 5, 2010 7:19 AM
  • User233475489 posted

    I found some discripency in your code.

     spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output;
    //Where is the value of the user_id??
    
                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;
    //I think you ave changed this already
    
                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;
    //You need to cast this to a number
     spcmd.Parameters.Add("p_prefix", OracleType.Number, 3, "prefix").Value = Convert.ToInt32(txtPrefix.Text.Trim());
    
                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;


    Please check this and let me know if this helps.

    Friday, March 5, 2010 8:44 AM
  • User-2020794970 posted

    I believe that the problem is the user_id. The user_id is generated through an Oracle sequence. So, when a new user is inserted then the user_id should be outputted. spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output; Funny thing, when I run the stored procedure from within Visual Studio 2010 RC, it works.
    Friday, March 5, 2010 2:33 PM
  • User-1960379965 posted

    It will be good if we can see that procedure...

    but

    try This code :


       1. protected void button1_click(object sender, EventArgs e)  
       2. {  
       3.     using (OracleConnection con = new OracleConnection("Data Source=DEV01;Persist Security Info=True;User;Password=xxx;Unicode=True"))  
       4.     {  
       5.         try  
       6.         {  
       7.             con.Open();  
       8.             OracleTransaction trans = con.BeginTransaction();  
       9.   
      10.             OracleCommand spcmd = new OracleCommand("NEW_USER",con);  
      11.             spcmd.CommandType = CommandType.StoredProcedure;  
      12.             spcmd.Transaction = trans;  
      13.             spcmd.Parameters.Add("user_id", OracleType.Number, 10, "user_id").Direction = ParameterDirection.Output;  
      14.             spcmd.Parameters.Add("p_username", OracleType.VarChar, 16, "username").Value = txtUsername.Text;  
      15.             spcmd.Parameters.Add("p_password", OracleType.VarChar, 16, "password").Value = txtPassword.Text;  
      16.             spcmd.Parameters.Add("p_salutation", OracleType.VarChar, 10, "salutation").Value = txtSalutation.Text;  
      17.             spcmd.Parameters.Add("p_academic_title", OracleType.VarChar, 20, "academic_title").Value = txtTitle.Text;  
      18.             spcmd.Parameters.Add("p_first_name", OracleType.VarChar, 70, "first_name").Value = txtFirstname.Text;  
      19.             spcmd.Parameters.Add("p_last_name", OracleType.VarChar, 70, "last_name").Value = txtLastname.Text;  
      20.             spcmd.Parameters.Add("p_dob", OracleType.DateTime,"dob").Value = Convert.ToDateTime(txtDOB.Text);  
      21.             spcmd.Parameters.Add("p_street_address", OracleType.VarChar, 70, "street_address").Value = txtStreet.Text;  
      22.             spcmd.Parameters.Add("p_company_name", OracleType.VarChar, 40, "company_name").Value = txtCompanyname.Text;  
      23.             spcmd.Parameters.Add("p_street_address_2", OracleType.VarChar, 70, "street_address_2").Value = txtCompanystreet.Text;  
      24.             spcmd.Parameters.Add("p_country", OracleType.VarChar, 50, "country").Value = txtCountry.Text;  
      25.             spcmd.Parameters.Add("p_postal_code", OracleType.VarChar, 10, "postal_code").Value = txtZip.Text;  
      26.             spcmd.Parameters.Add("p_city", OracleType.VarChar, 50, "city").Value = txtCity.Text;  
      27.             spcmd.Parameters.Add("p_email_address", OracleType.VarChar, 50, "email_address").Value = txtEmailaddress.Text;  
      28.             spcmd.Parameters.Add("p_country_code", OracleType.VarChar, 3, "country_code").Value = txtCountrycode.Text;  
      29.             spcmd.Parameters.Add("p_prefix", OracleType.Number, 3, "prefix").Value = Convert.ToInt32(txtPrefix.Text.Trim());  
      30.             spcmd.Parameters.Add("p_mobile_phone", OracleType.VarChar, 20, "mobile_phone").Value = txtMobilephone.Text;  
      31.             spcmd.Parameters.Add("p_home_phone", OracleType.VarChar, 20, "home_phone").Value = txtPhonenumber.Text;  
      32.             spcmd.Parameters.Add("p_language", OracleType.VarChar, 30, "language").Value = txtLanguage.Text;  
      33.             spcmd.Parameters.Add("p_newsletter", OracleType.Char, 1, "newsletter").Value = txtNewsletter.Text;  
      34.             spcmd.Parameters.Add("p_specials", OracleType.Char, 1, "specials").Value = txtSpecials.Text;  
      35.             spcmd.Parameters.Add("p_membership", OracleType.Char, 1, "membership").Value = txtMembership.Text;  
      36.             spcmd.Parameters.Add("p_remarks", OracleType.VarChar, 1000, "remarks").Value = txtRemarks.Text;  
      37.             spcmd.ExecuteNonQuery();  
      38.             trans.Commit();  
      39.               
      40.             lblMsg.Text = String.Format("Your ID = {0}", spcmd.Parameters["user_id"].Value));  
      41.         }  
      42.         catch (Exception ex)  
      43.         {  
      44.             lblMsg.Text = "Error --> " + ex.Message;  
      45.         }  
      46.     }  
      47. }  



    Wednesday, March 17, 2010 6:31 AM
  • User529939352 posted

    This should be a piece of cake but there's a few variables (so it's a little tricky to isolate the culprit).


    OK, you say you can execute the procedure from your SQL tool (sounds like the integrated VS Oracle plugin).  Great, that means you have an operational proc!  I know this is sort of a rehash, but I'm trying to cross things off :)

    By default Oracle attempts to bind the parameter names in the order that you add them to the command object, so if you're not sending them in the same order, that can cause a problem.  Part of my DAL that sets this behavior to allow a different order:


    oraComm.CommandType = System.Data.CommandType.StoredProcedure;


    oraComm.BindByName = true;




    (i.e, BindByName = true)


    Also note that there are some issues with numbers mapped to intrinsic Oracle types vs. .NET types.  I would do this:


    Remove the expected OUT param from the proc (and the matching code on the app side).  In other words, try to get just a clean, single insert working without fetching the output param.  Also where you're returning the rows affected.  Just a simple, clean insert (you can even do it without a transaction).  Just use the ExecNonQuery method.  In other words, back out everything and work forward - a simple insert with just username, and the userid.  If that works, then you can start adding back in params (and determine if it's a date conversion, output issue, etc.)

    Has your Try/Catch had an actual error get generated?  Heck, I'd remove them and let it blow up with a complete verbose error.  Anything with the ORA prefix is a on the DB side, otherwise it's probably a .NET issue.

    Thursday, March 18, 2010 4:11 PM
  • User-1960379965 posted

    To find solution you can try to run it Through SQLdataadapter Wizard and see exactly what details need to be entered then compare that to your code...

    Wednesday, March 24, 2010 6:21 AM
  • User-1011865089 posted

    Pls tel me about store procedure that you know ? where r u from ?

     

    Saturday, June 2, 2012 1:12 AM