Update Oledb Access columns at run time in C#/.Net


How to add fields to a access table in C# at run time ? This tutorial will share what you need to learn.

.Net Coder

First I have to say that , in this example used the Oledb adapter and connection which is not included in this code. You can check my previous posts on oledb connections(connection and Adapter).

I am going to put a checking section before adding column, others wise C# will catch an error. How ? I can use the Schema of the connection

var schema = con.GetSchema("COLUMNS"); 
                  var col = schema.Select("TABLE_NAME='" + tble + "' AND COLUMN_NAME='" + colname + "'" );

Here using var schema I can check the existence of column and if it found the var column contain a name, that means the length of col will be greater than zero. That make it simple. Let’s complete our function UpdateColumn

public static void UpdateColumn(string tble, string colname, string type)
        {
            OleDbCommand updateField = new OleDbCommand();
            try
            {
                 var schema = con.GetSchema("COLUMNS"); 
                 var col = schema.Select("TABLE_NAME='" + tble + "' AND COLUMN_NAME='" + colname + "'" );

                if(col.Length > 0)
                {
                   // Column exist
                }
                else
                {
                    // Column doesn't exist
                   updateField.CommandText=" ALTER TABLE " + tble +" ADD COLUMN " + colname +  "  "  +  type ;
                   updateField.Connection=con;
                   updateField.ExecuteNonQuery();
                  
                }
            }

Put the function in static class , if you want to use in the entire project

You can Call the Function as follows

 //Update Database
            this.UpdateColumn("Accounts", "extended", "bit");

Remember if you want add a new column to a table with Type of Yes/No, you have to use bit instead of Yes/No or bool.In access table design it will be Yes/No type

That is all I have today

Syntax Error INSERT INTO Statement – ADO.Net Solved


This is a common error occurred during the database operation in ADO.Net. The cause for this error is the column name matches some reserved words. To solve this issue suffix and prefix with “] and [” in column names.

Command object

If you are using command object use the following method in the insert statement.

OleDbCommand cmd = new OleDbCommand(“insert into acgroup(gname,parent,[note]) values(‘” + gname + “‘,'” + gparent + “‘,'” + gdescription + “‘” + “)”, Globals.con);
cmd.ExecuteScalar();

here ‘note’ cause the error.

Dataset and Command builder

If you are using dataset, adapter and command builder to insert records, use command builder Quickfix method to solve the issue as follows

dr = Globals.GroupDataSet.Tables[0].NewRow();
dr[“gname”] = gname;
dr[“parent”] = gparent;
dr[“note”] = gdescription;
Globals.cmdbuilder = new OleDbCommandBuilder(Globals.GroupAdapter);
Globals.cmdbuilder.QuotePrefix = “[“;
Globals.cmdbuilder.QuoteSuffix = “]”;
Globals.GroupDataSet.Tables[0].Rows.Add(dr);
Globals.GroupAdapter.InsertCommand = Globals.cmdbuilder.GetInsertCommand();
int stat = Globals.GroupAdapter.Update(Globals.GroupDataSet.Tables[0]);
if (stat > 0)
{
MessageBox.Show(“Group Information saved”);
}