Guest jcs1987 Posted October 8, 2008 Share Posted October 8, 2008 Hi guys,I've been working on some very simple code to enter details into an access database. When I run my code its displays the following error. Microsoft JET Database Engine error '80040e07'Data type mismatch in criteria expression.The line that it states in the error is Set objRecordset=objConnection.Execute(sql) This is what my code looks like currently as i am troubleshooting the error <html><body><%dim DVDs, DVD_id, Branch_id, DVD_title, Director,Main_actor, Supporting_actor, Synopsis, Genre, Rental_costDim objConnectionDim objRecordsetSet objConnection= Server.CreateObject("ADODB.Connection")Set objRecordset= Server.CreateObject("ADODB.Recordset")objConnection.Provider="Microsoft.Jet.OLEDB.4.0"objConnection.Open "c:\sites\content\j\c\s\jcs1987\db\The Picture House.mdb"objRecordset.open "DVDs", objConnectionsql="INSERT INTO DVDs (DVD_id ,Branch_id, DVD_title, Director, Main_actor, Supporting_actor, Synopsis, Genre, Rental_cost)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("dvdid") & "',"sql=sql & "'" & Request.Form("branchid") & "',"sql=sql & "'" & Request.Form("dvdtitle") & "',"sql=sql & "'" & Request.Form("director") & "',"sql=sql & "'" & Request.Form("mainactor") & "',"sql=sql & "'" & Request.Form("supportingactor") & "',"sql=sql & "'" & Request.Form("synopsis") & "',"sql=sql & "'" & Request.Form("genre") & "',"sql=sql & "'" & Request.Form("rentalcost") &"')"response.write(sql)response.end()Set objRecordset=objConnection.Execute(sql)If memberid<>"" Then Response.Write "Add DVD ID" Else Response.Redirect "Dvdaddsuccessful.html"end ifset objRecordset = nothing set objConnection=nothing with this the following is displayed INSERT INTO DVDs (DVD_id ,Branch_id, DVD_title, Director, Main_actor, Supporting_actor, Synopsis, Genre, Rental_cost) VALUES ('','','','','','','','','')I used the exact same code in a previous page and it works perfectly. I believe the problem here is because Rental_cost is an integer value in my database. I've tried everything I can think of to resolve the error including removing double and single quotes from the rentalcost line but I just can't get it to work. Can anyone help?Thanks Link to comment Share on other sites More sharing options...
amj Posted October 8, 2008 Share Posted October 8, 2008 I assume that you are attempting to add a blank record into your DVD db.A similar bit of code from one of my pages that adds a record into a db with data from a form is Dim strSQL ' String variable for building our query Dim objRecordset dim txtTemp Set objRecordset = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT * FROM Results WHERE 0=1;" ' creates a blank record objRecordset.Open strSQL, DB_CONNECTIONSTRING, adOpenKeyset, adLockPessimistic, adCmdText objRecordset.AddNew objRecordset.Fields("EventTitle") = CStr(request("EventTitle")) objRecordset.Fields("Forsale") = CStr(request("Forsale")) objRecordset.Fields("Name") = CStr(request("Name")) objRecordset.Fields("Notes") = CStr(request("Notes")) objRecordset.Fields("Date") = Cdate(request("Date")) objRecordset.Fields("Location") = CStr(request("Location")) iRecordAdded = objRecordset.Fields("key").Value objRecordset.Update objRecordset.Close Set objRecordset = Nothing I have other pages that update existing db values and it can be quite complex. iRecordId = Request.Form("id") iRecordId = Replace(iRecordId, "'", "''") strSQL = "UPDATE ext SET " _ & "Ext = '" & CStr(Replace(Request.Form("ext"), "'", "''")) & "', " _ & "[Owner Title] = '" & CStr(Replace(Request.Form("Owner Title"), "'", "''")) & "', " _ & "[Owner Initials] = '" & CStr(Replace(Request.Form("Owner Initials"), "'", "''")) & "', " _ & "[Owner Surname] = '" & CStr(Replace(Request.Form("Owner Surname"), "'", "''")) & "', " _ & "[Position] = '" & CStr(Replace(Request.Form("Position"), "'", "''")) & "', " _ & "Location = '" & CStr(Replace(Request.Form("Location"), "'", "''")) & "', " _ & "email = '" & CStr(Replace(Request.Form("email"), "'", "''")) & "', " _ & "Display = " & Cint(cstr(Request.Form("Display"))) & " " _ & "WHERE (id = " & iRecordId & ")"' Response.Write strSQL Set cnnDBEdit = Server.CreateObject("ADODB.Connection") cnnDBEdit.Open CONN_STRING cnnDBEdit.Execute strSQL, adAffectAll, adCmdText Or adExecuteNoRecords cnnDBEdit.Close Set cnnDBEdit = Nothing Link to comment Share on other sites More sharing options...
justsomeguy Posted October 8, 2008 Share Posted October 8, 2008 I've tried everything I can think of to resolve the error including removing double and single quotes from the rentalcost line but I just can't get it to work. Can anyone help?It's not a very good idea to use values from Request.Form directly in a SQL query. You need to sanitize the values first to make sure that someone isn't trying to hack your database. In particular, make sure that any single quotes in the input are being escaped. If your field is an int in the database then you don't want to be inserting a string, either use the value NULL if the field allows null values or use 0. But the point is to validate the data first before using it in the query, make sure everything is the correct data type and format before putting it in the query. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.