Ask a Question related to Coldfusion Database Access, Design and Development.
-
jrink1350 #1
Datatype mismatch in criteria expression
All,
Any help you can lend on this would be greatly appreciated. I am not a
coldfusion coder by any means and have stumbled into this problem at a client
and they need someone to help me troubleshoot. I believe a knowledgeable
person can diagnose this in a matter of 5 or 10 minutes.
In a nutshell, there is a .cfm page which contains a form and some information
for the user to fill out in textboxes (and one drop down box). At the bottom
of the form is a submit button which then is supposed to send the inputted
information entered by the user to the MS Access DB. However, clicking Submit
returns a Datatype Mismatch in Criteria Expression error.
[url]http://www.w3si.org/images/jrink/error.jpg[/url]
I've attached the code from the .cfm form page... If anyone wants I can email
them the actual DB too. Basically, this .cfm page uses the "Events' table in
the database. There are several fields, most of which are set as "text" data
types, and some fields with "date/time (long date)" data type... I believe the
data mismatch criteria problem is related to these date/time data types.
If you can help, please let me know. I would be more than willing to contact
you on the phone if you think that would help. I can't imagine this would take
long for someonoe skilled in coldfusion to figure out. THANKS ALOT!!
JR
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Admin</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<cfif ParameterExists(form.save)>
<cfset form.StartTime = CreateODBCDateTime('2000-12-30 ' & form.StartTime) >
<cfset form.endtime = CreateODBCDateTime('2000-12-30 ' & form.endtime) >
<cfif ParameterExists(url.recordid)>
<!--- Edit --->
<cfparam name="form.recordid" default="#url.recordid#">
<cfset form.recordid = url.recordid>
<cfdump var="#form#">
<cfupdate datasource="#database#" tablename="Events" dbtype="ODBC"
formfields="recordid,InternalMeeting,EventDate, StartTime, endtime,
Description, school, Sport, type, Grade, Gender,Location,agenda,Minutes">
<cflocation url="board.meetings.main.cfm" addtoken="No">
<cfelse>
<!--- Add --->
<cfinsert datasource="#database#" tablename="Events" dbtype="ODBC"
formfields="EventDate, StartTime, endtime, Description, school, Sport, type,
Grade, Gender,Location,agenda,Minutes">
<cflocation url="board.meetings.main.cfm" addtoken="No">
</cfif>
</cfif>
</head>
<cfinclude template="incl/incl.adminheader.cfm">
<table width="95%" border="0" align="center" cellpadding="3" cellspacing="3">
<tr>
<td><b>Board Admin</b></td>
</tr>
</table>
<table width="95%" border="0" align="center" cellpadding="3" cellspacing="3">
<tr>
<td>
<!--- Start --->
<cfif ParameterExists(url.recordid)>
<cfquery name="getevents" datasource="#database#">
SELECT recordid,EventDate, StartTime, endtime, Description, school,
Sport, type, Grade, Gender,Location,agenda,Minutes
FROM Events
where recordid = #url.recordid#
order by EventDate DESC, StartTime
</cfquery></cfif>
<cfset queryname = "getevents">
<form action="<cfoutput>#sn#<cfif
ParameterExists(url.recordid)>?recordid=#url.recor did#</cfif></cfoutput>"
method="post">
Meeting Type<br>
<cfset var = "type">
<select name="<cfoutput>#var#</cfoutput>">
<!--- default Value --->
<option value="">Meeting Type</option>
<cfset Displaylist ="Board - Monthly,Board - Curriculum & Instruction
Committee,Board - Pupil Services Committee,Board - Finance Committee,Board -
Personnel & HR Committee,Board - Buildings & Grounds Committee,Board -
Communications & PR Committee,Board - Special Meeting,Board - Executive,Board -
Monthly Committee Meeting 1,Board - Monthly Committee Meeting 2">
<!---<cfset Valueslist = "0,1,2"> --->
<!--- If value list is the same as the display list --->
<cfset Valueslist = Displaylist >
<cfloop index="i" list="#Displaylist#">
<cfset value=ListGetAt(Valueslist, ListFind(Displaylist, i))>
<option value="<cfoutput>#value#</cfoutput>" <cfoutput><cfif
IsDefined("form.#var#")><cfif listfindnocase(Evaluate("form." & var),value) >
selected</cfif><cfelseif IsDefined(queryname &'.'& var) and
listfindnocase(Evaluate(queryname &'.'& var), value)>
selected<cfelse></cfif></cfoutput>><cfoutput>#i#</cfoutput></option>
</cfloop>
</select> <br><br>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="150">Date: (01/01/2002)</td>
<td width="200">Start Time: (12:00 AM)</td>
<td width="150">End Time: (1:45 PM)</td>
</tr>
<tr>
<td><cfset var = "EventDate">
<input type="text" maxlength="10" name="<cfoutput>#var#</cfoutput>"
value="<cfoutput><cfif IsDefined("form.#var#")>#DateFormat(Evaluate("form ." &
var), "mm/dd/yy")#<cfelseif IsDefined(queryname &'.'&
var)>#DateFormat(Evaluate(queryname &'.'& var),
"mm/dd/yy")#<cfelse></cfif></cfoutput>" size="12">
<br><br></td>
<td><cfset var = "StartTime">
<input name="<cfoutput>#var#</cfoutput>" type="text" value="<cfoutput><cfif
IsDefined("form.#var#")>#TimeFormat(Evaluate("form ." & var) , "h:mm
tt")#<cfelseif IsDefined(queryname &'.'& var)>#TimeFormat(Evaluate(queryname
&'.'& var) , "h:mm tt")#<cfelse></cfif></cfoutput>" size="12" maxlength="10">
<br><br></td>
<td><cfset var = "EndTime">
<input type="text" maxlength="10" name="<cfoutput>#var#</cfoutput>"
value="<cfoutput><cfif IsDefined("form.#var#")>#TimeFormat(Evaluate("form ." &
var) , "h:mm tt")#<cfelseif IsDefined(queryname &'.'&
var)>#TimeFormat(Evaluate(queryname &'.'& var) , "h:mm
tt")#<cfelse></cfif></cfoutput>" size="12">
<br><br></td>
</tr>
</table>
Location<br>
<cfset var = "Location">
<input type="text" size="43" maxlength="255" name="<cfoutput>#var#</cfoutput>"
value="<cfoutput><cfif IsDefined("form.#var#")>#Evaluate("form." &
var)#<cfelseif IsDefined(queryname &'.'& var)>#Evaluate(queryname &'.'&
var)#<cfelse></cfif></cfoutput>" >
<br><br>
Agenda (Enter PDF file name)<br>
<cfset var = "agenda">
<input type="text" size="30" maxlength="255" name="<cfoutput>#var#</cfoutput>"
value="<cfoutput><cfif IsDefined("form.#var#")>#Evaluate("form." &
var)#<cfelseif IsDefined(queryname &'.'& var)>#Evaluate(queryname &'.'&
var)#<cfelse></cfif></cfoutput>" >
<br><br>
Minutes (Enter PDF file name)<br>
<cfset var = "minutes">
<input type="text" size="30" maxlength="255" name="<cfoutput>#var#</cfoutput>"
value="<cfoutput><cfif IsDefined("form.#var#")>#Evaluate("form." &
var)#<cfelseif IsDefined(queryname &'.'& var)>#Evaluate(queryname &'.'&
var)#<cfelse></cfif></cfoutput>" >
<br><br><input type="submit" name="save" value="Save">
</form>
<!--- End --->
</td>
</tr>
<tr>
<td> </td>
</tr>
</table>
</body>
</html>
jrink1350 Guest
-
Access database getting mismatch data on criteria
Hello, It's been a while since I used ad Access DB with CF so, forgive my memory lapse. But, can anyone explain why this query is throwing the... -
Arithmetic overflow error converting expression to datatype datetime
Hi all, I have a web application based on CFMX 6.1, IIS 5 and MS-SQL 8.0 On my development server the application works fine. On my production... -
Data type mismatch in criteria expression
the Access database data typs for the 'active' and 'officer' fields are datatype Yes/No ---------------------------------------EmTiDOHActOff.cfm... -
I think it's a datatype issue
I cannot seem to get this entry to be accepted into the intended table. I am getting the common error message which tells me cannot be found in... -
Type mismatch in expression
Dear anyone, I used the wizard to make a form, but when I try to go from design view to form view an eooro message comes up "Type mismatch in... -
reenaroy #2 Re: Datatype mismatch in criteria expression
The following example is working for me. I have done the following changes
<cfparam name="form.InternalMeeting" default="">
<cfparam name="form.Description" default="">
<cfparam name="form.school" default="">
<cfparam name="form.Sport" default="">
<cfparam name="form.Grade" default="">
<cfparam name="form.Gender" default="">
<cfupdate datasource="#database#" tablename="Events" dbtype="ODBC"
formfields="recordid,InternalMeeting,EventDate, StartTime, endtime,
Description, school, Sport, type, Grade, Gender,Location,agenda,Minutes">
specify the default value which is not existing as the form fields.
reenaroy Guest
-
BKBK #3 Re: Datatype mismatch in criteria expression
- The function ParameterExists() is deprecated since the arrival of Coldfusion
MX. If you're on MX or above, use
<cfif isDefined("form.save")> in place of <cfif ParameterExists(form.save)>
(that may not be the cause of your problem, though);
- Include some code, before cfupdate and cfinsert, to validate the form data
before it is sent to the database. For example, ensure that the input
representing a date is actually a valid date object, that input for a number is
numeric, etc. ;
- Use the alternative form of inserting data, which follows, in the hope that
Coldfusion will tell you which column is causing the problem. I have assumed
that EventDate, StartTime and endtime are dates and that Minutes is an integer,
hence these values have no single-quotes around them.
<cfquery name="insertQuery" datasource="#database#">
INSERT into Events (EventDate, StartTime, endtime, Description, school,
Sport, type, Grade, Gender,Location,agenda,Minutes)
VALUES
(#createODBCDate(form.EventDate)#,#form.StartTime# ,#form.endtime#,'#form.Descrip
tion#','#form.school#','#form.Sport#','#form.type# ','#form.Grade#','#form.Gender
#','#form.Location#','#form.agenda#',#form.Minutes #)
</cfquery>
BKBK Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
