ADO.NET Gotcha - Why is my Query Blowing Up
It works in Access or Query Analyzer
One pretty common problem I see in various newsgroups is the following: Someone will post a question to the effect of "I have a query that blows up whenever I run it through ADO.NET. However, I can take the same query and run it in Access/Sql Server Query Analyzer and it runs just fine. Is this some sort of bug?"
99% of the time, the problem is one of two things: 1) Using a character in the field name ie "First Name" 2) Using a SQL Reserved word or a word reserved by your RDMBS system.
Les Smith discusses the preferred solution which is to get the space out of the name or not use the reserved word, but let's say that you just can't bend. Many times people will say that they simply have too much legacy code referencing the illegal names and that it'd be too much work to fix it. I would counter that if it's too much work to fix it, it'll be too much work to maintain it b/c you or someone else is invariably going to forget the workaround, but that's another issue.
So, how do you use your reserved words or poorly constructed field names? Simple, enclose the field name in question in brackets "[]"
So, if this statement was blowing up on you:
| "SELECT First Name, Last Name, Date, Password FROM SomeTable WHERE First Name = @FirstName AND Last Name = @LastName AND Date > @SomeDate AND Password = @Password" |
change it to this:
| "SELECT [First Name], [Last Name], [Date], [Password] FROM SomeTable WHERE [First Name] = @FirstName AND [Last Name] = @LastName AND [Date] > @SomeDate AND [Password] = @Password" |
This simple fix will get you through the crunch, but take Les' advice and change the names...if you don't, don't be surprised when wrapping the fields in brackets slips you or one of your co-workers minds!
*For your reference, I'm including a few other lists of reserved words below:
Access Reserved Words
SQL Server Reserved Words (Includes Access)
SQL Server CE Reserved Words
Oracle Reserved Words
DB2 Reserved Words