470,596 Members | 1,609 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

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

Post your reply

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

Similar topics

2 posts views Thread by shank | last post: by
1 post views Thread by LJgrnl | last post: by
2 posts views Thread by Chicken Kebab Abdullah | last post: by
reply views Thread by news.paradise.net.nz | last post: by
1 post views Thread by Brian Henry | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.