Use parameterised query to add column of Numeric type with Scale of 2 in Access using OldDb

user_7709072 0
Viewed 397

I am trying to add some columns in an access database using Oledb Command. The type of the column are different, like Currency(OleDbType.Currency), float with two decimal place (OleDbType.Numeric), integer(OleDbType.Integer) etc. the below code is what I am using to accomplish my task... But each and every time it Shows error Message saying Some..".. Error in Field Definition.." or as I change code sometime it shows "Syntax Error in Column Definition .." etc.

var insertColumnCommand = @"ALTER TABLE ProductRecordsTable ADD COLUMN [Price]= @ProductPrice, [Shipped(NoS)]= @ProductShippedNumber, [Returned(NoS)]= @ProductReturnedNumber, [Sold(NoS)]= @ProductSoledNumber, [Tax Percentage]= @ProductTaxPercentage";
var con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyDatabase.accdb;Jet OLEDB:Database Password= 12345;");
using(con)
{
   var cmd = new OleDbCommand(insertColumnCommand, con);
   if (con.State == ConnectionState.Closed)
       con.Open();
   cmd.Parameters.Add("@ProductPrice", OleDbType.Currency);
   cmd.Parameters.Add("@ProductShippedNumber", OleDbType.Integer);
   cmd.Parameters.Add("@ProductReturnedNumber", OleDbType.Integer);
   cmd.Parameters.Add("@ProductSoledNumber", OleDbType.Integer);
   var parameter = new OleDbParameter("@ProductTaxPercentage",OleDbType.Numeric);
       parameter.Precision = 6;
       parameter.Scale = 2;
       cmd.Parameters.Add(parameter);

   cmd.ExecuteNonQuery();
   con.Close();
}

I would also like to add option like putting NULL value in already existing rows of the table in the newly added columns, and 0(Zero) will be inserted as default value, for the rows that will be added later on.But the cmd.ExecuteNonQuery(); like is too stubborn to not run successfully.

oledb ms-access database c#
1 Answers

As said in comments above, you cannot use parameters when you execute Data Definition Language commands like ALTER TABLE (this is true in any database I known of) In your specific case you can use the DECIMAL datatype for the columns that requires scale and precision values and no = between the name of the column and the type.

@"ALTER TABLE ProductRecordsTable 
    ADD COLUMN [Price] DECIMAL (10,2), 
               [Shipped(NoS)] INT, 
               [Returned(NoS)]  INT, 
               [Sold(NoS)] INT, 
               [Tax Percentage]  DECIMAL (6,2)";

Then suppose you want to set the Price to be NOT NULL. First execute a query to set every row to a predefined value

"UPDATE ProductRecordsTable SET Price = 0"

And now set the NOT NULL value on the column

@"ALTER TABLE ProductRecordsTable ALTER COLUMN [Price] DECIMAL (10,2) NOT NULL"

If you want to set a default value (for new records on some column

@"ALTER TABLE ProductRecordsTable ALTER COLUMN [Sold (NoS)] INT DEFAULT 0"
user_11975180
Related