Yes, it runs there fine. I even tried <cfoutput> to see what the sql is
when it is used in cfquery and copied it from the browser and pasted it back
into another cfquery tag with the static sql statment. it works fine.
This is what is happening. I have a form page with the form fields names
the same as what is in my table. I then use an include page that builds the
sql statement for me.
here is the insert page.
<!--- 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>
<!--- Sample Insert statement --->
<cfoutput>
INSERT INTO #Form.Tablename#
(#Trim(ColumnNames)#)
VALUES (#Trim(FieldNameValues)#)
</cfoutput>
<cfquery name="Insert" datasource="#Form.DSN#">
INSERT INTO #Form.Tablename#
(#Trim(ColumnNames)#)
VALUES (#Trim(FieldNameValues)#)
</cfquery>
As you can see it should work, but it does not.
----- Original Message -----
From: "Haggerty, Michael A." <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, January 17, 2002 11:34 AM
Subject: RE: Error inserting into SQL 7.0 server
> Have you tried running the query in Query Analyzer? Does it run there?
>
> Mike
>
> -----Original Message-----
> From: David Brown [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 17, 2002 10:42 AM
> To: CF-Talk
> Subject: Re: Error inserting into SQL 7.0 server
>
>
> I am using SA on the cf dsn.
> ----- Original Message -----
> From: "Mark A. Kruger - CFG" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Thursday, January 17, 2002 12:29 PM
> Subject: RE: Error inserting into SQL 7.0 server
>
>
> > Are you sure that CF's ODBC setup has the exact same permissions as
> you
> used
> > in registering your server in SQL enterprise manager?
> >
> > Mark
> >
> > -----Original Message-----
> > From: David Brown [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, January 17, 2002 7:05 AM
> > To: CF-Talk
> > Subject: Error inserting into SQL 7.0 server
> >
> >
> > Below is the text output of what i am trying to insert into a table
> in a
> sql
> > 7.0 server.
> >
> > I have pasted the text into sql window of sql enterprise manager and
> it
> > checked ok. But still gives me an odbc error.
> >
> > Any suggestions?
> >
> > INSERT INTO tbl_upload_content
> > (UploadFilename,HyperLinkTitle,Description)
> > Values
> > ('CF_Print_zip','This is a test','Test of hyperlink and doc')
> >
> >
> >
> > <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 4: Incorrect
> syntax
> > near
> > 'CF_Print_zip'.
> > <P>
> > <P>
> > <P>The error occurred while processing an element with a general
> identifier
> > of
> > (CFQUERY), occupying document position (63:1) to (63:47).</P>
> > <P>
> > <P>Date/Time: 01/17/02 10:01:44<BR>Browser: Mozilla/4.0 (compatible;
> MSIE
> > 6.0;
> > Windows NT 5.1; Q312461)<BR>Remote Address: 127.0.0.1<BR>HTTP
> Referer:
> > http://localhost/autoupload/upload.cfm</P>
> >
> > _________________________
> > _________________________
> > ____________________
> > Why Share?
> > Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20
> GB
> > MO/XFER
> > Instant Activation · $99/Month · Free Setup
> > http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
> > 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
> >
>
>
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
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
