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
8 2115 Rabbit 12,516
Recognized Expert Moderator MVP
Some of your date fields don't have quotes and some have only 1.
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 12,516
Recognized Expert Moderator MVP
No, you need the quotes. You weren't supposed to remove them, I was pointing out that you needed them.
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 12,516
Recognized Expert Moderator MVP
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.
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 12,516
Recognized Expert Moderator MVP
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.
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');
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Julien - Marseille |
last post by:
Hello,
I need help for php syntax when i call Mysql database
I have wrote that and my sql connection is working
I just have a problem with this command line :
$query = "SELECT * FROM...
|
by: red |
last post by:
I don't see why mysql is telling me the following is a syntax error:
create table articles
{ file char(30) not null
};
What is wrong with that ?
|
by: Maciej Paras |
last post by:
Hello! I've written 2 pages: one i HTML format, and second - in ASP. When
I'm posting data from HTML page, I receive this error generated by ASP page:
Microsoft JET Database Engine (0x80040E14)...
|
by: charles |
last post by:
The follow SQL expressions don't work in MySQL:
UPDATE calendar SET Date = '2004-01-29' WHERE Date < '2004-01-29' AND
Done = 0
UPDATE calendar SET Date = '2004-01-29' WHERE ((Date <...
|
by: sunny076 |
last post by:
Hi,
I am confused with the syntax for NOT in MYSQL where clause and wonder
if an expert in MYSQL can enlighten me. There are possibly two places
NOT can go in:
select * from employee_data...
|
by: Alicia |
last post by:
Does anyone know why I am getting a "Syntax error in Create Table
statement". I am using Microsoft Access SQL View to enter it. Any
other problems I may run into?
CREATE TABLE weeks
(...
|
by: Joe |
last post by:
Hello All,
I am trying to insert a record in the MS Access DB and for some reason I cannot get rid of error message,
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
...
|
by: Phil989 |
last post by:
Hi,
I hope you can help me. As I try to install Devil'z Clanportal, i only recieve this error message:
MySQL-Query failed:
ErrorNo = 1064
Error = You have an error in your SQL syntax; check...
|
by: goodguyjam |
last post by:
Hi all,
I'm having trouble with mysql.
I've just finished my php coding for HTTP authentication and with some help am now getting a login window pop up whenever I click on a link on my website...
|
by: guillermobytes |
last post by:
Hi,
i'm making a query with PDO and there is a SQL syntax error in it.
$sql = 'BAD CODE';
$pdoStmt = $pdo->prepare($sql);
if (false === $pdoStmt) {
echo 'ERROR';
}
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |