do this: <cfquery name="Update" datasource="#Form.DSN#"> Update #preserveSingleQuotes(Form.Tablename)# SET #preserveSingleQuotes(FieldNameValues)# WHERE ID = #preserveSingleQuotes(Form.ID)# </cfquery>
+-----------------------------------------------+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: David Brown [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:26 AM To: CF-Talk Subject: strange SQL error I have a action page that accepts any form page and its variables then based on the stored proc "sp_columns" builds sql statment for update or insert. When I try to use it I get an Datefield overflow error (SQL 2000). But if I output the sql statment and then copy and past it into my sql enterprise manager the statement will work fine. If I remove the form date field I then get an syntax error, but I can copy and past it into SQL enterprise manager and it works. Here is the page that builds the sql statment: <!--- Int Fields with default ---> <cfset FieldNameValue=""> <cfset ColumnNames=""> <cfset FieldNameValues=""> <cfparam name="Form.ID" default="0"> <!--- 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,Colum n_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 & ", " & Column_Name & "=" & "'" & Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) & "'"> </cfcase> <cfcase value="datetime"> <cfset FieldNameValues = FieldNameValues & ", " & Column_Name & "=" & CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))))> </cfcase> <cfcase value="text"> <cfset FieldNameValues = FieldNameValues & ", " & Column_Name & "=" & "'" & Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) & "'"> </cfcase> <cfcase value="timestamp"> <cfset FieldNameValues = FieldNameValues & ", " & Column_Name & "=" & "'" & Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) & "'"> </cfcase> <cfcase value="smalldatetime"> <cfset FieldNameValues = FieldNameValues & ", " & Column_Name & "=" & "'" & Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) & "'"> </cfcase> <CFDEFAULTCASE> <cfset FieldNameValues = FieldNameValues & ", " & Column_Name & "=" & Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos)))> </CFDEFAULTCASE> </CFSWITCH> </cfif> </cfloop> <!--- Check to see if there is a ,(comma) at the START of the string ---> <CFIF Left(FieldNameValues,1) EQ ","><cfset FieldNameValues = Right(FieldNameValues,Len(FieldNameValues)-1)></CFIF> <CFIF Left(ColumnNames,1) EQ ","><cfset ColumnNames = Right(ColumnNames,Len(ColumnNames)-1)></CFIF> <!--- Sample Insert statement ---> <cfquery name="Update" datasource="#Form.DSN#"> Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# </cfquery> Here is the output of the sql statment it builds and then error i get. Update tbl_PreOp SET id=5, PreRegType='Hosp', Allergies='Tape,Iodine', LatexDiagnosis='Yes', EpipenAnaphylaxis='Yes', RubberReaction='Yes', TypeReation='test1', AvacadoBannaETC='Yes', TypeReactionToAvacadoBananaETC='test2', MedCondition='test3', UnexplainedAllergyType='test4' WHERE ID = 5 ---------------------------------------------------------------------------- ---- Queries GetTableInfo (Records=57, Time=63ms) SQL = sp_columns tbl_PreOp </PLAINTEXT><PRE></PRE></Q></S></SAMP></SCRIPT></SELECT></SMALL></STRIKE></S TRONG></SUB></SUP></TABLE></TD></TEXTAREA></TH></TITLE></TR></TT></U> <UL></UL></VAR></WBR><XMP></XMP> <HR> <H3>Error Occurred While Processing Request</H3> <P> <TABLE border=1> <TBODY> <TR> <TD> <H4>Error Diagnostic Information</H4> <P>ODBC Error Code = 37000 (Syntax error or access violation) <P> <P>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. <P> <P>SQL = "Update tbl_PreOp SET id=5, PreRegType=''Hosp'', Allergies=''Tape,Iodine'', LatexDiagnosis=''Yes'', EpipenAnaphylaxis=''Yes'', RubberReaction=''Yes'', TypeReation=''test1'', AvacadoBannaETC=''Yes'', TypeReactionToAvacadoBananaETC=''test2'', MedCondition=''test3'', UnexplainedAllergyType=''test4'' WHERE ID = 5" <P>Data Source = "INTERNET_PHA" <P> <P>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (55:1) to (55:47) in the template file I:\INETPUB\WWWROOT\PALMETTOHEALTH\PREREGISTRATION\..\INCLUDE\CFM\INC_AUTOFOR MUPDATE.CFM.</P> <P> <P>Date/Time: 06/06/02 09:55:33<BR>Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)<BR>Remote Address: 204.116.185.37<BR>HTTP Referrer: http://intertest/preregistration/preop2.cfm?PreRegType=Hosp&Drugs=No& ;ID=5</P> ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists