Error in dynamic sql: Data type mismatch in criteria

J

Jack

Hi,
I am trying to test a sql statement in Access which gives me
the error as stated in the heading.
The sql statement is built as a part of asp login verification,
where the userid and password are input in login screen.
The password in the database is a number field.

I am writing the dynamic sql statement as follows below. I believe
I am going wrong in the password section of the code. I
appreciate any help. Thanks. Regards.


Set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection=CN

strSQL = "select qrySubGrantCombo.ComboID, qrySubGrantCombo.Password from
qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND " & _
"qrySubGrantCombo.Password ='" & Request.Form("txt_Password") & "'"
Response.Write strSQL
 
B

Bob Barrows [MVP]

Jack said:
Hi,
I am trying to test a sql statement in Access which gives me
the error as stated in the heading.
The sql statement is built as a part of asp login verification,
where the userid and password are input in login screen.
The password in the database is a number field.

I am writing the dynamic sql statement as follows below. I believe
I am going wrong in the password section of the code. I
appreciate any help. Thanks. Regards.


Set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection=CN

strSQL = "select qrySubGrantCombo.ComboID, qrySubGrantCombo.Password
from
qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND " &
_
"qrySubGrantCombo.Password ='" & Request.Form("txt_Password") & "'"
Response.Write strSQL
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. Why did you say you wanted to do it this way ...?

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
 
J

Jack

Mark,
This is the following sql statement I am getting with a sample userid and
password:
select qrySubGrantCombo.ComboID, qrySubGrantCombo.Password from
qrySubGrantCombo where qrySubGrantCombo.ComboID ='00-H15-81366' AND
qrySubGrantCombo.Password ='81366'
Here in the final output, if I do not have the ' sign on both sides of 81366
then, the query result is fine and the query does not give error as: Data
type mismatch in criteria expression. Thanks.
 
J

Jack

Thanks for your advise Bob. I appreciate it. As per your advise, I just took
out the quotes for numeric value. Now I have


strSQL = "select qrySubGrantCombo.ComboID, qrySubGrantCombo.Password from
qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND " & _
"qrySubGrantCombo.Password = & Request.Form("txt_Password") & "

However, with the change it is generating the following error:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/gwisnewcon/verify.asp, line 25, column 46
"qrySubGrantCombo.Password = & Request.Form("txt_Password") & "

---------------------------------------------^

Where am I going wrong?



Bob Barrows said:
Jack said:
Hi,
I am trying to test a sql statement in Access which gives me
the error as stated in the heading.
The sql statement is built as a part of asp login verification,
where the userid and password are input in login screen.
The password in the database is a number field.

I am writing the dynamic sql statement as follows below. I believe
I am going wrong in the password section of the code. I
appreciate any help. Thanks. Regards.


Set CN=server.createobject("ADODB.Connection")
CN.Open myDSN

Set RS = Server.CreateObject("ADODB.Recordset")
RS.ActiveConnection=CN

strSQL = "select qrySubGrantCombo.ComboID, qrySubGrantCombo.Password
from
qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND " &
_
"qrySubGrantCombo.Password ='" & Request.Form("txt_Password") & "'"
Response.Write strSQL
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. Why did you say you wanted to do it this way ...?

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
 
B

Bob Barrows [MVP]

Jack said:
Thanks for your advise Bob. I appreciate it. As per your advise, I
just took out the quotes for numeric value. Now I have


strSQL = "select qrySubGrantCombo.ComboID,
qrySubGrantCombo.Password from qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND
" & _ "qrySubGrantCombo.Password = & Request.Form("txt_Password") &
"

However, with the change it is generating the following error:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/gwisnewcon/verify.asp, line 25, column 46
"qrySubGrantCombo.Password = & Request.Form("txt_Password") & "

---------------------------------------------^

Where am I going wrong?
You're not showing us the result of

response.write strSQL

You/we cannot troubleshoot a sql statement without knowing what it is.
Showing us the vbscript code that is supposed to generate a sql statement is
not enough. I think I mention that in my previous post.

Bob Barrows
 
B

Bob Barrows [MVP]

Jack said:
Thanks for your advise Bob. I appreciate it. As per your advise, I
just took out the quotes for numeric value. Now I have

You can make this string shorter and more readable by not qualifying the
column names with the table/query name. There's only one table/query in the
FROM clause so there is no chance of confusion.
strSQL = "select qrySubGrantCombo.ComboID,
qrySubGrantCombo.Password from qrySubGrantCombo where " & _
"qrySubGrantCombo.ComboID ='" & Request.Form("txt_UserName") & "' AND
" & _ "qrySubGrantCombo.Password = & Request.Form("txt_Password") &
"

Rewritten, it looks like:
strSQL = "select ComboID,Password " & _
"from qrySubGrantCombo where " & _
"ComboID ='" & Request.Form("txt_UserName") & "' AND " & _
"Password = & Request.Form("txt_Password") & "

Do you see the problem? Concentrate on the 4th line.

What do you need to do before concatenating a new string to an existing
string? Answer: complete the existing string.

"Password =

is not complete until you close/delimit it with an ending quote.

"Password = " & Request.Form("txt_Password")

And then, you attempt to concatenate the beginning of a string ... What is
the purpose of that final & "?

Again, you can avoid this delimiter nonsense by using saved parameter
queries as demonstrated in my initial reply.

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top