Skip to main content
K
kelster2
Participant
October 15, 2006
Question

Problem with insert

  • October 15, 2006
  • 2 replies
  • 670 views


ODBC Error Code = 22005 (Error in assignment)


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

Here is my insert statement:
Insert into Obits
(FirstName,
MiddleName,
LastName,
DOB,
DeathDate,
BirthCity,
BirthState,
Visitation,
FuneralMass,
Cemetery,
<cfif isDefined("Picture")><cfif Picture NEQ "">picture,</cfif></cfif>
Condolences
)
values
('#FirstName#',
'#MiddleName#',
'#LastName#',
'#DateFormat(DOB, "MM/DD/YYYY")#',
'#DateFormat(DeathDate, "MM/DD/YYYY")#',
'#BirthCity#',
'#BirthState#',
'#DateFormat(Visitation, "MM/DD/YYYY")#',
'#DateFormat(FuneralMass, "MM/DD/YYYY")#',
'#Cemetery#',
<cfif isDefined("Picture")><cfif Picture NEQ "">'#Picture#',</cfif></cfif>
'#Condolences#')

Can anyone see what I am doing wrong? Any help is greatly appreciated.

Thanks,
Kelly
This topic has been closed for replies.

2 replies

N
nick_endgame
Inspiring
October 18, 2006
Hi Kelster2,

Just a shot in the dark, try outputting #Picture# in SQL expr:

<cfif isDefined("Picture") AND Picture NEQ "">Picture=<cfoutput>'#Picture#'</cfoutput>

Hope it works for u.
N
Newsgroup_User
Inspiring
October 15, 2006
Kelly,

It usually means a date field error:

http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_14487

http://support.microsoft.com/default.aspx?scid=kb;EN-US;246570

Try this:

Insert into Obits
(
FirstName,
MiddleName,
LastName,
DOB,
DeathDate,
BirthCity,
BirthState,
Visitation,
FuneralMass,
Cemetery,
picture,
Condolences
)
values
(
'#FirstName#',
'#MiddleName#',
'#LastName#',
'#DateFormat(DOB, "MM/DD/YYYY")#',
'#DateFormat(DeathDate, "MM/DD/YYYY")#',
'#BirthCity#',
'#BirthState#',
'#DateFormat(Visitation, "MM/DD/YYYY")#',
'#DateFormat(FuneralMass, "MM/DD/YYYY")#',
'#Cemetery#',
<cfif isDefined("Picture") AND Picture NEQ "">'#Picture#',<cfelse>"",</cfif>
'#Condolences#'
)

Or use a cfqueryparam for the date fields, like this:

(
'#FirstName#',
'#MiddleName#',
'#LastName#',
<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.DOB#">,
<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.DeathDate#">,
'#BirthCity#',
'#BirthState#',
<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.Visitation#">,
<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.FuneralMass#">,
'#Cemetery#',
<cfif isDefined("Picture") AND Picture NEQ "">'#Picture#',<cfelse>"",</cfif>
'#Condolences#'
)

--
Ken Ford
Adobe Community Expert
Fordwebs, LLC
http://www.fordwebs.com


"kelster2" <webforumsuser@macromedia.com> wrote in message
news:egs77e$ir8$1@forums.macromedia.com...
>
> ODBC Error Code = 22005 (Error in assignment)
>
>
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
> expression.
>
> Here is my insert statement:
> Insert into Obits
> (FirstName,
> MiddleName,
> LastName,
> DOB,
> DeathDate,
> BirthCity,
> BirthState,
> Visitation,
> FuneralMass,
> Cemetery,
> <cfif isDefined("Picture")><cfif Picture NEQ
> "">picture,</cfif></cfif>
> Condolences
> )
> values
> ('#FirstName#',
> '#MiddleName#',
> '#LastName#',
> '#DateFormat(DOB, "MM/DD/YYYY")#',
> '#DateFormat(DeathDate, "MM/DD/YYYY")#',
> '#BirthCity#',
> '#BirthState#',
> '#DateFormat(Visitation, "MM/DD/YYYY")#',
> '#DateFormat(FuneralMass, "MM/DD/YYYY")#',
> '#Cemetery#',
> <cfif isDefined("Picture")><cfif Picture NEQ
> "">'#Picture#',</cfif></cfif>
> '#Condolences#')
>
> Can anyone see what I am doing wrong? Any help is greatly appreciated.
>
> Thanks,
> Kelly
>


K
kelster2Author
Participant
October 15, 2006
Hi Ken,
Thanks for the quick response. I tried the first query you sent me and got the following error:

ODBC Error Code = 07001 (Wrong number of parameters)


[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

SQL = "Insert into Obits ( FirstName, MiddleName, LastName, DOB, DeathDate, BirthCity, BirthState, Visitation, FuneralMass, Cemetery, picture, PersonalInfo ) values ( 'nnnnnnnn', 'nnnnnnnnn', 'nnnnnnnnnnn', '', '', '', 'IL', '', '', '', "", '' )"

Then I tried the query using the cfqueryparam and I get the following error;

ODBC Error Code = 22008 (Datetime field overflow)


[Microsoft][ODBC Microsoft Access Driver]Datetime field overflow (null)


SQL = "Insert into Obits (FirstName, MiddleName, LastName, DOB, DeathDate, BirthCity, BirthState, Visitation, FuneralMass, Cemetery, PersonalInfo ) values ('nnnnnnnn', 'nnnnnnnnn', 'nnnnnnnnnnn', ?, ?, '', 'IL', ?, ?, '', '')"

Query Parameter Value(s) -

Parameter #1 = {d '1010-10-10'}

Parameter #2 = {d '1010-10-20'}

Parameter #3 = {d '1010-10-30'}

Parameter #4 = {d '1010-10-30'}

Any other suggestions? It's been awhile since I programmed so you will have to bare with me. Thanks again for your help I really appreciate it.

Kelly
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices