ok im gonna show you my code, im trying to upload a comma delimited file to 
a database , seems to work in most situtiions, except when im working with 
this one file that has 28 fields, the insert statement shows a syntax 
error, if i shrink the same csv file to 5 fields, no errors happen, im 
trying to figure out if the errors from my csv file or my cold fusion code,

heres the whole file:( i think youd just wanna look at the part were the 
cfinsert happens

<!--- Declaring optional parameter --->
<cfparam name="attributes.append" default="no" type="boolean">
<cfparam name="attributes.columnhead" default="no" type="boolean">
<cfparam name="attributes.datatype" default="" type="string">

<cftry>
         <cfif not parameterexists(attributes.datasource)>
                 <cfthrow message="Custom tag CSV2DATA must contains 
<b>datasource</b> parameter">
         <cfelseif not parameterexists(attributes.file)>
                 <cfthrow message="Custom tag CSV2DATA must contains 
<b>file</b> parameter">
         <cfelseif not parameterexists(attributes.file)>
                 <cfthrow message="Custom tag CSV2DATA must contains 
<b>table</b> parameter">
         <cfelseif not attributes.columnhead>
                 <cfif not parameterexists(attributes.column)>
                         <cfthrow message="Custom tag CSV2DATA must 
contains <b>column</b> parameter">
                 </cfif>
         </cfif>
         <cfif not FileExists(attributes.file)>
                 <cfthrow message="please provide a valid path for the CSV 
file.">
         <cfelse>
                 <!--- Reading File and storing in variable read_file --->
                 <cffile action="READ" file="#attributes.file#" 
variable="read_file">
         </cfif>
<cfoutput>
<!--- Delete all records from the table if append is off --->
         <cfif not attributes.append>
                 <cfquery datasource="#attributes.datasource#">
                         delete from #attributes.table#
                 </cfquery>
         </cfif>
         <cftransaction>
<!--- Loop for each line of the file --->
         <cfloop index="line" list="#read_file#" delimiters="#chr(13)#">
         <!--- Works only if line contains some character --->
                 <cfif trim(line) is not "">
                         <!--- Initilizing variables --->
                         <cfset full_word = "">
                         <!---
                                 Putting a pair of double quotes at the 
first and last position if there is no data
                                 also in between if field is empty
                          --->
                         <cfset line=replace(line,",,",","""",","ALL")>
                         <cfset line=ReReplace(line,"^,",""""",","ALL")>
                         <cfset line=ReReplace(line,",$",",""""","ALL")>

                         <cfset quote_search =false>
                         <cfset value_array = ArrayNew(1)>
                         <!--- Loop for each field data separated by comma --->
                         <cfloop index="word" list="#line#" delimiters=",">
                                 <!---
                                         Checking for first character 
whether it is double quote or not,
                                         If it is double quote then check 
how many double quotes are in this word
                                         if odd then add it to next 
elements else add it to array
                                 --->
                                 <cfif left(word,1) is """" or 
quote_search>
                                         <cfset full_word = full_word & word>
                                         <!--- Initilizing quote_count --->
                                         <cfset quote_count = 0>
                                         <cfset search_word = """">
                                         <!--- Script for calculating 
number of double quote in full_word variable--->
                                         <cfscript>
                                                 for(i=1;i LTE 
len(full_word); i = i + 1){
                                                         if(mid(full_word, 
i, len(search_word)) EQ search_word){
                                                                 quote_count 
= quote_count + 1;
                                                         } 

                                                 }
                                         </cfscript>
                                         <cfif (quote_count MOD 2) EQ 
0>
                                                 <cfset temp = 
ArrayAppend(value_array,replace(mid(full_word,2,len(full_word) - 
2),"""""","""","ALL"))>
                                                 <cfset quote_search = false>
                                                 <cfset full_word = "">
                                         <cfelse>
                                                 <cfset quote_search = true>
                                                 <cfset full_word = 
full_word &   ",">
                                         </cfif>

                                 <cfelse>
                                         <cfset temp = 
ArrayAppend(value_array, word)>
                                 </cfif>
                         </cfloop>
                         <!--- If columnhead is defined in CSV file then 
initilize column --->
                         <cfif attributes.columnhead>
                                 <cfset attributes.column = 
ArraytoList(value_array)>
                                 <cfset attributes.columnhead = "no">
                         <cfelse>
                         <!--- Statement to insert in the database --->
                                 <cfquery 
datasource="#attributes.datasource#">
                                         insert into #attributes.table# 
(#attributes.column#) values(
                                         <cfloop index="ele" from="1" 
to="#ListLen(attributes.column)#">
                                                 <cfif 
trim(value_array[ele]) is "">
                                                         <cfqueryparam 
null="Yes">
                                                 <cfelseif 
attributes.datatype is not "">
                                                         <cfqueryparam 
value="#trim(value_array[ele])#" 
cfsqltype="#ListGetAt(attributes.datatype,ele)#">
                                                 <cfelse>
                                                         <cfqueryparam 
value="#trim(value_array[ele])#" cfsqltype="CF_SQL_VARCHAR">
                                                 </cfif>
                                                 <cfif ele NEQ 
ListLen(attributes.column)>,</cfif>
                                         </cfloop>
                                         )
                                 </cfquery>
                         </cfif>
                 </cfif>
         </cfloop>
         </cftransaction>
</cfoutput>
<cfcatch type="Any">
                 <table width="700" border="0" cellspacing="0" 
cellpadding="0" align="center">
                                 <tr>
                                         <td>
                                                 <table border="0" 
width="100%" cellspacing="0" cellpadding="0">
                                                         <tr><td 
align="center" class="title">Error in the Custom Tag &lt;CSV2DATA&gt;</td>
                                                         </tr>
                                         </table>
                                 </td>
                         </tr>
                 </table>
                 <table width="700" border="0" cellspacing="0" 
cellpadding="0" align="center">
                                 <tr>
                                         <td>
                                                 <table border="0" 
width="100%" cellspacing="1" cellpadding="0">
                                                         <tr> 

                 <cfoutput>
                 <td class="td"><u>#cfcatch.message#</u></td></tr>
                 <td class="td">#cfcatch.detail#</td></tr>
                 </cfoutput>

                                         </table>
                                 </td>
                         </tr>
                 </table>
<cfabort>
</cfcatch>
</cftry>



At 06:33 AM 3/26/2002 -0600, you wrote:
>Richard,
>
>Check your insert statement for proper use of quotes.  Whenever you are
>inserting/updating, or selecting on a string (versus numeric) type of
>field, your field values must be specified inside of quotes.  Even
>though it looks like you are using parameters, you probably need to have
>something like:  values("#parameter1#",....
>
>Ryan
>
>-----Original Message-----
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
>Behalf Of Richard Morrison
>Sent: Tuesday, March 26, 2002 5:12 AM
>To: [EMAIL PROTECTED]
>Subject: [KCFusion] Can someone tell me what the hells wrong with this
>sql statement
>
>
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
>statement.
>SQL = "insert into test (Area, GeoMarket, Ctry, Location,
>LocationDescription, Family, Subfamily, Tag, Group, AssetDescription,
>Co,
>Rig, Quantity, Approval, Manufacturer, Model, Serial, PO, ServDateMMYY,
>FA_TC01, FA_TC02, FA_TC03, FA_TC04, FA_TC05, FA_TC06, FA_TC07, FA_TC08,
>FA_TC09) values( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ,
>?
>, ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )"
>Query Parameter Value(s) -
>Parameter #1 = LAM
>Parameter #2 = VTT
>Parameter #3 = VEN
>Parameter #4 = 58258
>Parameter #5 = VEN EAST OFFICE
>Parameter #6 = 1
>Parameter #7 = 1
>Parameter #8 = 2
>Parameter #9 = WL
>Parameter #10 = VEHICLE TOYOTA 4X4 PU PC LONG
>Parameter #11 = XE
>Parameter #12 = 258
>Parameter #13 = 1
>Parameter #14 = TRN IN GEC
>Parameter #15 = 1
>Parameter #16 = 3
>Parameter #17 = 3
>Parameter #18 = 3
>Parameter #19 = 297
>Parameter #20 = 3
>Parameter #21 = 3
>Parameter #22 = 3
>Parameter #23 = 3
>Parameter #24 = 3
>Parameter #25 = 3
>Parameter #26 = 3
>Parameter #27 = 3
>Parameter #28 = 3
>Data Source = "1"
>
>
>
>
>______________________________________________________________________
>The KCFusion.org list and website is hosted by Humankind Systems, Inc.
>List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
>Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
>To Subscribe.................... mailto:[EMAIL PROTECTED]
>To Unsubscribe................ mailto:[EMAIL PROTECTED]
>
>
>
>
>
>
>______________________________________________________________________
>The KCFusion.org list and website is hosted by Humankind Systems, Inc.
>List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
>Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
>To Subscribe.................... mailto:[EMAIL PROTECTED]
>To Unsubscribe................ mailto:[EMAIL PROTECTED]
>


 
 
______________________________________________________________________
The KCFusion.org list and website is hosted by Humankind Systems, Inc.
List Archives........ http://www.mail-archive.com/cf-list@kcfusion.org
Questions, Comments or Glowing Praise.. mailto:[EMAIL PROTECTED]
To Subscribe.................... mailto:[EMAIL PROTECTED]
To Unsubscribe................ mailto:[EMAIL PROTECTED]
 

Reply via email to