Check if column name already exists in Access/oledb

Question

I made a simple Windows forms application which creates a table in the access file(mdb) insert, delete, update columns. (I used OLEDB connection for this.) I wrote this code to insert column.

   con.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = con;
    cmd.CommandText = "alter table [" +tableName + "] add [" + columnName + "] long;
    cmd.ExecuteNonQuery();
    con.Close();

Now i want program to check if column name which user want to insert is already exists in the database. What should i do for that?

In the database or in the table? – daZza Mar 31 '17 at 08:56 — daZza, Mar 31 '17 at 08:56

Rahul · Accepted Answer · 2017-03-31T09:43:04.950

For MS-Access:

Since you are doing this at application code, you can wrap it in a try .. catch block. If column exists then it will throw an exception, which you can catch and do whatever needed like

try
{
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandText = "alter table [" +tableName + "] add [" + columnName + "] long";
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  //do your processing
}
finally
{
      con?.Close();
}

If using SQL Server then

You can check against sys.columns system view like

SELECT 1 FROM sys.columns 
        WHERE [name] = N'your_new_columnName' 
        AND [object_id] = OBJECT_ID(N'Your_tableName');

Again, yo be better you can consider wrapping this in a stored procedure and call that procedure in your application code like

create procedure usp_altertable(table_name varchar(20), column_name varchar(20))
as
begin
DECLARE @sql varchar(100);
   IF NOT EXISTS(SELECT 1 FROM sys.columns 
            WHERE [name] = N'your_new_columnName' 
            AND [object_id] = OBJECT_ID(N'Your_tableName'))
SET @sql = 'alter table ' +table_name +' add ' + column_name + ' long';
EXEC(@sql);
end

Finally, if table_name and column_name are coming as user input from UI then be wary of SQL Injection and use parameterized query instead.

And you are sure that Access (mdb) has a sys.columns table, supports stored procedures and has a varchar type? Maybe you mistakenly assumed an SQL Server? — Heinzi, Mar 31 '17 at 09:36
@Heinzi, my bad... missed that point completely. Looks like I should remove the irrelevant part which is targetted for SQL Server. — Rahul, Mar 31 '17 at 09:38
@Heinzi, answer edited ... hope it make sense now. Thanks for pointing. — Rahul, Mar 31 '17 at 09:43

Check if column name already exists in Access/oledb

1 Answers1