Add Unique Column Name to Access Database via Application - C#

Question

I have a program which records ID,Name,TimeIn,TimeOut. On the first scan of a card it record the id,name and timein, and then on second swipe it adds to the timeout column. I am trying to get it to add another "TimeIn" column on the third swipe, so I tried to get it to insert "TimeIn + Unique Number", but it does not pick up the variable due to the quotes.

Here is my code:

    private void SignIn_Time(OleDbCommand updateCmd, OleDbConnection OLEDB_Connection, Object varName, Object varID, String varTime)
    {
        object varTimeColumn;
        varTimeColumn = "TimeIn" + GetUniqueNumber();

        updateCmd.CommandText = "ALTER TABLE TestDB ADD COLUMN varTimeColumn TEXT";
        updateCmd.CommandText = "INSERT INTO TestDB (varTimeColumn) VALUES (@TIMEIN)";
        updateCmd.Parameters.AddWithValue("@TIMEIN", varTime);
        OLEDB_Connection.Open();
        updateCmd.Connection = OLEDB_Connection;
        updateCmd.ExecuteNonQuery();
        OLEDB_Connection.Close();
    }

    static int counter;
    public static int GetUniqueNumber()
    {
        return counter++;
    }

You're adding a column to a table every time a third swipe occurs? This is madness! I urge you to rethink your data model! — Corak, Nov 29 '13 at 10:04
In the context I am using it this works in conjunction with it. @Corak — Ryan Gillooly, Nov 29 '13 at 10:39
Adding an unknown amount of columns to a table is usually a sign that you should create a second table with a 1-n reference to the first and *one* column for the values (and maybe a "sequence" column). If you know how many columns you add, then why not have them there in the first place? — Corak, Nov 29 '13 at 10:56
I agree with @Corak that this is not a good idea. At the very least, when the time comes for you to query your database and find out who "swiped" on a certain date you would need to `SELECT ... FROM TestDB WHERE TimeIn1=... OR TimeOut1=... OR TimeIn2=... or TimeOut2=... `. Ick. — Gord Thompson, Nov 29 '13 at 11:40

Steve · Answer 1 · 2013-11-29T11:29:27.480

There are two errors in the code above:
The Access Jet Engine doesn't support two concatenated commands. You should send each command by itself.

Another problem is the variable name used to represent the column name. You cannot embedd the variable inside the command. You should put its value, and to do that, you could only use a string concatenation.

private void SignIn_Time(OleDbCommand updateCmd, OleDbConnection OLEDB_Connection, 
                         Object varName, Object varID, String varTime)
{
    try
    {
        OLEDB_Connection.Open();
        string varTimeColumn = "TimeIn" + GetUniqueNumber().ToString();
        updateCmd.Connection = OLEDB_Connection;
        updateCmd.CommandText = "ALTER TABLE TestDB ADD COLUMN " + varTimeColumn + " TEXT";
        updateCmd.ExecuteNonQuery();

        updateCmd.CommandText = "INSERT INTO TestDB (varTimeColumn) VALUES (@TIMEIN)";
        updateCmd.Parameters.AddWithValue("@TIMEIN", varTime);
        updateCmd.ExecuteNonQuery();
        OLEDB_Connection.Close();
    }
    catch(Exception ex)
    {
        if(OLEDB_Connection.State == ConnectionState.Open)
            OLEDB_Connection.Close();

        // Perhaps in debug you could do something here with the exception like a log message
        // or rethrow the execption to be handled at an upper level...
        throw;
    }
}

static int counter;
public static int GetUniqueNumber()
{
    return counter++;
}

Also I suggest to use a try/catch block around your code because, in case of exceptions, you don't close the connection. A better approach should be the using statement, but from the code above is not clear how to implement this pattern

I completely agree with the comment from @Corak above. The proposed solution is the only rationale approach to your logical requirements. Also, remember that an Access Table has limitations on the max number of columns that could be added to a table. 255 is this limit and your code doesn't seem to keep this in any consideration.

Microsoft Access 2010 specifications

Add Unique Column Name to Access Database via Application - C#

1 Answers1