SQL script help

I

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>
<!--#include file="DatabaseConnect.asp"-->
<!--#include virtual="/06Winter/levini/database/adovbs.inc"-->
<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">&nbsp;</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>

<!-- Response
************************************************************ -->

<%
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") & "&nbsp;</td>"
response.write"<td>" & objRS("VendorName") & "&nbsp;</td>"
response.write"<td>" & objRS("ItemName") & "&nbsp;</td>"
response.write"<td>" & objRS("ItemPriceBuy") & "&nbsp;</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
 
I

isaac2004

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

Bob Barrows [MVP]

isaac2004 said:
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>
<!--#include file="DatabaseConnect.asp"-->
<!--#include virtual="/06Winter/levini/database/adovbs.inc"-->
Click to expand...
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
Click to expand...
the problem is with the actual SQL statement but the syntax looks fine
to me.
Click to expand...

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
 
I

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
 
B

Bob Barrows [MVP]

isaac2004 said:
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
Click to expand...

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?
 
B

Bob Barrows [MVP]

isaac2004 said:
what do you mean by this that line opens the record set and opens a
connection
Click to expand...

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
Click to expand...

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
Click to expand...

Show us the result of
response.write strSQL

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

Bob Barrows
 
I

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>
<!--#include file="DatabaseConnect.asp"-->
<!--#include virtual="/06Winter/levini/database/adovbs.inc"-->
<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>


<!-- Response
************************************************************ -->


<%
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
 
B

Bob Barrows [MVP]

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

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
Click to expand...

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
Click to expand...
<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")
Click to expand...

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
Click to expand...
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
Click to expand...


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/micr.../713f592513bf333c?hl=en&lr=&ie=UTF-8&oe=UTF-8

Here is more about using parameters with saved parameter queries:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

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

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
 
I

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
 

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

Similar Threads

Registration form 13
Help with code 0
Help needed with thank you message 5
I need help fixing my website 2
help with SQL Insert and delete 0
SQL statement help 0
How to have two html audio players on one page? 0
Help with Visual Lightbox: Scripts 2

Members online

No members online now.
Total: 59 (members: 0, guests: 59)
Robots: 430

Forum statistics

Threads
473,990
Messages
2,570,211
Members
46,796
Latest member
SteveBreed

Latest Threads

Top