Click here to Skip to main content
15,921,467 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
i am using vs2010 and mysql database, i am passing the 9 values to stored procedure and i am writing code using sqltransactions ,

Admin.aspx.cs (code)

btnAddCusInvoice_Click

oInvInvoiceLineItems.CustomerID = ddlCustomerName.SelectedValue;
        oInvInvoiceLineItems.InvoiceID = Convert.ToInt32(txtCusInvNumber.Text);

        DataSet local_ds = null;
        string local_strStoredProcName;
        int count = 0;
        local_ds = new DataSet();
        oInvHeader.CustomerID = ddlCustomerName.SelectedValue;
        oInvHeader.InvoiceID = Convert.ToInt32(txtCusInvNumber.Text);
        oInvHeader.Flag = "N";
        local_strStoredProcName = "uspInvoiceFedTaxID";
        oInvInvoiceLineItems.Select(out local_ds, local_strStoredProcName, ddlCustomerName.SelectedValue);
        oInvHeader.FedTaxID = local_ds.Tables[0].Rows[0]["FedTaxID"].ToString();
        oInvHeader.PaymentDate = DateTime.Now;`if (txtMemo.Text != string.Empty)
    {
        oInvHeader.Memo = txtMemo.Text;
    }
    else
    {
        oInvHeader.Memo = "Null";
    }
    oInvHeader.DueDate = DateTime.Now;

oInvHeader.Amount = Amount;

        oInvInvoiceLineItems.TotalAmount = Amount; 

        MySqlTransaction oSqlTrans = null;

if (customer != null)

        {

            oInvInvoiceLineItems.Update(count, oSqlTrans);

        }

        else

        {

            oInvHeader.Add(oInvInvoiceLineItems, count, oSqlTrans);

        }`    



oInvHeader.cs(DAO)

write the code like this


C#
public bool Add(InvInvoiceLineItems oInvoiceLineItems,int count, MySqlTransaction oSqlTran)

    {
        m_bFlag = false;

        //always reset SqlTrans with connection object

        oSqlTran = null;

        MySqlConnection oConn = new MySqlConnection(Utilities.ConnectionString());


        string sTransName = "Invoice";

        if (oConn.State != ConnectionState.Open)
        {
            oConn.Open();
        }

        oSqlTran = oConn.BeginTransaction();

        //Always associate Connection with Transaction to begin with

        try
        {
            //Add InvoiceDetails table
            if (this.Add(oSqlTran, oConn))
            {

                //Add InvoiceHeader table
                if (oInvoiceLineItems.Add(oSqlTran,count ,oConn))
                {

                    oSqlTran.Commit();

                }


            }
        }
        catch (Exception ex)
        {
            oSqlTran.Rollback();
            m_sbErrMsg.Length = 0;
            m_sbErrMsg = Utilities.ErrorMessage(ex);
            //DB write the Error Log
            m_oErrlog.Add(m_sbErrMsg.ToString(), DateTime.Now);
            throw;

        }
        finally
        {
            oConn.Close(); //always close the connection
        }
        return this.m_bFlag;
    }

    public bool Add(MySqlTransaction oSqlTran, MySqlConnection oConn)
    {
        m_bFlag = false;


        //if (m_oConn.State != ConnectionState.Open)
        //{
        // m_oConn.Open();
        //}

         m_oCmd = new MySqlCommand("uspInvoiceHeaderAdd", oConn);
        m_oCmd.CommandType = CommandType.StoredProcedure;
        m_oCmd.Transaction = oSqlTran;
        m_oCmd.Parameters.AddWithValue("_CustomerID", CustomerID);
        m_oCmd.Parameters.AddWithValue("_InvoiceID", InvoiceID);
        m_oCmd.Parameters.AddWithValue("_Amount", Amount);
        m_oCmd.Parameters.AddWithValue("_PaymentDate", PaymentDate);
        m_oCmd.Parameters.AddWithValue("_Documentype", Documentype);
        m_oCmd.Parameters.AddWithValue("_Flag", Flag);
        m_oCmd.Parameters.AddWithValue("_FedTaxID", FedTaxID);
        m_oCmd.Parameters.AddWithValue("_Memo", Memo);
        m_oCmd.Parameters.AddWithValue("_DueDate", DueDate);


        if (m_oCmd.ExecuteNonQuery() > 0)
        {
            m_bFlag = true;
        }
        return this.m_bFlag;
    }


so i am passing all values (CustomerID etc)but error is
(Incorrect Number of arguments for procedure uspInvoiceHeaderAdd;expected 9,got 6)

and i am using mysql 5.1 stored procedure is

CREATE DEFINER=`root`@`%` PROCEDURE `uspInvoiceHeaderAdd`(
_CustomerID VARCHAR(50),
_InvoiceID INT,
_Amount DECIMAL(18,2),
_PaymentDate DATETIME,
_Documentype INT,
_Flag NATIONAL CHAR(10),
_FedTaxID CHAR(9),
_Memo VARCHAR(50),
_DueDate DATETIME
)
BEGIN
     insert into tblInvoiceHeader(CustomerID,InvoiceID,Amount,
PaymentDate,Documentype,Flag,FedTaxID,Memo,DueDate)
values(_CustomerID,_InvoiceID,_Amount,_PaymentDate,ifnull(_Documentype,0),_Flag,_FedTaxID,_Memo,_DueDate);
END



so what is the problem and how to solve the problem , any one pls help me

Thank's Hemanth
Posted

1 solution

Certainly looks correct to me.

When something is impossible it means an assumption is wrong. Two possible assumptions here (there could be others.)
1. That you are actually running the C# code that you think you are.
2. That the proc you are calling actually looks like what you think it is.

Verify the first by adding output to the C# such as a System.Console. Output during runtime verifies what you are running.
Verify the second by deleting the proc. Exception (different one) at runtime verifies the correct one is being called. (Then recreate and verify parameter exception is still there.)

As another possibility look carefully at _Flag. It is char(10), which means a fixed size. It shouldn't matter but right pad your value in your C# code to that size with spaces. Same with FedTaxId.

If none of that helps then you can at least localize the problem by deleting parameters one at a time off the proc and adjusting your code appropriately. By doing this carefully you should be able to reduce it to only a couple parameters. That might provide either some clue as to a problem in your code or even some oddity (bug) in the driver itself.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900