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:

[sourcecode language=”csharp”]
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
}
[/sourcecode]

That will never work! Access will instantly reject it. Here’s what does work; note the square brackets now.
[sourcecode language=”csharp”]
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
}
[/sourcecode]

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.