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

Add auto field with alter statement in Access/Vb6


Create auto filed ,which automatically generate value, programmatically in Vb6 is easy with two line of code.

What we will do when you need create auto value programmatically or with query.

Here what you need to do

  1. Create a number/text field.
  2. Alter the filed as auto .

  alter table YourTable column [auto] counter(1,1)

You can use the statement with Execute method of ADO connection in VB6  as follows

 adodbconnection.Execute “alter table YourTable  alter column [auto] counter(1,1)”