I made this to auto insert form fields into a table without me having to
write insert statements all the time.
For testing, it outputs the sql statement and then I do the insert. I get
an error when I try to insert.
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax
near '12'.
But I can copy and past the SQL output into SQL CF builder and it works
fine. I also have used ms interdev to check my sql and all is fine.
Any thoughts.</FONT>
SQL statement after processing:
INSERT INTO tbl_preregistration (AdmissionDate,hospital,Physician,PatientNa
me,PatientBirth,PatientAge,MotherMaiden,PatientBirthPlace,MaritalStatus,Rel
igion,Church,Race,PatientStreetAddress,PatientAptRtBox,PatientCity,PatientS
tate,PatientZip,PatientCounty,PatientAreacode,PATIENTPHONE,Student,PatientE
mployerSchool,EmployerAddress,EmployerCity,EmployerState,EmployerZip,Patien
tOccupation,TimeEmployed,EmployerAreacode,EmployerPhone,veteran,SocialSecur
ity,PreviousAdmission,Insured,EmergencyContact,EmergencyContactRelationship
,EmergencyAddress,EmergencyCity,EmergencyState,EmergencyZip,EmergencyAreaCo
de,EmergencyPhone,OrganDonor,LivingWill,PowerAttorney,PreRegType,SubmitDate
) Values ('12/01/2001','PHBE','Test, David, Douglas','Test, David,
Douglas','04/05/67','34','Hammonds','Greenvile, SC','Married','Test','Test
Church','Test Race','309 Gandy Court','309','West Columbia','SC','29169','L
exington','803','951-3789','Not a student','Test Employer','Test Info','Tes
t Info','sc','29169','Web Developer','2 years','803','434-4703','no','251-9
4-2056','No','YES','Test info','Test info','Test info','Test info','SC','29
169','803','434-4703','Yes','Yes','Yes','Birth','11/09/2001')
Code:
<!--- Int Fields with default --->
<cfparam name="FieldNameValue" default="">
<cfparam name="ColumnNames" default="">
<cfparam name="FieldNameValues" default="">
<!--- Get Column Names, Data Types from Form Field TableName and DSN(should
be an hidden form fields --->
<cfquery name="GetTableInfo"datasource="#Form.DSN#">
sp_columns #Form.TableName#
</cfquery>
<!--- Loop through each column in table --->
<cfloop query="GetTableInfo">
<cfif ListFindNoCase(Form.FIELDNAMES,Column_Name) NEQ 0 AND Trim(Evaluate
(ListGetAt(Form.FieldNames,ListFindNoCase(Form.FIELDNAMES,Column_Name))))
NEQ "">
<cfset ElePos = ListFindNoCase(Form.FIELDNAMES,Column_Name)>
<!--- Check to see what data type each column is and build insert based
on value --->
<CFSWITCH EXPRESSION=#GetTableInfo.Type_Name#>
<cfcase value="varchar">
<cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(ListG
etAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="datetime">
<cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(ListG
etAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="text">
<cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(ListG
etAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="timestamp">
<cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(ListG
etAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="smalldatetime">
<cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(ListG
etAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<CFDEFAULTCASE>
<cfset FieldNameValues = FieldNameValues & "," & Trim(Evaluate(ListGe
tAt(Form.FieldNames,ElePos)))>
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</CFDEFAULTCASE>
</CFSWITCH>
</cfif>
</cfloop>
<!--- Check to see if there is a ,(comma) at the END the string --->
<CFIF Left(FieldNameValues,1) EQ ","><cfset FieldNameValues = Right(Field
NameValues,Len(FieldNameValues)-1)></CFIF>
<CFIF Left(ColumnNames,1) EQ ","><cfset ColumnNames = Right(ColumnNames,L
en(ColumnNames)-1)></CFIF>
<!--- Sample Insert statement --->
<cfoutput>
INSERT INTO #Form.Tablename#
(#ColumnNames#)
Values
(#FieldNameValues#)
<cfquery name="Insert" datasource="#Form.DSN#">
INSERT INTO #Form.Tablename#
(#ColumnNames#)
Values
(#FieldNameValues#)
</cfquery>
</cfoutput>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
