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 Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help with update 1

Status
Not open for further replies.
OccasionalCoder

OccasionalCoder

Technical User
Sep 26, 2005
36
US
I get a datatype mismatch error when I use the following update code:

<cfquery datasource="#db#" username="#un#" password="#pw#">
update copyfellows
set
first_name='#trim(form.first_name)#',
mi='#trim(form.mi)#',
last_name='#trim(form.last_name)#',
suffix='#trim(form.suffix)#',
nee='#trim(form.nee)#',
photo='#trim(form.photo)#',
agency='#trim(form.agency)#',
abbr='#trim(form.abbr)#',
division='#trim(form.division)#',
unit='#trim(form.unit)#',
school='#trim(form.school)#',
program='#trim(form.program)#',
synopsis='#trim(form.synopsis)#',
website='#trim(form.website)#',
email='#trim(form.email)#',

<cfif len(trim(form.start_year)) is 0>start_year=null,
<cfelse>
start_year='#trim(form.start_year)#',
</cfif>

<cfif len(trim(form.end_year)) is 0>end_year=null,
<cfelse>
end_year='#trim(form.end_year)#',
</cfif>

<cfif len(trim(form.twostart_year)) is 0>twostart_year=null,
<cfelse>
twostart_year='#trim(form.twostart_year)#',
</cfif>

<cfif len(trim(form.twoend_year)) is 0>twoend_year=null,
<cfelse>
twoend_year='#trim(form.twoend_year)#',
</cfif>

status='#form.status#'
where entry_id=#form.entry_id#
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Untitled</title>
</head>

<body>
<cfoutput>
#first_name# #mi# #last_name# has been updated.<br><br>
</cfoutput>

<a href="edit1.cfm">Edit another entry?</a>
<a href="../mainmenu.cfm">Main Menu</a>
</body>
</html>

------------
This is the error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The error occurred in C:\Inetpub\ line 41

39 :
40 : status='#form.status#'
41 : where entry_id=#form.entry_id#
42 : </cfquery>
43 :

----------
I've been looking at the code for a couple of hours and cannot see the reason for the error. Is there something about using null's with date/time datatypes that's not obvious?

Thanks for the help.
 
Sort by date Sort by votes
what data type are your year fields?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
Its and Access database with all the date/time fields = date/time - Short Date - Not Required.

The information I'm trying to put into the database is mm/yyyy

Thanks again.
 
Upvote 0 Downvote
dB type date/time in Access doesn't allow you to set the value to NULL. If you don't want a date entered, leave it blank.

Like:
Code: 
<cfif len(trim(form.start_year)) is 0>
  [b]start_year=""[/b],
<cfelse>
  start_year='#trim(form.start_year)#',
</cfif>

BTW, you do realize that if you use Access dB type date/time, and don't enter a day, it defaults to 1? If you only enter 02/2005, access will enter it as 02/01/2005




____________________________________
Just Imagine.
 
Upvote 0 Downvote
i use null all the time.

if the xxx_year fields are date time try
start_year=###trim(form.start_year)###,

or try entering the full date. OR make it a number field and only enter the year like the field name suggests

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
this is not true, you can certainly set a date/time column to NULL, i just tested it
Click to expand...

I tested it also before I replied here. I opened an existing access dB that had a column data type date/time and entered in: NULL, 'NULL', <NULL>, and all other instances and got back an error stating The value you entered isn't valid for this field. Its only when I left the field blank, it was accepted.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
Forgot to mention, in SQL Server you can enter a null value by pressing CTRL+0, I tried the same idea in Access and got nothing.

r937, how did you enter null values in your test? And, did you look inside the table to see if the column had a NULL or just an empty field? Same for you TruthInSatire.

I didn't do my test via CF, I just openen up Access and performed the test.


____________________________________
Just Imagine.
 
Upvote 0 Downvote
Using the same code I showed in the original posting, except that I eliminated the <cfif> statements. If Access will accept empty fields but not nulls, then I don't need the <cfif> statements. I tried to submit empty fields (and fields with the mm/yyyy date into the database, and still get the datatype mismatch error.

I also tried TruthInSatire's code with the ###'s and got the same error. I tried entering a full date 02/02/2005 and still got the error.

Thanks

 
Upvote 0 Downvote
you can't enter null into the table directly (in access)because access sees null as a string. however if you use sql to enter null with no spaces it sees it as a null not a string.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
no spaces
Click to expand...
i meant to say " no quotes"

coder, can you paste the generated sql please?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.
 
Upvote 0 Downvote
I have given up on this...I have convinced my client to use SQL server instead of Access. Originally they didn't want to pay the couple extra bucks a month in hosting fees for SQL Server...sheesh!

I've gotten it to work ok in SQL Server.

I want to thank everyone who has taken the time to help with this, I greatly appreciate it.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FALCONSEYE
  • Locked
  • Question
ColdFusion microservices with REST
Replies
0
Views
580
FALCONSEYE
FALCONSEYE
LyndonOHRC
  • Locked
  • Question
Need help using ImageCrop()
Replies
1
Views
259
LyndonOHRC
LyndonOHRC
mdvp
  • Locked
  • Question
Intermittent X-ref problem with figures in FM 12
Replies
0
Views
559
mdvp
mdvp
MDEJAKE
  • Locked
  • Question
autosuggest (cfc and jquery) help
Replies
0
Views
301
MDEJAKE
MDEJAKE
oshinwa
  • Locked
  • Question
Which Call collector should I use and which one is compatible with my Ericsson BP250?
Replies
0
Views
661
oshinwa
oshinwa

Part and Inventory Search

Sponsor

Back
Top