I started working on a VB client/server app that uses Winsock to transmit encrypted data to and from my server at home. The server end connects to a simple MS Access database and runs SQL queries against it. The database only has 2 tables. One is named "Users" and the other is named "Accounts". The "Users" table only has two columns. One of them is named "ID" and it is an integer value that auto-increments. The other column is named "User" and just stores a 50 character string value.
One of the more simple queries involves updating the "Users" table by adding a new row to it. This is the code I was using INSERT INTO Users (User) VALUES ('sometext') Every time I ran this SQL query it would crash with an invalid syntax error. I scratched my head and fumbled around for quite a while trying to figure out what in the World was going on. By all accounts my syntax was perfectly fine and sometext did not contain any SQL reserved characters, such as, the apostrophe < ' >.
I even ran the same query against my "Accounts" table after substituting the User column for a column in my Accounts table. The SQL query ran perfectly. Just for kicks I changed the name of the User column in my Users table to "Name", re-ran my query, and it ran PERFECTLY. I guess having a table named "Users" with a column named "User" is unacceptable to Microsoft Access and possibly other database verisons? This is probably documented somewhere I'm sure.
So, the moral of the story is, "Don't give your table coumns names that appear within the name of the table itself."
No comments:
Post a Comment