help with sql insert

Question

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#) 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 4644 : #DateFormat(PCSDate, "mmmm d, yyyy")#, 45 : #form.CChildren#, 46 : #form.COwner#) 47 : 48 : sqlState 42000datasource pcsvendorErrorCode 1064sql 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

BKBK · Answer

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'>.

		<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#)

Error Executing Database Query.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded