Programming with Access? Know this about column names

Because it’s the beginning of the week, I’m again presenting more about programming .NET with an Access database.

Last week, I offered help with a confusing syntax error. Before that, I demonstrated the unique way Access handles JOIN. Today I have a tip on addressing poorly named columns in your code.Suppose someone hands you an Access table with a field named “# of children” for example. I have actually been given one just like that. Now try to SELECT from it:

public void Foo()
{
string sql = "select # of children from foo"; // note pound sign in SQL
 OleDbCommand cmd = new OleDbCommand(sql);
 // SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.
 cmd.ExecuteReader(); // Output is wasted, irrelevant to example
}

That will never work! Access will instantly reject it. Here’s what does work; note the square brackets now.

public void Foo()
{
string sql = "select [# of children] from foo";
 OleDbCommand cmd = new OleDbCommand(sql);
 // SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.
 cmd.ExecuteReader(); // Output is wasted, irrelevant to example
}

A tip on that tip

Have you ever iterated through the columns of a table to execute some kind of SQL command on them? It never hurts to put square brackets around every field name. Something like select [foo],[bar],[baz] from quux is just fine. A lot of times I’m building field lists at runtime and this works great.