How to set Access column value to 0 (default) on a new created DB column? C#

Question

So I have read multiple posts about my question but I cant find the right solution for it.

I've got a form in which a user can add a custom column to a database. This column is always of type currency with the currency type of Euro.

I thought I did the trick by creating this query:

string query = "ALTER TABLE Uitgaven ADD COLUMN [" + textBox16.Text + "] MONEY DEFAULT 0";

                    command.CommandText = query;
                    command.ExecuteNonQuery();

At my initialize I have a functions that add my database values to a listbox with a reader, it also transforms the read values to the right output, see here the code for it:

 while (reader2.Read())
                {                                               
                    for (int i = 0; i < reader2.FieldCount; i++)
                    {
                        y = reader2.GetName(i).Length;
                        if (reader2.GetName(i) != "Maand" && reader2.GetName(i) != "Uitgaven Id")
                        {
                            euro_display = (decimal)(reader2.GetValue(i));
                            ListBox2_add.Items.Add(reader2.GetName(i) + "€".PadLeft(31 - y) + euro_display.ToString("0.00", CultureInfo.CreateSpecificCulture("nl-NL")));
                            comboBox1.Items.Add(reader2.GetName(i));
                            totaal_uitgaven += reader2.GetDecimal(i);
                        }
                    }
                }

This above function however isn't working when someone adds a custom field with my first query code. This above function needs a value in the database and for some reason the query code in the beginning of my code isn't setting the new added column values to the default 0 (0,00 euro).

I try'd multiple things but non of them see to do the trick.

I modified my query to (among others):

string query = "ALTER TABLE Uitgaven ADD COLUMN [" + textBox16.Text + "] MONEY DEFAULT 0 NOT NULL";

string query = "ALTER TABLE Uitgaven ADD COLUMN [" + textBox16.Text + "] Y DEFAULT 0 NOT NULL"; `

(read somewhere that Y was the currency for Euro--not sure tough)

I also try'd to add a second query after my first query to set its value of the created column:

string query2 = "ALTER TABLE Uitgaven ADD DEFAULT 0 FOR [" + textBox16.Text + "]";

string query2 = "INSERT INTO Uitgaven ([" + textBox16.Text + "] ) VALUES (0)"; 

This all didn't work though.. Anyone has a clue how I can set the values of a custom created column to a default 0 or just to 0 (currency - euro)?

Thanks in advance!

Answer 1

Your problem is related to the fact that after adding the new column, the old records are not updated with the default value. They contains a NULL. You need to add an UPDATE query to your creation routine that sets the DEFAULT for all existing records

string query = "ALTER TABLE Uitgaven ADD COLUMN [" + 
               textBox16.Text + "] MONEY DEFAULT 0";
command.CommandText = query;
command.ExecuteNonQuery();
command.CommandText = "UPDATE TABLE Uitgaven SET [" + textBox16.Text + "] = 0";
command.ExecuteNonQuery();

Another approach could be to check for null values and act accordingly if one null is detected

while (reader2.Read())
{                                               
    for (int i = 0; i < reader2.FieldCount; i++)
    {
        string fieldname = reader2.GetName(i);
        y = fieldname.Length;
        if (fieldname != "Maand" && fieldname != "Uitgaven Id")
        {
            decimal euro_amount = reader.IsDbNull(i) ? 0m : reader2.GetDecimal(i);
            ListBox2_add.Items.Add(fieldname + "€".PadLeft(31 - y) + euro_amount.ToString("0.00", CultureInfo.CreateSpecificCulture("nl-NL")));
            comboBox1.Items.Add(fieldname);
            totaal_uitgaven += euro_amount;
        }
    }
}