Highlighted

help with sql insert

New Here ,
Jul 02, 2015

Copy link to clipboard

Copied

<cfset pcsDate= #CreateDate(form.Cyear, form.Cmonth, form.Cday)#>
<cfquery datasource="pcs" username="manpcs" password="Charle54!">
INSERT INTO client
(
First_Name,
Last_Name,
Phone,
Email,
Rank,
CSFirst_Name,
CSLast_Name,
CSPhone,
CSEmail,
CSRank,
Address,
City,
State,
Postal,
CDuty_Station,
FDuty_Station,
PCSDate,
Children,
Owner)
VALUES
(#form.CFirst_name#,
#form.CLast_name#,
#form.CMobil#,
#form.CEmail#,
#form.CRank#,
#form.CSFirst_Name#,
#form.CSLast_Name#,
#form.CSMobil#,
#form.CSEmail#,
#form.CSRank#,
#form.CAddress#,
#form.CCity#,
#form.CState#,
#form.CPostal#,
#form.CDuty_Station#,
#form.CFuture_Duty_Station#,
#DateFormat(PCSDate, "mmmm d, yyyy")#,
#form.CChildren#,
#form.COwner#)

</cfquery>

Error Executing Database Query.

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 '3, 2017, 3, 2)' at line 39
The error occurred in D:/home/manpcs.com/wwwroot/Clients/insert_action.cfm: line 46
44 :      #DateFormat(PCSDate, "mmmm d, yyyy")#, 45 :      #form.CChildren#, 46 : #form.COwner#) 47 :  </cfquery> 48 : 

sqlState  42000
datasource  pcs
vendorErrorCode  1064
sql   INSERT INTO client ( First_Name, Last_Name, Phone, Email, Rank, CSFirst_Name, CSLast_Name, CSPhone, CSEmail, CSRank, Address, City, State, Postal, CDuty_Station, FDuty_Station, PCSDate, Children, Owner) VALUES (a, b, c, d, E-1, eF, F, G, H, E-1, I, J, K, L, M, N, March 3, 2017, 3, 2)

Any help is appreciated

TOPICS
Getting started

Views

258

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

help with sql insert

New Here ,
Jul 02, 2015

Copy link to clipboard

Copied

<cfset pcsDate= #CreateDate(form.Cyear, form.Cmonth, form.Cday)#>
<cfquery datasource="pcs" username="manpcs" password="Charle54!">
INSERT INTO client
(
First_Name,
Last_Name,
Phone,
Email,
Rank,
CSFirst_Name,
CSLast_Name,
CSPhone,
CSEmail,
CSRank,
Address,
City,
State,
Postal,
CDuty_Station,
FDuty_Station,
PCSDate,
Children,
Owner)
VALUES
(#form.CFirst_name#,
#form.CLast_name#,
#form.CMobil#,
#form.CEmail#,
#form.CRank#,
#form.CSFirst_Name#,
#form.CSLast_Name#,
#form.CSMobil#,
#form.CSEmail#,
#form.CSRank#,
#form.CAddress#,
#form.CCity#,
#form.CState#,
#form.CPostal#,
#form.CDuty_Station#,
#form.CFuture_Duty_Station#,
#DateFormat(PCSDate, "mmmm d, yyyy")#,
#form.CChildren#,
#form.COwner#)

</cfquery>

Error Executing Database Query.

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 '3, 2017, 3, 2)' at line 39
The error occurred in D:/home/manpcs.com/wwwroot/Clients/insert_action.cfm: line 46
44 :      #DateFormat(PCSDate, "mmmm d, yyyy")#, 45 :      #form.CChildren#, 46 : #form.COwner#) 47 :  </cfquery> 48 : 

sqlState  42000
datasource  pcs
vendorErrorCode  1064
sql   INSERT INTO client ( First_Name, Last_Name, Phone, Email, Rank, CSFirst_Name, CSLast_Name, CSPhone, CSEmail, CSRank, Address, City, State, Postal, CDuty_Station, FDuty_Station, PCSDate, Children, Owner) VALUES (a, b, c, d, E-1, eF, F, G, H, E-1, I, J, K, L, M, N, March 3, 2017, 3, 2)

Any help is appreciated

TOPICS
Getting started

Views

259

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 02, 2015 0
BKBK LATEST
Adobe Community Professional ,
Jul 05, 2015

Copy link to clipboard

Copied

Two things:

1) DateFormat(PCSDate, "mmmm d, yyyy") is in fact a string. The error arises because you attempted to insert it into a date column. It introduces an extra comma, which breaks your query. Format the database date column, not the input value.

2) Use cfqueryparam for inserting form variables into the database. In fact, that will enable you to just insert the date as

<cfqueryparam value = "#pcsDate#"  cfsqltype = 'cf_sql_date'>.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 05, 2015 1