DataAdapter UpdateCommand - formatexception

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
orosseel
Posts: 1
Joined: Wed 26 Mar 2008 12:19

DataAdapter UpdateCommand - formatexception

Post by orosseel » Wed 26 Mar 2008 12:25

Hi,

I have a serious problem.

I wrote some code to fill up a table in an oracle database and it worked, I think, with the trial version.

After I bought an official version, I constantly get an error by updating the table in the database. Reading data from an oracle DB is possible.

Here is a part of my code:

Code: Select all

public static bool UpdatePOToDB(DataSet ds, string tableName)
        {
            DataSet dsChanges = ds.GetChanges();
            if (!dsChanges.HasChanges()) return true;
 
            StoreTablePickingOrder(dsChanges, tableName);
            if (dsChanges.HasErrors) return false;

            ds.Merge(dsChanges);
            bool ok = !ds.HasErrors;

            if (ok) ds.AcceptChanges();
            else ds.RejectChanges();

            return ok;
        }

private static bool StoreTablePickingOrder(DataSet ds, string tableName)
        {
            if (makeConnection())
            {
                OracleDataAdapter oraDataAdapter = new OracleDataAdapter();
                OracleCommand oraCommand;
                OracleParameter oraParm;

                oraCommand = new OracleCommand("SELECT * FROM " + tableName, myConnection);
                oraDataAdapter.SelectCommand = oraCommand;

                try
                {
                    oraCommand = new OracleCommand("UPDATE " + tableName + " SET LOC_ID=@loc_id", myConnection);

                        //+ "COMP_QUANT = :comp_quant "

                        //+ "COMP_PQUANT = :comp_pquant "

                        //+ "PIO_ID = :pio_id "

                        //+ "PIOL_ID = :piol_id "

                        //+ "PR_ID = :pr_id "

                        //+ "COMP_PR_ID = :comp_pr_id "

                        //+ "LOC_ID = @loc_id "

                        //+ "PIO_CODE = @pio_code "

                        //+ "LOC_CODE = :loc_code "

                        //+ "COMP_PR_CODE = :comp_pr_code "

                        //+ "PR_CODE = :pr_code "

                        //+ "COMP_BARCODE = :comp_barcode "

                        //+ "COMP_DESCRIPTION = :comp_description"

                        //, myConnection);

                  //oraDataAdapter.UpdateCommand.Parameters.Add("comp_quant", ds.Tables[0].Columns["COMP_QUANT"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("comp_pquant", ds.Tables[0].Columns["COMP_PQUANT"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("pio_id", ds.Tables[0].Columns["PIO_ID"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("piol_id", ds.Tables[0].Columns["PIOL_ID"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("pr_id", ds.Tables[0].Columns["PR_ID"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("comp_pr_id", ds.Tables[0].Columns["COMP_PR_ID"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("loc_id", ds.Tables[0].Columns["LOC_ID"]);

                    oraCommand.Parameters.Add("@loc_id", OracleDbType.Number, 38, "LOC_ID");

                    //oraDataAdapter.UpdateCommand.Parameters.Add("@pio_code", ds.Tables[0].Columns["PIO_CODE"]);

                    //oraCommand.Parameters.Add("@pio_code", OracleDbType.VarChar, 20, "PIO_CODE");

                    //oraDataAdapter.UpdateCommand.Parameters.Add("loc_code", ds.Tables[0].Columns["LOC_CODE"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("comp_pr_code", ds.Tables[0].Columns["COMP_PR_CODE"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("pr_code", ds.Tables[0].Columns["PR_CODE"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("comp_barcode", ds.Tables[0].Columns["COMP_BARCODE"]);

                    //oraDataAdapter.UpdateCommand.Parameters.Add("comp_description", ds.Tables[0].Columns["COMP_DESCRIPTION"]);

 
                    oraDataAdapter.UpdateCommand = oraCommand;

                    OracleCommandBuilder oraCommandBuilder = new OracleCommandBuilder(oraDataAdapter);

                    oraDataAdapter.Update(ds);  => here, I’m getting the error

                    oraDataAdapter.Dispose();

                    return true;

                }
                catch (DBConcurrencyException)
                {
                    oraDataAdapter.Dispose();
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
and this is the error in visual studio:

Code: Select all

System.FormatException was unhandled
  Message="FormatException"
  StackTrace:
    at System.Number.ParseDouble()
    at System.Double.Parse()
    at System.Convert.ToDouble()
    at System.String.System.IConvertible.ToDouble()
    at System.Convert.ToDouble()
    at CoreLab.Oracle.OracleParameter.n()
    at CoreLab.Oracle.OracleParameter.af()
    at CoreLab.Oracle.OracleCommand.aa()
    at CoreLab.Oracle.OracleCommand.al()
    at CoreLab.Common.DbCommandBase.ag()
    at System.Data.Common.DbCommand.ExecuteReader()
    at CoreLab.Oracle.OracleCommand.ExecuteNonQuery()
    at System.Data.Common.DbDataAdapter.UpdateRowExecute()
    at System.Data.Common.DbDataAdapter.Update()
    at System.Data.Common.DbDataAdapter.UpdateFromDataTable()
    at System.Data.Common.DbDataAdapter.Update()
    at System.Data.Common.DbDataAdapter.Update()
    at PDAPicking.OracleDB.StoreTablePickingOrder()
    at PDAPicking.OracleDB.UpdatePOToDB()
    at PDAPicking.DSPickingOrder.WriteToDB()
    at PDAPicking.frmMain.menuItem5_Click()
    at System.Windows.Forms.MenuItem.OnClick()
    at System.Windows.Forms.Menu.ProcessMnuProc()
    at System.Windows.Forms.Form.WnProc()
    at System.Windows.Forms.Control._InternalWnProc()
    at Microsoft.AGL.Forms.EVL.EnterMainLoop()
    at System.Windows.Forms.Application.Run()
    at PDAPicking.Program.Main()
Can you please help me?

Yours sincerely

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 26 Mar 2008 12:47

Please send me (alexeyman*crlab*com) a small test project to reproduce the problem.
Please include the definition of your own database objects.
Do not use third party components.

Post Reply