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.

About these ads

2 responses

  1. […] Programming with Access? Know this about column names « Critical Results blog.criticalresults.com/2009/11/03/access-oledb-tips-3 – view page – cached Because it’s the beginning of the week, I’m again presenting more about programming .NET with an Access database. […]

  2. […] JOIN syntax. Then this one about “parameter” errors. And finally this one about weird column names. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,556 other followers

%d bloggers like this: