By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,326 Members | 2,873 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,326 IT Pros & Developers. It's quick & easy.

mysql syntax error

P: 4
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

Expand|Select|Wrap|Line Numbers
  1. 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') 
  2.  
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.

Expand|Select|Wrap|Line Numbers
  1. <cfquery name="popSp" datasource="perso">
  2.             insert into pers000.superspersonal(BADGE_NBR,
  3.             BASICLOC,
  4.             BELT_BADGE_NBR,
  5.             CELL_PHONE,
  6.             CITY,
  7.             CREATED_BY,
  8.             CREATED_DATE,
  9.             CREDENTIAL_NUMBER,
  10.             DATE_1811,
  11.             DATE_AP_INITIAL_CERT,
  12.             DATE_AP_REMOVAL,
  13.             DATE_AP_RE_CERT,
  14.             DATE_BASIC,
  15.             DATE_FIREARMS_QUAL,
  16.             EMAIL,
  17.             EMP_ID,
  18.             EOIG_FAILED_LOGINS,
  19.             EOIG_LOGIN_DATE,
  20.             EOIG_OFFICE,EOIG_OFFICE2,
  21.             EOIG_OFFICE3,
  22.             EOIG_REGION,EXECCORRTRK_YN,
  23.             FIREARMS_INST,
  24.             GOWNO,
  25.             GOWTYPE,
  26.             HOME_PHONE,
  27.             HOTLINE_FAILED_LOGINS,
  28.             HOTLINE_LOGIN_DATE,
  29.             HOTLINE_YN,
  30.             IMIS_FAILED_LOGINS,
  31.             IMIS_LOGIN_DATE,
  32.             IMIS_YN,
  33.             INVENTORY_FAILED_LOGINS,
  34.             INVENTORY_LOGIN_DATE,
  35.             INVENTORY_YN,
  36.             ISIS_FAILED_LOGINS,
  37.             ISIS_LOGIN_DATE,
  38.             ISIS_YN,
  39.             LEGACY_EMP_ID,
  40.             LEGACY_OITS_EMP_ID,
  41.             MGR_ID,
  42.             ORACLEID,
  43.             PAGER_NBR,
  44.             PERSONAL_COST_CENTER,
  45.             PERSONAL_DATE_GRADE,
  46.             PERSONAL_DATE_SEPARATION,
  47.             PERSONAL_DOB,
  48.             PERSONAL_EOD,
  49.             PERSONAL_GRADE,
  50.             PERSONAL_NAMEF,
  51.             PERSONAL_NAMEL,
  52.             PERSONAL_SALARY,
  53.             PERSONAL_SSN,
  54.             POWNO,
  55.             POWTYPE,
  56.             SAC_ASAC,
  57.             SPOUSE_NAME,
  58.             STATE,
  59.             STREET,
  60.             UPDATED_BY,
  61.             UPDATED_DATE,
  62.             UPDATE_FROM_PERSONAL,
  63.             VALID_FLAG,ZIP)
  64.             VALUES('#BADGE_NBR#',
  65.             '#BASICLOC#',
  66.             '#BELT_BADGE_NBR#',
  67.             '#CELL_PHONE#',
  68.             '#CITY#',
  69.             '#CREATED_BY#',
  70.             <cfif CREATED_DATE is ''>
  71.                 #dateFormat(now(),'yyyy-mm-dd')#
  72.             <cfelse>'#CREATED_DATE#'</cfif>,
  73.             '#CREDENTIAL_NUMBER#',
  74.             <cfif DATE_1811 is ''>
  75.             #dateFormat(now(),'yyyy-mm-dd')#
  76.             <cfelse>
  77.             '#DATE_1811#'</cfif>,
  78.             <cfif DATE_AP_INITIAL_CERT is ''>
  79.             #dateFormat(now(),'yyyy-mm-dd')#
  80.             <cfelse>
  81.             '#DATE_AP_INITIAL_CERT#'</cfif>,
  82.             <cfif  DATE_AP_REMOVAL is ''>
  83.                 #dateFormat(now(),'yyyy-mm-dd')#
  84.             <cfelse>
  85.             '#DATE_AP_REMOVAL#</cfif>',
  86.             <cfif DATE_AP_RE_CERT is ''>
  87.                 #dateFormat(now(),'yyyy-mm-dd')#
  88.             <cfelse>
  89.             '#DATE_AP_RE_CERT#'</cfif>,
  90.             <cfif DATE_BASIC is ''>
  91.                 #dateFormat(now(),'yyyy-mm-dd')#
  92.             <cfelse>
  93.             '#DATE_BASIC#'</cfif>,
  94.             <cfif DATE_FIREARMS_QUAL is ''>
  95.                 #dateFormat(now(),'yyyy-mm-dd')#
  96.             <cfelse>
  97.             '#DATE_FIREARMS_QUAL#'</cfif>,
  98.             '#EMAIL#',
  99.             #EMP_ID#,
  100.  
  101.             #EOIG_FAILED_LOGINS#,
  102.             <cfif EOIG_LOGIN_DATE is ''>
  103.                 #dateFormat(now(),'yyyy-mm-dd')#
  104.             <cfelse>
  105.             '#EOIG_LOGIN_DATE#'</cfif>,
  106.             '#EOIG_OFFICE#',
  107.             '#EOIG_OFFICE2#',
  108.             '#EOIG_OFFICE3#',
  109.             '#EOIG_REGION#',
  110.             '#EXECCORRTRK_YN#',
  111.             '#FIREARMS_INST#',
  112.             '#GOWNO#',
  113.             '#GOWTYPE#',
  114.             '#HOME_PHONE#',
  115.             #HOTLINE_FAILED_LOGINS#,
  116.             <cfif HOTLINE_LOGIN_DATE is ''>
  117.                 #dateFormat(now(),'yyyy-mm-dd')#
  118.             <cfelse>
  119.             '#HOTLINE_LOGIN_DATE#'</cfif>,
  120.             '#HOTLINE_YN#',
  121.             #IMIS_FAILED_LOGINS#,
  122.             <cfif IMIS_LOGIN_DATE is ''>
  123.                 #dateFormat(now(),'yyyy-mm-dd')#
  124.             <cfelse>
  125.             #IMIS_LOGIN_DATE#</cfif>,
  126.             '#IMIS_YN#',
  127.             #INVENTORY_FAILED_LOGINS#,
  128.             <cfif INVENTORY_LOGIN_DATE is ''>
  129.                 #dateFormat(now(),'yyyy-mm-dd')#
  130.             <cfelse>#INVENTORY_LOGIN_DATE#</cfif>,    
  131.             '#INVENTORY_YN#',
  132.             #ISIS_FAILED_LOGINS#,
  133.             <cfif ISIS_LOGIN_DATE is ''>
  134.                 #dateFormat(now(),'yyyy-mm-dd')#
  135.             <cfelse>#ISIS_LOGIN_DATE#
  136.             </cfif>,
  137.             '#ISIS_YN#',
  138.             #LEGACY_EMP_ID#,    
  139.             '#LEGACY_OITS_EMP_ID#',
  140.             #MGR_ID#,
  141.             '#ORACLEID#',
  142.             '#PAGER_NBR#',
  143.             '#PERSONAL_COST_CENTER#',
  144.             '#PERSONAL_DATE_GRADE#',
  145.             <cfif PERSONAL_DATE_SEPARATION is ''>
  146.                 #dateFormat(now(),'yyyy-mm-dd')#
  147.             <cfelse>'#PERSONAL_DATE_SEPARATION#'</cfif>,
  148.             <cfif PERSONAL_DOB is ''>
  149.                 #dateFormat(now(),'yyyy-mm-dd')#
  150.             <cfelse>'#PERSONAL_DOB#'</cfif>,
  151.             <cfif PERSONAL_EOD is ''>
  152.                 #dateFormat(now(),'yyyy-mm-dd')#
  153.             <cfelse>'#PERSONAL_EOD#'</cfif>,
  154.             '#PERSONAL_GRADE#',
  155.             '#PERSONAL_NAMEF#',
  156.             '#PERSONAL_NAMEL#',
  157.             #PERSONAL_SALARY#,
  158.             '#PERSONAL_SSN#',
  159.             '#POWNO#',
  160.             '#POWTYPE#',
  161.             '#SAC_ASAC#',
  162.             '#SPOUSE_NAME#',
  163.             '#STATE#',
  164.             '#STREET#',
  165.             '#UPDATED_BY#',
  166.             <cfif UPDATED_DATE is ''>
  167.                 #dateFormat(now(),'yyyy-mm-dd')#
  168.             <cfelse>'#UPDATED_DATE#'</cfif>,
  169.             '#UPDATE_FROM_PERSONAL#',
  170.             '#VALID_FLAG#','#ZIP#')
  171.         </cfquery>
  172.  
  173.  
thanks in advance
jbird4k
Nov 28 '11 #1
Share this Question
Share on Google+
8 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Some of your date fields don't have quotes and some have only 1.
Nov 28 '11 #2

P: 4
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.
Nov 29 '11 #3

Rabbit
Expert Mod 10K+
P: 12,359
No, you need the quotes. You weren't supposed to remove them, I was pointing out that you needed them.
Nov 29 '11 #4

P: 4
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.
Nov 29 '11 #5

Rabbit
Expert Mod 10K+
P: 12,359
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.
Nov 29 '11 #6

P: 4
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.
Nov 29 '11 #7

Rabbit
Expert Mod 10K+
P: 12,359
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.
Nov 29 '11 #8

100+
P: 122
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:
Expand|Select|Wrap|Line Numbers
  1. insert into tablename(coldate, colname, coltel, colstreet) values(2011-12-03, ', , 'Sesame Street);
this is correct
Expand|Select|Wrap|Line Numbers
  1. insert into tablename(coldate, colname, coltel, colstreet) values('2011-12-03', '', null, 'Sesame Street');
Dec 3 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.