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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
