473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Peculiar data mismatch SQL Statement issue in ASP...

15
Dear friends,

I am connecting an ASP page to an Access table. The table in Access has a field called orderNo which is of type "Number". Now...what I want to do is design an SQL statement in the ASP page which picks up all records from the table where username and orderNo match the values that are being stored into the variables inside the ASP page.

Let me illustrate.

someFile.asp
-----------------
Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. Dim orderNo, username
  4.  
  5. orderNo = Request.QueryString("orderNo")
  6. username = Request.QueryString("username")
  7.  
  8. Dim conn,SQLQuery, data_source
  9.  
  10. data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "&_
  11. Server.MapPath("orders.mdb")
  12.  
  13. SQLQuery = "select description,qty,price from purchase where Username = '"&UserName&"' AND orderNo = '"&orderNo&"'"
  14. Set conn = Server.CreateObject("ADODB.Connection")
  15. conn.open(data_source)
  16.  
  17. set rs = conn.Execute(SQLQuery)
  18.  
  19. %>
  20.  
The above SQL statement is giving me an error because I am comparing the second parameter with an integer value. The error is the following...

..............
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.
..............


When I replace the orderNo with another text based field from the table it works just fine. :(

Please help.

Sk
Sep 14 '07 #1
2 1412
ilearneditonline
130 Expert 100+
Dear friends,
Expand|Select|Wrap|Line Numbers
  1.  
  2. SQLQuery = "select description,qty,price from purchase where Username = '"&UserName&"' AND orderNo = '"&orderNo&"'"
  3.  
  4. %>
  5.  
..............
Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.
..............


Sk
Try ...

Expand|Select|Wrap|Line Numbers
  1. SQLQuery = "select description,qty,price from purchase where Username = '"&UserName&"' AND orderNo = "&orderNo
Since orderNo is a number, you don't use the single quotes. Single quotes makes it a string value and that would be a data type mismatch.
Sep 14 '07 #2
irkahs
15
Try ...

Expand|Select|Wrap|Line Numbers
  1. SQLQuery = "select description,qty,price from purchase where Username = '"&UserName&"' AND orderNo = "&orderNo
Since orderNo is a number, you don't use the single quotes. Single quotes makes it a string value and that would be a data type mismatch.
Ah! It is always something extraordinarily simple. Yes. That was it. :)

Thank you friend. You saved me a lot of time and effort.


Sk
Sep 15 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: shank | last post by:
What is the proper use for CInt ..? I get a type mismatch error whether I use it or not. I assumed I needed it because I need to make sure Request("Qty") and Request("Weight") were integers. ...
1
by: LJgrnl | last post by:
I've got a type mismatch error that's driving me nutty. Variable blnNoData has the initial value False. If a recordset comes back empty (both .EOF and ..BOF are true) then blnNoData is set to...
7
by: Jack | last post by:
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...
2
by: Chicken Kebab Abdullah | last post by:
Does anyone know why I get the error 3464 Data type mismatch from the following code. I have a form with a combo(to choose a consumable) and 2 list boxes on it. list on left is all printers...
0
by: news.paradise.net.nz | last post by:
I have been developing access databases for over 5 years. I have a large database and I have struck this problem with it before but can find nothing in help or online. Access 2000 I have a query...
1
by: Brian Henry | last post by:
I have an access database, and one of the fields in the table I am inserting into has a date/time data type. What is the correct OleDb data type to insert the date and time that it is at the moment...
2
by: psychomad | last post by:
Please, can someone help me out to solve this error, i've been searching throughout my codes and yet i didnt succeed in finding the error!!!! The Error is: Server Error in '/' Application....
15
by: sara | last post by:
I have a Memo field in a table to hold notes from a conversation a social worker has had with a client (this is for a non-profit). If the user needs to update the memo field, I need to find the...
9
by: nixonmg | last post by:
When the Command Button "Notify" is clicked, I am wanting to send out an email to the user with appropriate information in the email (works great), check the "Notified" check box (does not work), and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.