mysql syntax error

[jbird4k]

hi

I am having an issue inserting data into a mysql table.

I am getting the following error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '', 2011-11-28 , 2011-11-28 , 2011-11-28 , 'foster.sharon@ oig.dol' at line 75

here is the code that is trying to execute

insert into pers000.superspersonal(BADGE_NBR, BASICLOC, BELT_BADGE_NBR, CELL_PHONE, CITY, CREATED_BY, CREATED_DATE, CREDENTIAL_NUMBER, DATE_1811, DATE_AP_INITIAL_CERT, DATE_AP_REMOVAL, DATE_AP_RE_CERT, DATE_BASIC, DATE_FIREARMS_QUAL, EMAIL, EMP_ID, EOIG_FAILED_LOGINS, EOIG_LOGIN_DATE, EOIG_OFFICE,EOIG_OFFICE2, EOIG_OFFICE3, EOIG_REGION,EXECCORRTRK_YN, FIREARMS_INST, GOWNO, GOWTYPE, HOME_PHONE, HOTLINE_FAILED_LOGINS, HOTLINE_LOGIN_DATE, HOTLINE_YN, IMIS_FAILED_LOGINS, IMIS_LOGIN_DATE, IMIS_YN, INVENTORY_FAILED_LOGINS, INVENTORY_LOGIN_DATE, INVENTORY_YN, ISIS_FAILED_LOGINS, ISIS_LOGIN_DATE, ISIS_YN, LEGACY_EMP_ID, LEGACY_OITS_EMP_ID, MGR_ID, ORACLEID, PAGER_NBR, PERSONAL_COST_CENTER, PERSONAL_DATE_GRADE, PERSONAL_DATE_SEPARATION, PERSONAL_DOB, PERSONAL_EOD, PERSONAL_GRADE, PERSONAL_NAMEF, PERSONAL_NAMEL, PERSONAL_SALARY, PERSONAL_SSN, POWNO, POWTYPE, SAC_ASAC, SPOUSE_NAME, STATE, STREET, UPDATED_BY, UPDATED_DATE, UPDATE_FROM_PERSONAL, VALID_FLAG,ZIP) VALUES('', 'GA', '', '478-442-1354', 'Atlanta', 'PERSONAL', '2009-10-13 00:00:00.0', '', '2006-11-13 00:00:00.0', '2009-09-28 00:00:00.0', 2011-11-28 ', 2011-11-28 , 2011-11-28 , 2011-11-28 , 'foster.sharon@oig.dol.gov', 49870, 0, '2009-11-30 00:00:00.0', '40', '', '', '40', 'No', '', '', '', '404-681-3526', , 2011-11-28 , '', 0, 2009-11-30 00:00:00.0, 'Y', , 2011-11-28 , 'Y', , 2011-11-28 , '', 9000023, '', 8966, 'SAF4341', '', '341', '', 2011-11-28 , '1971-09-07 00:00:00.0', '2009-09-27 00:00:00.0', '12', 'SHARON', 'FOSTER', 72745, '111114433', '', '', 'A', 'Rick Bell', 'GA', '27 Howell Street NE', 'PERSONAL', '01-DEC-09', 'Y', 'Y','30312')

the data is coming from a query on an access table with the same structure.

I have tried, removing the dates and redceived the same error.
have a conditional check if the date field is blank insert sysdate ,

I have tried single quotes around the date variable and no quotes.

Here is the coldfusion code. Not all field are valid for each record.

<cfquery name="popSp" datasource="perso">
			insert into pers000.superspersonal(BADGE_NBR,
			BASICLOC,
			BELT_BADGE_NBR,
			CELL_PHONE,
			CITY,
			CREATED_BY,
			CREATED_DATE,
			CREDENTIAL_NUMBER,
			DATE_1811,
			DATE_AP_INITIAL_CERT,
			DATE_AP_REMOVAL,
			DATE_AP_RE_CERT,
			DATE_BASIC,
			DATE_FIREARMS_QUAL,
			EMAIL,
			EMP_ID,
			EOIG_FAILED_LOGINS,
			EOIG_LOGIN_DATE,
			EOIG_OFFICE,EOIG_OFFICE2,
			EOIG_OFFICE3,
			EOIG_REGION,EXECCORRTRK_YN,
			FIREARMS_INST,
			GOWNO,
			GOWTYPE,
			HOME_PHONE,
			HOTLINE_FAILED_LOGINS,
			HOTLINE_LOGIN_DATE,
			HOTLINE_YN,
			IMIS_FAILED_LOGINS,
			IMIS_LOGIN_DATE,
			IMIS_YN,
			INVENTORY_FAILED_LOGINS,
			INVENTORY_LOGIN_DATE,
			INVENTORY_YN,
			ISIS_FAILED_LOGINS,
			ISIS_LOGIN_DATE,
			ISIS_YN,
			LEGACY_EMP_ID,
			LEGACY_OITS_EMP_ID,
			MGR_ID,
			ORACLEID,
			PAGER_NBR,
			PERSONAL_COST_CENTER,
			PERSONAL_DATE_GRADE,
			PERSONAL_DATE_SEPARATION,
			PERSONAL_DOB,
			PERSONAL_EOD,
			PERSONAL_GRADE,
			PERSONAL_NAMEF,
			PERSONAL_NAMEL,
			PERSONAL_SALARY,
			PERSONAL_SSN,
			POWNO,
			POWTYPE,
			SAC_ASAC,
			SPOUSE_NAME,
			STATE,
			STREET,
			UPDATED_BY,
			UPDATED_DATE,
			UPDATE_FROM_PERSONAL,
			VALID_FLAG,ZIP)
			VALUES('#BADGE_NBR#',
			'#BASICLOC#',
			'#BELT_BADGE_NBR#',
			'#CELL_PHONE#',
			'#CITY#',
			'#CREATED_BY#',
			<cfif CREATED_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>'#CREATED_DATE#'</cfif>,
			'#CREDENTIAL_NUMBER#',
			<cfif DATE_1811 is ''>
			#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_1811#'</cfif>,
			<cfif DATE_AP_INITIAL_CERT is ''>
			#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_AP_INITIAL_CERT#'</cfif>,
			<cfif  DATE_AP_REMOVAL is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_AP_REMOVAL#</cfif>',
			<cfif DATE_AP_RE_CERT is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_AP_RE_CERT#'</cfif>,
			<cfif DATE_BASIC is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_BASIC#'</cfif>,
			<cfif DATE_FIREARMS_QUAL is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#DATE_FIREARMS_QUAL#'</cfif>,
			'#EMAIL#',
			#EMP_ID#,
			
			#EOIG_FAILED_LOGINS#,
			<cfif EOIG_LOGIN_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#EOIG_LOGIN_DATE#'</cfif>,
			'#EOIG_OFFICE#',
			'#EOIG_OFFICE2#',
			'#EOIG_OFFICE3#',
			'#EOIG_REGION#',
			'#EXECCORRTRK_YN#',
			'#FIREARMS_INST#',
			'#GOWNO#',
			'#GOWTYPE#',
			'#HOME_PHONE#',
			#HOTLINE_FAILED_LOGINS#,
			<cfif HOTLINE_LOGIN_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			'#HOTLINE_LOGIN_DATE#'</cfif>,
			'#HOTLINE_YN#',
			#IMIS_FAILED_LOGINS#,
			<cfif IMIS_LOGIN_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>
			#IMIS_LOGIN_DATE#</cfif>,
			'#IMIS_YN#',
			#INVENTORY_FAILED_LOGINS#,
			<cfif INVENTORY_LOGIN_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>#INVENTORY_LOGIN_DATE#</cfif>,	
			'#INVENTORY_YN#',
			#ISIS_FAILED_LOGINS#,
			<cfif ISIS_LOGIN_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>#ISIS_LOGIN_DATE#
			</cfif>,
			'#ISIS_YN#',
			#LEGACY_EMP_ID#,	
			'#LEGACY_OITS_EMP_ID#',
			#MGR_ID#,
			'#ORACLEID#',
			'#PAGER_NBR#',
			'#PERSONAL_COST_CENTER#',
			'#PERSONAL_DATE_GRADE#',
			<cfif PERSONAL_DATE_SEPARATION is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>'#PERSONAL_DATE_SEPARATION#'</cfif>,
			<cfif PERSONAL_DOB is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>'#PERSONAL_DOB#'</cfif>,
			<cfif PERSONAL_EOD is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>'#PERSONAL_EOD#'</cfif>,
			'#PERSONAL_GRADE#',
			'#PERSONAL_NAMEF#',
			'#PERSONAL_NAMEL#',
			#PERSONAL_SALARY#,
			'#PERSONAL_SSN#',
			'#POWNO#',
			'#POWTYPE#',
			'#SAC_ASAC#',
			'#SPOUSE_NAME#',
			'#STATE#',
			'#STREET#',
			'#UPDATED_BY#',
			<cfif UPDATED_DATE is ''>
				#dateFormat(now(),'yyyy-mm-dd')#
			<cfelse>'#UPDATED_DATE#'</cfif>,
			'#UPDATE_FROM_PERSONAL#',
			'#VALID_FLAG#','#ZIP#')
		</cfquery>

thanks in advance
jbird4k

[Rabbit]

Some of your date fields don't have quotes and some have only 1.

[jbird4k]

OK, rabbit, I have removed all quotes.

What is the proper syntax for inserting a date? with or without quotes?

and ran it again and received the same error message.

[Rabbit]

No, you need the quotes. You weren't supposed to remove them, I was pointing out that you needed them.

[jbird4k]

I added the quotes and still received the same error. I can manually enter the exact same data and not recieve an error.

is there any way to determine which field is throwing the error?
....near line 67. is actually the column name not the value, the ..115. is the last line of the query.

confused and frustrated.

[Rabbit]

I don't know cold fusion but it would help to see your updated SQL. Also, please separate each field on its own line, it was hard to read the original where everything was on one line.

[jbird4k]

i have had some success in dynamically adding a single record, but I had to provide a value for every field.

How would I insert a null or blank value? Not all fields apply to all users.

[Rabbit]

If you want null you can put null. If you want a blank string you can put two single quotes. But if you're creating a dynamic string, just leave the fields out altogether.

[adriancs]

many of the values does not apply quotes ' ' and some only have 1 quote.

it should be something like this:

sample data:

coldate = 03 December 2011
colname = **blank data
coltel = **not entered
colstreet = Sesame Street

this is wrong:

insert into tablename(coldate, colname, coltel, colstreet) values(2011-12-03, ', , 'Sesame Street);

this is correct

insert into tablename(coldate, colname, coltel, colstreet) values('2011-12-03', '', null, 'Sesame Street');