473,224 Members | 1,517 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,224 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 1408
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.