|
Home > Archive > IIS ASP > December 2004 > Error in dynamic sql: Data type mismatch in criteria
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Error in dynamic sql: Data type mismatch in criteria
|
|
|
| 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
| |
| Mark Schupp 2004-12-27, 5:49 pm |
| What do you get when you write out the sql statement?
--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Jack" <Jack@discussions.microsoft.com> wrote in message
news:FBA01CD4-0B0B-4882-ADFA-4DD0ABE4988F@microsoft.com...
> 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
>
| |
| Bob Barrows [MVP] 2004-12-27, 8:47 pm |
| "Jack" <Jack@discussions.microsoft.com> wrote in message
news:FBA01CD4-0B0B-4882-ADFA-4DD0ABE4988F@microsoft.com...
> 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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I don't
check it very often. If you must reply off-line, then remove the "NO SPAM"
| |
|
| 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.
"Mark Schupp" wrote:
> What do you get when you write out the sql statement?
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> www.ielearning.com
>
>
> "Jack" <Jack@discussions.microsoft.com> wrote in message
> news:FBA01CD4-0B0B-4882-ADFA-4DD0ABE4988F@microsoft.com...
> from
> _
>
>
>
| |
|
| 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 [MVP]" wrote:
> "Jack" <Jack@discussions.microsoft.com> wrote in message
> news:FBA01CD4-0B0B-4882-ADFA-4DD0ABE4988F@microsoft.com...
> 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
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I don't
> check it very often. If you must reply off-line, then remove the "NO SPAM"
>
>
>
| |
| Bob Barrows [MVP] 2004-12-28, 2:51 am |
| Jack wrote:
> 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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
| |
| Bob Barrows [MVP] 2004-12-28, 2:51 am |
| Jack wrote:
> 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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
| |
|
| Thanks for the feedback again. You guys are just great. Regards.
"Bob Barrows [MVP]" wrote:
> Jack wrote:
>
> 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.
>
>
> 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
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>
|
|
|
|
|