|
Troubleshooting SQL Statements
by Charles Carroll
Now we will show some SQL statements (below)
with mistakes and the fixes indicated in red to show how to make the statements work. The
kind of fixes we deploy include:
text fields must have single quotes around
the values.
fields with spaces in their names
must be surrounded with [ ]
set parameters must have commas between them
Text fields with single quote '
cannot be placed into SQL statements unmodified. Notice how the last example below uses
the VBScript replace command to transform a string that may contain embedded '
.
| Statement
with Flaws |
Improved
Statement (fixes in
red) |
| UPDATE mytableSET
LocID=0007,Material=13 1/4 Description=T-shirts ListPrice=35 WHERE CusID=97 |
added space before SET
UPDATE mytable SET LocID='0007',Material='13 1/4', Description='T-shirts', [List Price]=35 WHERE CusID=97 |
| INSERT INTO authors
(AU_ID, author, year_born) VALUES (7000, Joe Smith,1950) |
INSERT INTO authors
(AU_ID, author, year_born) VALUES (7000, 'Joe Smith',1950) |
SELECT * from atable
where state = MD and
year born<1955 |
SELECT * from atable
where state = 'MD' and
and [year born]<1955 |
<%
key=request.querystring("id")
au=request.querystring("author")
birthyear=request.querystring("year")
SQLstmt="INSERT INTO authors (AU_ID, author, year_born) VALUES ("
SQLstmt= SQLstmt & key & ","
SQLstmt= SQLstmt & author & ","
SQLstmt= SQLstmt & birthyear & ")"
%> |
<%
key=request.querystring("id")
au=request.querystring("author")
au=Replace(au,
"'", "''")
birthyear=request.querystring("year")
SQLstmt="INSERT INTO authors (AU_ID, author, year_born) VALUES ("
SQLstmt= SQLstmt & key & ",'"
SQLstmt= SQLstmt & author & "',"
SQLstmt= SQLstmt & birthyear & ")"
%> |
 |  |  |
|
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too.
a $5 tip buys him lunch at McDonalds,
a $20 tip buys his kid Hitoshi a new computer game,
a $39 tip buys his daughter Michiko a few nice outfits.
See our donor list.
| |
 | |  |
|
|
|
|