Mats wrote:
Thanks Bob.
Ok - I should have remembered the four dubbleqoutes you ned in order
to escape quotes in asp.
I'm surprised that something so important and problematic is hardly
discussed at all.
Could you direct me and others to a source of information in this
matter?
Mats
Most books on data access cover this. Bill Vaughn's "ADO Examples and Best
Practices" is a good one, despite being geared more to VB than ASP. Here's
something I posted a couple weeks ago that a couple people found helpful:
Here are the rules for delimiting data in dynamic sql strings, particularly
in the WHERE clause:
To decide whether or not to delimit the data, look at the datatype of the
FIELD BEING COMPARED TO - NOT THE DATA.
1. If it's a numeric field, you must supply it with numeric data, which
means you MUST NOT delimit the data by putting quotes around it.
2. If it's a character/text field, then you must supply string data by
delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:
Update tbl set textfield = 'O''Malley'
In Access, you can use double quotes for your delimiters, so this will work
as well:
Update tbl set textfield = "O'Malley"
Note: you don't have to escape the apostrophe in O'Malley when you use
double quotes as the delimiter. However, you will need to escape the double
quotes when assigning this statement to a variable:
sSQL = "Update tbl set textfield = ""O'Malley"""
So most people will use the single quotes and escape the apostrophe:
sName = "O'Malley"
sSQL = "Update tbl set textfield = '" & Replace(sName,"'","''") & "'"
response.write sSQL
3. If it's a date/Time field, then the delimiters depend on the type of
database. Since you are using Access, then you must delimit the data with
hash marks (#). Additionally, you must supply the data in US format
(m/d/yyyy) or in ISO (yyyy-mm-dd), with the latter being the more
recommended.
4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using
And then, when you think you have it right and it still does not work,
response.write it to see the result of your concatenation. If you've done it
correctly, you will have a statement that you can copy and paste from the
browser window into the SQL View of an Access Query Builder and run without
modification (unless you need to replace the wildcards with the Jet
wildcards).
This all seems rather difficult, doesn't it? Add to this the fact that a
dynamic sql query will not perform as well as a saved query/stored
procedure, and you have two strikes against it. Add the lack of security due
to leaving yourself open to a SQL Injection attack and you have three
strikes.
Let me show you how easy this can be using a saved parameter query. let's go
back to your statement and parameterize it* :
UPDATE tblListingspriceChanges SET NewPrice = [P1],
ChangeDate = [P2], [Name]=[P3], Original_Price=[P4]
WHERE PriceChangeID = [P5]
Do you notice ANY delimiters in the above sql statement? :-)
Test this statement in the Access Query Builder by running it: you will be
prompted to supply values for each of the parameters. Supply some values and
make sure it works as intended. When you've finished debugging it, save it
as qUpdPriceChange. Notice that you've created and debugged your query in
the environment where debugging and testing of queries should be done: in
the database environment.
Now to run it in ASP:
'create and open a connection object, cn, populate and
'validate your data variables, then:
cn.qUpdPriceChange NewPrice, ChangeDate, Name, _
Original_Price, PriceChangeI
If you are running a query that returns records, you can still use this
syntax, by supplying a recordset variable as an extra argument:
set rs = server.createobject("adodb.recordset")
cn.QueryName parm1,...parmN, rs
HTH,
Bob Barrows
