> > <cfquery ...> > > #PreserveSingleQuotes(myvar)# > > </cfquery> > >
that fixed it thanks. ----- Original Message ----- From: "David Brown" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, November 14, 2001 11:46 AM Subject: Re: ODBC error > 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). > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
