Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data type mismatch in criteria expression.

Status
Not open for further replies.
KeystoneBlues

KeystoneBlues

Programmer
Jun 14, 2004
9
US
This message is killing me..


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

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

/ASP/DeleteCC.asp, line 8


My delete code is (which may be a mess by now, Ive tried so many different variations)
<html>
<body><h2>Record Deleted</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("secureorderform.mdb")
cid=Request.Form("customerID")
set rs=Server.CreateObject("ADODB.Recordset")
rs.open "SELECT * FROM Results WHERE customerID='" & cid & "'",conn
sql="DELETE FROM Results"
sql=sql & " WHERE customersID='" & cid & "'"
response.write("Record " & cid & " was deleted!")
conn.close
%></body>
</html>

I know what it means.. I just cannot find any information that Can tell me what the proper parameters for numeric chars and text are.
I know the single quotes have something to do with it. But Ive tried every which way but loose and still every error message in the book has come up, and when I finaly get it right, i get the data type mismatch error..

My 1st field is numeric, the customerID. Thats my primary field that is autonumber. Others will be vary from text to numeric.

Please help if you can. Link is here

I am about a week old to asp programming. So far I love it but this is killing me. All of this to simply delete a record. Anyway, any help would be appriciated.
 
Sort by date Sort by votes
proper parameters for numeric chars and text are
For text, single quote: 'the text'
For date, hash: #mm/dd/yyyy# or #yyyy-mm-dd#
Note, single quote for SQL server dates
Numeric: No surrounding char

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Thank You. That answers that question. And my code was a mess, i cleaned it up and now I'm getting
Delete Record
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables.

/ASP/DeleteCC.asp, line 9


Can you tell me what I am missing? Maybe this code is just hopeless or I'm doing something completly wrong. I start class for asp in the Fall but I really need to get this done very soon. So I cannot wait for class.. It's important. So any help will be appriciated.

<html>
<body><h2>Delete Record</h2>
<%
set conn=Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("secureorderform.mdb")
cid=Request.Form("customerID")
set rs=Server.CreateObject("ADODB.Recordset")
sql="DELETE FROM Results"
Rs.Open sql, conn
sql=sql & " WHERE customerID=" & cid
response.write("Record " & cid & " was deleted!")
conn.close
%></body>
</html>
 
Upvote 0 Downvote
Terminate the sql string building before opening the recordset !
And as always, BACKUP, BACKUP and BACKUP !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Hmm, what would cause it to tell me record is deleted but when i go back to query it's still there?
 
Upvote 0 Downvote
You may consider to browse the Errors collection of the Connection object.
Take a look at the Command object and the Execute method too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Yea I thought about using comm.execute but didn't have any luck. It's crazy, I have everything in place for this system to work, but cannot get the delete to work. I could alter the system to perform the task i need but I'm too damn stubborn. It's something I'll just have to figure out.

Thanks for your help...
al
 
Upvote 0 Downvote
I'm not into ASP so I don't know the implications of what you are doing in that context.
But it seems to me that you are deleting everything from Results. The 'where' clause doesn't get set until the deletion (of everything) has been done.
 
Upvote 0 Downvote
I have experience with Access queries and with .asp. However, when I work with .asp I use SQL Server on the backend. In addition, I think that the syntax of the connection between .asp and the db may vary somewhat from system to system. With those cautions in mind, I will offer my advice.

First, I notice that you are using "Rs.open..." I suspect that you need to use "conn.execute..." I think that "conn" is the name of the connection. Here is some of my code for a delete sql.


Set conn = Server.CreateObject("ADODB.Connection")
ConnectDB(conn)
set rs=Server.CreateObject("ADODB.Recordset")
sql1= "DELETE FROM dbo.tableName WHERE (fieldName= '" & aspVariable & "')"
conn.Execute sql1
CloseConnection(conn)

I don't think that you need "dbo.". That's an sql server thing. Also, you may need to tinker with the character that incloses the variable. I used a single quote, but I'm not sure that that would always be appropriate.
 
Upvote 0 Downvote
Well, i feel really stupid for the result of my problem. You know I must say i didn't see too many people talk about this so I'll post the resolution just in case anyone else is having a problem with this action.

You all of course were correct, i took out " " around the ID and that was OK. But, I had created a test folder to do all of my testing in so that i did not interfer with any of the other forms or database files. Well, I didn't include the Global.asa file! And I wasn't using conn.execute.

so use conn.execute and make sure your global.asa file is in the root directory.

Thanks all for your help. It's Miller Time!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question
Query expression negative to a 0
Replies
6
Views
588
dhookom
dhookom
C
  • Locked
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
510
OzFoxy
OzFoxy
combo
  • Locked
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
2K
combo
combo
BugZap13
  • Locked
  • Question
Need help creating a date from two fields to be used in where clause
Replies
5
Views
762
mikrom
mikrom
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
5K
combo
combo

Part and Inventory Search

Sponsor

Back
Top