The ODBC driver needs to have the dates passed as ODBC Formatted
dates. Use CreateODBCDate function for the Admission Date and Patient
Birth fields
The reason it works in the SQL Builder may be that it's using an OLEDb
connection which allows the dates to be formatted like mm/dd/yyyy
Marlon
At 10:08 AM 11/9/2001 -0500, you wrote:
>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
