Yes, the SQL is in variables.
That is why I had it output the SQL statement at the top. To make sure I
was sending the write string.
Here is the CFquery line.
This code has worked in the past.
<!--- 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,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 & ",'" &
Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="datetime">
<cfset FieldNameValues = FieldNameValues & "," &
CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))))>
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="text">
<cfset FieldNameValues = FieldNameValues & ",'" &
Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) &"'">
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="timestamp">
<cfset FieldNameValues = FieldNameValues & "," &
CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))))>
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<cfcase value="smalldatetime">
<cfset FieldNameValues = FieldNameValues & "," &
CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))))>
<cfset ColumnNames = ColumnNames & "," & Column_Name>
</cfcase>
<CFDEFAULTCASE>
<cfset FieldNameValues = FieldNameValues & "," &
Trim(Evaluate(ListGetAt(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(FieldNameValues,Len(FieldNameValues)-1)></CFIF>
<CFIF Left(ColumnNames,1) EQ ","><cfset ColumnNames =
Right(ColumnNames,Len(ColumnNames)-1)></CFIF>
<!--- Insert statement --->
<cfquery name="Insert" datasource="#Form.DSN#">
INSERT INTO #Form.Tablename#
(#ColumnNames#)
Values
(#FieldNameValues#)
</cfquery>
----- Original Message -----
From: "Pascal Peters" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, November 14, 2001 11:30 AM
Subject: RE: ODBC error
> Im I correct in thinking this SQL Is in a variable?
>
> <cfquery ...>
> #PreserveSingleQuotes(myvar)#
> </cfquery>
>
> Pascal
>
> -----Original Message-----
> From: David Brown [mailto:[EMAIL PROTECTED]]
> Sent: woensdag 14 november 2001 17:17
> To: CF-Talk
> Subject: ODBC error
>
>
> The below is an output of what is in the query.
>
> INSERT INTO tbl_user_profiles
> (username,password,FirstName,LastName,Email,Phone,CreateDate) Values
> ('dbrown','karen1','David','Brown','[EMAIL PROTECTED]','803
> -
> 434-4703',{ts '2001-11-14 10:55:47'})
>
>
> Below is the error I get. Any idea?
>
>
> ------------------------------------------------------------------------
> -
> -------
>
> Queries
>
> GetTableInfo (Records=10, Time=16ms)
> SQL =
> sp_columns tbl_user_profiles
>
>
>
> </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 = 22005 (Error in assignment)
> <P>
> <P>[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
> <P>
> <P>SQL = "INSERT INTO tbl_user_profiles
> (username,password,FirstName,LastName,Email,Phone,CreateDate) Values
> (''dbrown'',''karen1'',''David'',''Brown'',''david.brown@palmettohealth.
> o
> rg'',''803-434-4703'',{ts
> ''2001-11-14 10:55:47''})"
> <P>Data Source = "Intranet_PHA2"
> <P>SQL = "INSERT INTO tbl_user_profiles
> (username,password,FirstName,LastName,Email,Phone,CreateDate) Values
> (''dbrown'',''karen1'',''David'',''Brown'',''david.brown@palmettohealth.
> o
> rg'',''803-434-4703'',{ts
> ''2001-11-14 10:55:47''})"
> <P>
> <P>The error occurred while processing an element with a general
> identifier of
> (CFQUERY), occupying document position (62:1) to (62:47).
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
