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,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 & ", " & 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></STRONG></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_AUTOFORMUPDATE.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>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
