SQL script help

isaac2004

hi i have a page that inclued a SQL statement that takes a user input
from a form and displays the request from the database. every time i
try to open the page i get an error message that says Data type
mismatch in criteria expression where i open the record set. usually
this means that you have something as a number when it shouldnt be but
i dont know what is wrong. \
here is my code

<% Option Explicit %>
<html>


<head>
<title>Query the Clothier Database</title>
</head>

<body>
<center>
<h3>Query the Clothier Database</h3><hr>
<p> <b>List all items supplied by a vendor.</b></p>

<% dim strSQL, objRS, strVendorID
strVendorID = request.querystring("strVendorID")
strSQL = "SELECT Vendors.VendorID, Vendors.VendorName,
Items.ItemName, Items.ItemPriceBuy " & _
"FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID " & _
"WHERE (((Vendors.VendorID) = '" & strVendorID & "')) " &
_
"ORDER BY Items.ItemName "

response.write strSQL
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, objConn

%>

Use either form:
<div align="center">
<center>
<table border="0" cellpadding="3" cellspacing="0" width="400">
<tr>
<td align="center" bgcolor="#FFFFCC">
<form method="GET" >
<p>Vendor ID (1-4)</p>
<p><input type="text" name="strVendorID" size="1"
maxlength="1"></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
<td width="100"> </td>
<td align="center" bgcolor="#FFFFCC">
<form method="GET">
<p>Vendor ID</p>
<p>
<select size="1" name="strVendorID">
<option selected value="1">Vendor 1</option>
<option value="2">Vendor 2</option>
<option value="3">Vendor 3</option>
<option value="4">Vendor 4</option>
</select></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
</tr>
</table>

<p align="center">What advantage does the right-hand form have?</p>



<%
if strVendorID > 0 then

response.write"<hr>All items by Vendor " & strVendorID & "<br>
Ordered by Item Name.<hr>"
response.write"<table border='1' cellpadding='3' cellspacing='0'
bordercolor='#BBDBE6'>"
response.write"<TR>"
response.write"<td><b>VendorID</b></td>"
response.write"<TD><b>Vendor Name</b></td>"
response.write"<td><b>Item Name</b></td>"
response.write"<td><b>Price</b></td>"
response.write"</tr>"
Do While Not objRS.EOF

response.write"<tr>"
response.write"<td>" & objRS("VendorID") & " </td>"
response.write"<td>" & objRS("VendorName") & " </td>"
response.write"<td>" & objRS("ItemName") & " </td>"
response.write"<td>" & objRS("ItemPriceBuy") & " </td>"
response.write"</tr>"
objRS.MoveNext
Loop
else if strVendor > 4 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try again.<br><br>"
else if strVendor < 1 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try again.<br><br>"

end if
end if
end if

%>

</body>

</html>

the problem is with the actual SQL statement but the syntax looks fine
to me.
any help would be greatly appreciated

Feb 9 '06 #1

Subscribe Post Reply

1493

Dave Anderson

Care to tell us which line generates the error?
isaac2004 wrote:

hi i have a page that inclued a SQL statement that takes a user input
from a form and displays the request from the database. every time i
try to open the page i get an error message that says Data type
mismatch in criteria expression where i open the record set. usually
this means that you have something as a number when it shouldnt be but
i dont know what is wrong. \
here is my code

<% Option Explicit %>
<html>


<head>
<title>Query the Clothier Database</title>
</head>

<body>
<center>
<h3>Query the Clothier Database</h3><hr>
<p> <b>List all items supplied by a vendor.</b></p>

<% dim strSQL, objRS, strVendorID
strVendorID = request.querystring("strVendorID")
strSQL = "SELECT Vendors.VendorID, Vendors.VendorName,
Items.ItemName, Items.ItemPriceBuy " & _
"FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID " & _
"WHERE (((Vendors.VendorID) = '" & strVendorID & "')) " &
_
"ORDER BY Items.ItemName "

response.write strSQL
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, objConn

%>

Use either form:
<div align="center">
<center>
<table border="0" cellpadding="3" cellspacing="0" width="400">
<tr>
<td align="center" bgcolor="#FFFFCC">
<form method="GET" >
<p>Vendor ID (1-4)</p>
<p><input type="text" name="strVendorID" size="1"
maxlength="1"></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
<td width="100"> </td>
<td align="center" bgcolor="#FFFFCC">
<form method="GET">
<p>Vendor ID</p>
<p>
<select size="1" name="strVendorID">
<option selected value="1">Vendor 1</option>
<option value="2">Vendor 2</option>
<option value="3">Vendor 3</option>
<option value="4">Vendor 4</option>
</select></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
</tr>
</table>

<p align="center">What advantage does the right-hand form have?</p>



<%
if strVendorID > 0 then

response.write"<hr>All items by Vendor " & strVendorID & "<br>
Ordered by Item Name.<hr>"
response.write"<table border='1' cellpadding='3' cellspacing='0'
bordercolor='#BBDBE6'>"
response.write"<TR>"
response.write"<td><b>VendorID</b></td>"
response.write"<TD><b>Vendor Name</b></td>"
response.write"<td><b>Item Name</b></td>"
response.write"<td><b>Price</b></td>"
response.write"</tr>"
Do While Not objRS.EOF

response.write"<tr>"
response.write"<td>" & objRS("VendorID") & " </td>"
response.write"<td>" & objRS("VendorName") & " </td>"
response.write"<td>" & objRS("ItemName") & " </td>"
response.write"<td>" & objRS("ItemPriceBuy") & " </td>"
response.write"</tr>"
objRS.MoveNext
Loop
else if strVendor > 4 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try
again.<br><br>"
else if strVendor < 1 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try
again.<br><br>"

end if
end if
end if

%>

</body>

</html>

the problem is with the actual SQL statement but the syntax looks fine
to me.
any help would be greatly appreciated

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

Feb 9 '06 #2

isaac2004

the line that reads
objRS.Open strSQL, objConn
the one that actual opens the record set

Feb 9 '06 #3

Bob Barrows [MVP]

isaac2004 wrote:

hi i have a page that inclued a SQL statement that takes a user input
from a form and displays the request from the database. every time i
try to open the page i get an error message that says Data type
mismatch in criteria expression where i open the record set. usually
this means that you have something as a number when it shouldnt be but
i dont know what is wrong. \
here is my code

<% Option Explicit %>
<html>

 <snip of irrelevant stuff> strSQL = "SELECT Vendors.VendorID, Vendors.VendorName,
Items.ItemName, Items.ItemPriceBuy " & _
"FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID " & _
"WHERE (((Vendors.VendorID) = '" & strVendorID & "')) "
& _
"ORDER BY Items.ItemName "

response.write strSQL
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, objConn
<snip of irrelevant stuff> the problem is with the actual SQL statement but the syntax looks fine
to me.

So why do you think the sql statement is incorrect?

I'm more inclined to believe that you don't have a valid/open connection
object here.

What is the result of
response.write typename(objConn)?

If the result is "object", then what is the result of this:

if isobject(objConn) then
response.write objConn.State
end if

Bob Barrows
PS. Check here for a better way of including the ado constant definitions in
your pages:
http://www.aspfaq.com/show.asp?id=2112
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Feb 9 '06 #4

isaac2004

what do you mean by this that line opens the record set and opens a
connection the SQL statement with dynamic response is whats wrong
because if i make it statci it works tr out the code youll see what i
mean i just dont know how to fix it

Feb 9 '06 #5

Bob Barrows [MVP]

isaac2004 wrote:

what do you mean by this that line opens the record set and opens a
connection the SQL statement with dynamic response is whats wrong
because if i make it statci it works tr out the code youll see what i
mean i just dont know how to fix it

Could you
1. quote what you are replying to and
2. make an effort to use proper grammar?

What were the results of the response.writes I asked you to try?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Feb 9 '06 #6

Bob Barrows [MVP]

isaac2004 wrote:

what do you mean by this that line opens the record set and opens a
connection
If this means what I think it means, then you are using an implicit
connection which is bad technique.
Always use an explicit connection object. There are many reasons for this,
including the fact that using implicit connections wastes resources and
impairs scalability by preventing the use of session pooling.

The wrong way:
ConnString = "Provider= ..."
rs.open sql, ConnString

The right way:
ConnString = "Provider= ..."
dim cn
Set cn=createobject("adodb.connection")
cn.open ConnString

This gives you a connection object that can be used as many times as needed
in your page. When finished using it, you release it back to the session
pool by:
cn.close: set cn=nothing
the SQL statement with dynamic response is whats wrong
because if i make it statci it works tr out the code youll see what i
mean
How can I try it to see what you mean? I don't have your database or
anything ...
i just dont know how to fix it

Show us the result of
response.write strSQL

And show us the entire error message, not just the end part.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Feb 9 '06 #7

isaac2004

hello sorry for all the bad grammar. the full error code is

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression.

/06Winter/levini/A06/VendorSelect.asp, line 25
<% Option Explicit %>
<html>


<head>
<title>Query the Clothier Database</title>
</head>
<body>
<center>
<h3>Query the Clothier Database</h3><hr>
<p> <b>List all items supplied by a vendor.</b></p>
<% dim strSQL, objRS, strVendorID
strVendorID = request.querystring("strVendorID")
strSQL = "SELECT Vendors.VendorID, Vendors.VendorName,
Items.ItemName, Items.ItemPriceBuy " & _
"FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID " & _
"WHERE (((Vendors.VendorID) = '" & strVendorID & "')) " &

_
"ORDER BY Items.ItemName "
response.write strSQL
Set objRS = Server.CreateObject ("ADODB.Recordset")

----------------------------------------------------------this is line
25
objRS.Open strSQL, objConn
-------------------------------------------------------------------------------------

%>
Use either form:
<div align="center">
<center>
<table border="0" cellpadding="3" cellspacing="0" width="400">
<tr>
<td align="center" bgcolor="#FFFFCC">
<form method="GET" >
<p>Vendor ID (1-4)</p>
<p><input type="text" name="strVendorID" size="1"
maxlength="1"></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
<td width="100"> </td>
<td align="center" bgcolor="#FFFFCC">
<form method="GET">
<p>Vendor ID</p>
<p>
<select size="1" name="strVendorID">
<option selected value="1">Vendor 1</option>
<option value="2">Vendor 2</option>
<option value="3">Vendor 3</option>
<option value="4">Vendor 4</option>
</select></p>
<p><input type="submit" value="Submit Query"></p>
</form>
</td>
</tr>
</table>
<p align="center">What advantage does the right-hand form have?</p>

<%
if strVendorID > 0 then
response.write"<hr>All items by Vendor " & strVendorID & "<br>
Ordered by Item Name.<hr>"
response.write"<table border='1' cellpadding='3' cellspacing='0'
bordercolor='#BBDBE6'>"
response.write"<TR>"
response.write"<td><b>VendorID</b></td>"
response.write"<TD><b>Vendor Name</b></td>"
response.write"<td><b>Item Name</b></td>"
response.write"<td><b>Price</b></td>"
response.write"</tr>"
Do While Not objRS.EOF
response.write"<tr>"
response.write"<td>" & objRS("VendorID") & " </td>"
response.write"<td>" & objRS("VendorName") & " </td>"
response.write"<td>" & objRS("ItemName") & " </td>"
response.write"<td>" & objRS("ItemPriceBuy") & " </td>"
response.write"</tr>"
objRS.MoveNext
Loop
else if strVendor > 4 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try again.<br><br>"

else if strVendor < 1 then
response.write"<hr>"
response.write" All items by Vendor " & strVendorID & "<br> Ordered by
Item Name."
response.write"<hr>"
response.write"No Records match your search. Please try again.<br><br>"

end if
end if
end if
%>
</body>
</html>

response.write strSQL outputs

SELECT Vendors.VendorID, Vendors.VendorName, Items.ItemName,
Items.ItemPriceBuy FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID WHERE (((Vendors.VendorID) = '')) ORDER BY
Items.ItemName

the order of operation stops when the record set is first opened up
with

Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, objConn

everything else is done up until that point
objRs.Open strSQL opens the SQL statement that is shown with the above
response.write
and objConn opens the Connection to the dataabse.

i hope that is what you needed, thanks for the help

Feb 10 '06 #8

Bob Barrows [MVP]

isaac2004 wrote:

hello sorry for all the bad grammar. the full error code is

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
This is the part that would have steered me away from the wrong conclusion I
made yesterday.

It has nothing to do with your error, but you should read this:
http://www.aspfaq.com/show.asp?id=2126

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression.

/06Winter/levini/A06/VendorSelect.asp, line 25

<snip of irrelevent stuff - this is an asp group - showing us your html does
not help us solve your problem, in most cases>
<% dim strSQL, objRS, strVendorID
strVendorID = request.querystring("strVendorID")
Do you expect request.querystring("strVendorID") to contain a value? The
result of the response.write shown below shows that it did not contain a
value. This is part of your problem: you need to determine why the
querystring value is empty if you expect it to contain something.

strSQL = "SELECT Vendors.VendorID, Vendors.VendorName,
Items.ItemName, Items.ItemPriceBuy " & _
"FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID " & _
"WHERE (((Vendors.VendorID) = '" & strVendorID & "')) " &

_
"ORDER BY Items.ItemName "
response.write strSQL
Set objRS = Server.CreateObject ("ADODB.Recordset")

----------------------------------------------------------this is line
25
objRS.Open strSQL, objConn
-------------------------------------------------------------------------------------

%>

<more snippage>
response.write strSQL outputs

SELECT Vendors.VendorID, Vendors.VendorName, Items.ItemName,
Items.ItemPriceBuy FROM Items INNER JOIN Vendors ON Items.VendorID =
Vendors.VendorID WHERE (((Vendors.VendorID) = '')) ORDER BY
Items.ItemName

Well, the error seems to indicate that VendorID is a number. You are
comparing it to a string which is causing the type mismatch. If VendorID is
numeric, you should not be surrounding the value you are comparing it to
with quotes. Using quotes tells the query engine that the value you are
supplying is a string. Yes, Jet will sometimes help you recover from this
mistake by performing an implicit conversion to make the datatype match, but
when the string is empty, the implicit conversion fails and you get the
datatype mismatch. Here are the rules for using delimiters, as well as a
suggestion of a better way to be doing this (using parameters):
http://groups.google.com/group/micro...UTF-8&oe=UTF-8

Here is more about using parameters with saved parameter queries:
http://groups-beta.google.com/group/...d322b882a604bd

And here is an alternative method if you wish to avoid using saved parameter
queries for some reason:
http://groups-beta.google.com/group/...e36562fee7804e

After you fix your sql statement and/or your method of putting your
strVendorID value into it, your next step eill be to determine why
strVendorID is empty. I see nothing wrong with your data submission form.

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"

Feb 10 '06 #9

isaac2004

>Do you expect request.querystring("strVendorID") to contain a value? The
result of the response.write shown below shows that it did not contain
a
value. This is part of your problem: you need to determine why the
querystring value is empty if you expect it to contain something.

i have a if then statement that validates a null value for strVendorID
when the page is launched but the script never gets that far because
the code is scripted before the connection to the database is made

Feb 10 '06 #10

by: dpackwood | last post by:

Hello, I have two different scripts that do pretty much the same thing. The main perl script is on Windows. It runs and in the middle of it, it then calls out another perl script that then...

Perl

Script to calculate remaining minutes

by: hupjack | last post by:

I finally joined the millions of cell phone users out there. I'm the 2nd phone on what is now a family share plan. (Our two cell phones use minutes from a central 400 minute peak time pool.)...

Perl

Script-generated code displaying differntly than the exact same code displayed staticly... help!?!?!

by: Akbar | last post by:

Hey there, Big-time curiosity issue here... Here's the test code (it's not that long)... it's to display a large number of image links with captions, ideally pulled in from an external file...

Javascript

Need help with this script please..

by: Johnny Knoxville | last post by:

I've added a favicon to my site (http://lazyape.filetap.com/) which works fine if you add the site to favourites the normal way, but I have some JavaScript code on a couple of pages with a link,...

Javascript

help please - script problem in IE 6 - (but not in Mozilla/Firefox)

by: Derek | last post by:

I have the following script in a page and it gets an error in IE 6. Says something about an invalid argument but the line number doesn't help since I can't see the javascript code when viewing...

Javascript

Erratic Sever Side Script Debugging ASP 3.0/.NET DLLs: Problems and Solution

by: ZMan | last post by:

Scenario: This is about debugging server side scripts that make calls to middle-tier business DLLs. The server side scripts are legacy ASP 3.0 pages, and the DLLs are managed DLLs...

ASP.NET

What is client startup script?

by: Harry Smith | last post by:

While reading the documentation on IsStartupScriptRegistered, there is a reference to "client startup script" as "Determines if the client startup script is registered with the Page object." What...

ASP.NET

How to work out which script to call to Save

by: Angus | last post by:

I have a web page with a toolbar containing a Save button. The Save button can change contextually to be a Search button in some cases. Hence the button name searchsavechanges. The snippet of...

Javascript

Re: Script Optimization

by: David | last post by:

On Sun, May 4, 2008 at 4:43 AM, lev <levlozhkin@gmail.comwrote: Hi, I started tidying up the script a bit, but there are some parts I don't understand or look buggy. So I'm forwarding you the...

Python

How to Make a File Download Script with Perl

by: KevinADC | last post by:

Note: You may skip to the end of the article if all you want is the perl code. Introduction Many websites have a form or a link you can use to download a file. You click a form button or click...

Perl

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Similar topics