 By
Monique Angelich
Contributing Writer
Many programmers hit a brick wall in their SQL queries, slowing development to
a crawl and causing an over abundance of hair loss and coffee consumption. If
you have been staring at the screen with a blank look and a pounding heart, wondering
what you did to deserve this mess, you have come to the right place. In this article
I will unveil the six most common SQL query errors and give you tips on stress
prevention. Since the Structured Query Language (SQL) is used to access all
three commonly used website databases; mySQL, SQL and Access, I have chosen the
errors that are most common to all, rather than snub a database and cause an influx
of complaints. (wink)
|
Fingers A’Flyin
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 tick 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?
For The Professional Developer
Trials, Downloads and More... |
Tic Tack Quote
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:
rsSQL = “Select Column1 from Table1 where This_Data = ‘thatData’
“
It should NOT look like this:
rsSQL = “Select Column1 from Table1 where This_Data = “thatData”
“
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.
Size Does Matter
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.
Pest Control
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.
Rappin’ It Up
SQL is a database programming language most of us learn by trial and error. We
all begin with a less than complete understanding of how to write our queries,
leaning on more experienced programmers in our times of desperation.
To get off to a good start and prevent SQL errors from rearing their ugly heads:
- Think Ahead - Plan your database carefully, allowing enough room for
the data you intend it to hold.
- Create a Database Chart - Reference your chart while writing all programming
code.
- Don’t Rush – Watch your typing when writing SQL queries.
If you still find yourself in over your head, or tearing out your hair in frustration,
email me with your SQL query question. If
I don’t know the answer, I will find someone who does!
About the Author:
Monique Angelich is a successful freelance Internet Programmer, Software
Engineer, and Web Designer specializing in helping companies use the
Internet as their portal to success. You can visit the Devedia family of
websites at http://www.devedia.com. Email Monique directly at devi@devedia.com.
|
ASP
Development Tools
IISTracer
IISTracer is a real-time monitoring tool for Microsoft IIS scripts (.asp, .cgi,..),
static files (.htm,.gif,..), downloads (.mp3,.zip,..) and uploads. Shows online
current status and progress of each running request in IIS, lets you log current
IIS state
ASP
Barcode Server Component
Easily add server-side barcodes to web applications. The ASP Barcode Server Component
for IIS is a server side component used to add bar codes to webpages hosted on
Microsoft IIS through ASP (Active Server Pages). This implementation is compatible
with all browsers and is easy to embed in HTML as an image with the IMG tag.
ASPassword
1.0
ASPassword password protects your website quickly and easily through the use of
a single ASP page. All you need to do to protect your website is create your ASPassword
login file, and add one line of code to the top of every page you wish to protect.
There are no complicated components to install on the server, no need to send
email to your hosts support team asking whether they support ASPassword.
CustomError
for IIS
Deploy your own error messages for integrated design & security with CustomError.
|
|
|
|
