SQL Database Errors
The most common SQL errors occur from typing mistakes. Take note of the word 'structured' in Structured Query Language. They weren't kidding, SQL wants its statements written according to syntax, with no exceptions. Think of SQL as the Prima Donna of all database languages.
If you receive a SQL error when testing your program, the first thing you should ALWAYS do is print that query to the page before you attempt to execute it in the code. Get a clean cloth, clean your glasses, wipe the dust off the monitor, and peer closely at the screen. Are the commas where the commas belong? Are you missing an ending tic when setting a column equal to a string data type? Is the entire query there, or did you perhaps forget to combine your statement lines?
Remember your Tic Marks
Most often, I tend to get sidetracked and forget to add the tic marks (single quotes) around my strings properly, sometimes neglecting them all together. Another possibility is using double quotes inside the string where they do not belong.
For example, your query should look like this:
It should NOT look like this:
If you write a query like the second one, the error returned is, “Expected end of statement.” SQL feels sorry for you and gives you an exact location of the first double quote that doesn't belong in your string. If you try leaving out the ending quote mark in your statement, you will get a patronizing, “Unterminated string constant” error message.
Type your SQL statements slowly and reference your database chart often. Take your time with your statements now, or spend it later debugging.
Out To Lunch
Let's not forget the programmer's tendency to attempt to collect column data from the wrong table, asking SQL to get the UserName from the Products table. Before you run off and try that one, I can tell you now that it doesn't work. (grin) SQL gets really testy with me when I do that.
SQL likes to be vague about this error, telling you “No value given for one or more required parameters.” If you see this error, check your column name spellings and table name. Most often, one of them has been misspelled.
The best way to avoid this mistake is to create a database chart for your current program and put it right next to your keyboard. Refer to the chart when writing your queries and this error will become much less common in your program.
Apples and Oranges
Data type errors are also extremely common when working with databases. Often, a programmer will attempt to insert letters or symbols into a column that has been set to contain numeric data, or vice versa. SQL is very forgiving about this error, nicely telling you there is a “Data type mismatch in criteria expression.”
Avoiding this error is a simple matter of referring to your database chart or schematic when writing your queries. I cannot stress the importance of a detailed database chart enough when it comes to programming with a database.
When creating a table, the most often ignored setting for the column is the size property. Good database planning before creating your tables will prevent you from seeing this error message, “The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.” This message means exactly what it says, you can't shove 25 characters into a column you set up to hold a maximum of 10.
Spend some time researching before creating your database charts. What type of data do you expect to be entered? Is there a maximum length a certain field should hold? A minimum length? Should the field be allowed to be empty, or is that column holding data that another part of your program MUST have to function properly?
Play it Again, Sam
The primary key of a database is a unique column in the database that ensures all rows remain separate. They are unique handles used to grab data that otherwise might be too similar. There might be ten Don Johnson's in your database, but only one of them is Don Johnson with the ID number of 145. A primary key, or ID number cannot be duplicated. The integrity of your data depends on it, and SQL will tell you that it “Cannot update 'User_ID'; field not updateable” if you even think about it.
If all else fails and your database query has more bugs than a Sunday picnic, pull out everything but the SQL statement, set each value in the statement without pulling info from the querystring or a form post, then slowly replace each variable until you find the cause of the error.