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 <CSV2DATA></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]
