data type mismatch error

[David]

Hi,

Getting the following error:-
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The code is as follows, and the last line is the one that produces the
error.

set con = server.createobject("adodb.connection")
con.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
server.mappath("uk.mdb") &""

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country WHERE " & Dot2LongIP & "
BETWEEN IP_FROM AND IP_TO"
Set RsLoc = con.Execute(slocSQL)

I've searched for the error on aspfaq.com, and the error message apparently
refers to inserting invalid dates. However, the data in Dot2LongIP is a
number, and the fields in the database it's trying to look up are both
columns of numerical data, and the columns are set to be text. I've tried
changing the columns in the database to be numeric, but for some reason it
deletes a lot of records, so that appears not to be an option.

Additional info:-

What I'm trying to do is detect visitors country, and send them to a
different version of the page if they are from the UK. I'm attempting to use
the above with the ip-2-country database that I've converted from .csv to
..mdb. Using this database seems to be a good option IF I can get it to work!
However, if anyone has any suggestions about different ways of achieving
redirect if UK visitor then I'd be happy to hear it. I've looked at various
commercial options, but all are way out of what I can financially justify.

I don't work with asp very often, so I'd be grateful for any advice or
assistance!

[Slim]

"David" <da*********@tesco.net> wrote in message
news:Sm*******************@newsfe5-gui.ntli.net...

Hi,

Getting the following error:-
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The code is as follows, and the last line is the one that produces the
error.

set con = server.createobject("adodb.connection")
con.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
server.mappath("uk.mdb") &""

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country WHERE " & Dot2LongIP & "
BETWEEN IP_FROM AND IP_TO"
Set RsLoc = con.Execute(slocSQL)

I've searched for the error on aspfaq.com, and the error message
apparently refers to inserting invalid dates. However, the data in
Dot2LongIP is a number, and the fields in the database it's trying to look
up are both columns of numerical data, and the columns are set to be text.

I asume you are trying to compare ip numbers, you would need to treat them
as text, or remove the dots

try Cstr(IP_FROM ) Cstr(IP_TO )


I've tried changing the columns in the database to be numeric, but for some reason it
deletes a lot of records, so that appears not to be an option.

Additional info:-

What I'm trying to do is detect visitors country, and send them to a
different version of the page if they are from the UK. I'm attempting to
use the above with the ip-2-country database that I've converted from .csv
to .mdb. Using this database seems to be a good option IF I can get it to
work! However, if anyone has any suggestions about different ways of
achieving redirect if UK visitor then I'd be happy to hear it. I've looked
at various commercial options, but all are way out of what I can
financially justify.

I don't work with asp very often, so I'd be grateful for any advice or
assistance!

[Bob Barrows [MVP]]

David wrote:

Hi,

Getting the following error:-
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The code is as follows, and the last line is the one that produces the
error.

set con = server.createobject("adodb.connection")
con.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
server.mappath("uk.mdb") &""

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country WHERE " & Dot2LongIP
& " BETWEEN IP_FROM AND IP_TO"
Set RsLoc = con.Execute(slocSQL)

Yet another problem caused by the use of dynamic sql ;-)
The ip fields are text, so you need to compare them with a string - you tell
the query engine that you are using a string by surrounding it (delimiting
it) with quotes.
" ... WHERE '" & Dot2LongIP & "' ... "

Here is some more infor:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

However, that's the hard way to do it. The better way is to use parameters.
Why? Because using parameters means you never have to deal with these
delimiter issues again, as well as never again leaving yoursself open to the
risks of sql injection
(http://mvp.unixwiz.net/techtips/sql-injection.html).Try using a parameter
marker, like this:

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country " & _
"WHERE ? BETWEEN IP_FROM AND IP_TO"
dim cmd
set cmd = createobject("adodb.command")
cmd.commandtype = 1 'adCmdText
cmd.commandtext=slocSQL
set cmd.activeconnection=con
set rs=cmd.execute(,array(Dot2LongIP))

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]]

Bob Barrows [MVP] wrote:

(http://mvp.unixwiz.net/techtips/sql-injection.html).Try using a
parameter

I don't know how that happened ... the link should read

http://mvp.unixwiz.net/techtips/sql-injection.html

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"

[Slim]

"Slim" <[e-mail]> wrote in message
news:en***************@tk2msftngp13.phx.gbl...

"David" <da*********@tesco.net> wrote in message
news:Sm*******************@newsfe5-gui.ntli.net...

Hi,

Getting the following error:-
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The code is as follows, and the last line is the one that produces the
error.

set con = server.createobject("adodb.connection")
con.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
server.mappath("uk.mdb") &""

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country WHERE " & Dot2LongIP & "
BETWEEN IP_FROM AND IP_TO"
Set RsLoc = con.Execute(slocSQL)

I've searched for the error on aspfaq.com, and the error message
apparently refers to inserting invalid dates. However, the data in
Dot2LongIP is a number, and the fields in the database it's trying to
look up are both columns of numerical data, and the columns are set to be
text.

I asume you are trying to compare ip numbers, you would need to treat them
as text, or remove the dots

try Cstr(IP_FROM ) Cstr(IP_TO )

Sorry i missled you, I hav shown you how to convert text to be used in
VBscript, not SQL

Bob has shown you the correct way in the same thread

I've tried

changing the columns in the database to be numeric, but for some reason
it deletes a lot of records, so that appears not to be an option.

Additional info:-

What I'm trying to do is detect visitors country, and send them to a
different version of the page if they are from the UK. I'm attempting to
use the above with the ip-2-country database that I've converted from
.csv to .mdb. Using this database seems to be a good option IF I can get
it to work! However, if anyone has any suggestions about different ways
of achieving redirect if UK visitor then I'd be happy to hear it. I've
looked at various commercial options, but all are way out of what I can
financially justify.

I don't work with asp very often, so I'd be grateful for any advice or
assistance!

[David]

Thanks for the reply - ' " & is about the only combination of operators I
didn't try!!!
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eG****************@TK2MSFTNGP15.phx.gbl...

David wrote:

Hi,

Getting the following error:-
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

The code is as follows, and the last line is the one that produces the
error.

set con = server.createobject("adodb.connection")
con.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&
server.mappath("uk.mdb") &""

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country WHERE " & Dot2LongIP
& " BETWEEN IP_FROM AND IP_TO"
Set RsLoc = con.Execute(slocSQL)

Yet another problem caused by the use of dynamic sql ;-)
The ip fields are text, so you need to compare them with a string - you
tell the query engine that you are using a string by surrounding it
(delimiting it) with quotes.
" ... WHERE '" & Dot2LongIP & "' ... "

Here is some more infor:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

However, that's the hard way to do it. The better way is to use
parameters. Why? Because using parameters means you never have to deal
with these delimiter issues again, as well as never again leaving
yoursself open to the risks of sql injection
(http://mvp.unixwiz.net/techtips/sql-injection.html).Try using a parameter
marker, like this:

slocSQL = "SELECT COUNTRY_CODE3 FROM ip_country " & _
"WHERE ? BETWEEN IP_FROM AND IP_TO"
dim cmd
set cmd = createobject("adodb.command")
cmd.commandtype = 1 'adCmdText
cmd.commandtext=slocSQL
set cmd.activeconnection=con
set rs=cmd.execute(,array(Dot2LongIP))

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"