Thank you very much. I should I have known better.
-----Original Message-----
From: [EMAIL PROTECTED]
Sent: Friday, November 09, 2001 10:20 AM
To: CF-Talk <[EMAIL PROTECTED]>
Subject: Re: Question why I get Incorrect syntax near in my SQL when it
works in CF Query builder
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,PatientN
a
>me,PatientBirth,PatientAge,MotherMaiden,PatientBirthPlace,MaritalStatus,Re
l
>igion,Church,Race,PatientStreetAddress,PatientAptRtBox,PatientCity,Patient
S
>tate,PatientZip,PatientCounty,PatientAreacode,PATIENTPHONE,Student,Patient
E
>mployerSchool,EmployerAddress,EmployerCity,EmployerState,EmployerZip,Patie
n
>tOccupation,TimeEmployed,EmployerAreacode,EmployerPhone,veteran,SocialSecu
r
>ity,PreviousAdmission,Insured,EmergencyContact,EmergencyContactRelationshi
p
>,EmergencyAddress,EmergencyCity,EmergencyState,EmergencyZip,EmergencyAreaC
o
>de,EmergencyPhone,OrganDonor,LivingWill,PowerAttorney,PreRegType,SubmitDat
e
>) 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','2
9
>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(Evalua
te
>(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(Lis
tG
>etAt(Form.FieldNames,ElePos))) &"'">
> <cfset ColumnNames = ColumnNames & "," & Column_Name>
> </cfcase>
> <cfcase value="datetime">
> <cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(Lis
tG
>etAt(Form.FieldNames,ElePos))) &"'">
> <cfset ColumnNames = ColumnNames & "," & Column_Name>
> </cfcase>
> <cfcase value="text">
> <cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(Lis
tG
>etAt(Form.FieldNames,ElePos))) &"'">
> <cfset ColumnNames = ColumnNames & "," & Column_Name>
> </cfcase>
> <cfcase value="timestamp">
> <cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(Lis
tG
>etAt(Form.FieldNames,ElePos))) &"'">
> <cfset ColumnNames = ColumnNames & "," & Column_Name>
> </cfcase>
> <cfcase value="smalldatetime">
> <cfset FieldNameValues = FieldNameValues & ",'" & Trim(Evaluate(Lis
tG
>etAt(Form.FieldNames,ElePos))) &"'">
> <cfset ColumnNames = ColumnNames & "," & Column_Name>
> </cfcase>
> <CFDEFAULTCASE>
> <cfset FieldNameValues = FieldNameValues & "," & Trim(Evaluate(List
Ge
>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(Fiel
d
>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>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
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
