CFspreadsheet issue

Hello list, Long time (years) no post. I am trying to read from a spreadsheet that is loaded up onto the server and then insert the information into the database. I have three column headers called Code, Barcode and ResortID. Code and Barcode have values and ResortID is empty. The reason that I did this is because I want to insert the ResortID from a formfield called ResortID. The problem is that I am getting the error message: Columc count doesn't match value count at row 1. Here is my code: <!--- Read the spreadsheet data into a query object ---> <cfspreadsheet action="read" query="DealsDSN" headerrow="1" src="#ExpandPath("Spreadsheets/#ClientFile#")#" /> <!--- Loop thorugh the query starting with the first row containing data (row 2) ---> <cfloop query="MahDSN" startrow="2"> <!--- Insert the code and bar code into the database for future use ---> <cfquery datasource="DealsDSN" result="foobar">   INSERT INTO BarCodes   (Code, BarCode, ResortID)   VALUES   (   <cfqueryparam cfsqltype="cf_sql_varchar" value="#Code#">   <cfqueryparam cfsqltype="cf_sql_varchar" value="#BarCode#">   <cfqueryparam cfsqltype="cf_sql_int" value="#Form.ResortID">   ) </cfquery> </cfloop> The error is on <cfqueryparam cfsqltype="cf_sql_int" value="#Form.ResortID"> Any assistance is greatly appreciated. Bruce

I'm seeing a missing # sign after Form.Request Hello list, Long time (years) no post. I am trying to read from a spreadsheet that is loaded up onto the server and then insert the information into the database. I have three column headers called Code, Barcode and ResortID. Code and Barcode have values and ResortID is empty. The reason that I did this is because I want to insert the ResortID from a formfield called ResortID. The problem is that I am getting the error message: Columc count doesn't match value count at row 1. Here is my code: <!--- Read the spreadsheet data into a query object ---> <cfspreadsheet action="read" query="DealsDSN" headerrow="1" src="#ExpandPath("Spreadsheets/#ClientFile#")#" /> <!--- Loop thorugh the query starting with the first row containing data (row 2) ---> <cfloop query="MahDSN" startrow="2"> <!--- Insert the code and bar code into the database for future use ---> <cfquery datasource="DealsDSN" result="foobar">   INSERT INTO BarCodes   (Code, BarCode, ResortID)   VALUES   (   <cfqueryparam cfsqltype="cf_sql_varchar" value="#Code#">   <cfqueryparam cfsqltype="cf_sql_varchar" value="#BarCode#">   <cfqueryparam cfsqltype="cf_sql_int" value="#Form.ResortID">   ) </cfquery> </cfloop> The error is on <cfqueryparam cfsqltype="cf_sql_int" value="#Form.ResortID"> Any assistance is greatly appreciated. Bruce

Yeah I picked up on that and still the same issue. On Aug 10, 2012, at 1:00 PM, "Sandra Clark" <sclarklists@gmail.com> wrote: > > I'm seeing a missing # sign after Form.Request >

Did you pick up the missing commas between the cfparams? They need commas, just like the field list. That would give a mismatched column count error. -Cameron > Yeah I picked up on that and still the same issue.

Thanks Cameron. I have been out of the game so long I am making noob mistakes, LOL. On Aug 10, 2012, at 1:33 PM, Camer > > Did you pick up the missing commas between the cfparams? They need commas, > just like the field list. > > That would give a mismatched column count error. > > -Cameron

You do know your query names don't match up.. Your reading the spreadsheet into a query object called DealsDSN and looping over an object called "MahDSN" ----- Excess quoted text cut - see Original Post for more -----

Hello, I have a question about CFspreadsheet.... I'm reading a spreadsheet and inserting the data into a SQL server   DB....everything works fine unless 1 of my columns in the spreadsheet is blank. I'm getting the following error: Element PHONE is undefined in EXCELQUERYBYNAME. I tried the put a cfif isdefined to see if that would solve it but no luck....same error message. How can I handle this issue? My cfquery is below: <cfquery name="insert_Sh_data" datasource="#datasource#">     INSERT into contactrecord        (        contact_date,        fname,        lname,        address,        city,        state,        zip,        county,        <cfif isDefined(excelQueryByName.phone)>phone,</cfif>        <cfif isDefined(excelQueryByName.cell)>cell,</cfif>        <cfif isDefined(excelQueryByName.fax)>fax,</cfif>        <cfif isDefined(excelQueryByName.title)>title,</cfif>        <cfif isDefined(excelQueryByName.organization)>organization,</cfif>        <cfif isDefined(excelQueryByName.email)>email,</cfif>        <cfif isDefined(excelQueryByName.general_comments)>general_comments,</cfif>        <cfif isDefined(excelQueryByName.fname2)>fname2,</cfif>        <cfif isDefined(excelQueryByName.lname2)>lname2,</cfif>         mailing,         input_by,         input_date,         input_time,         input_by_id,         country,         sh_rating         )                                      Values(                       '#Trim(excelQueryByName.contact_date)#',         '#Trim(excelQueryByName.fname)#',         '#Trim(excelQueryByName.lname)#',         '#Trim(excelQueryByName.address)#',         'Trim(excelQueryByName.city)#',         '#Trim(excelQueryByName.state)#',         '#Trim(excelQueryByName.zip)#',         '#Trim(excelQueryByName.county)#',         <cfif isDefined(excelQueryByName.phone)>'#Trim(excelQueryByName.phone)#',</cfif>         <cfif isDefined(excelQueryByName.cell)>'#Trim(excelQueryByName.cell)#',</cfif>          <cfif isDefined(excelQueryByName.fax)>'#Trim(excelQueryByName.fax)#',</cfif>                     <cfif isDefined(excelQueryByName.title)>'#Trim(excelQueryByName.title)#',</cfif>          <cfif isDefined(excelQueryByName.organization)>'#Trim(excelQueryByName.organization)#',</cfif>          <cfif isDefined(excelQueryByName.email)>'#Trim(excelQueryByName.email)#',</cfif>          <cfif isDefined(excelQueryByName.general_comments)>'#Trim(excelQueryByName.general_comments)#',</cfif>          <cfif isDefined(excelQueryByName.fname2)>'#Trim(excelQueryByName.fname2)#',</cfif>          <cfif isDefined(excelQueryByName.lname2)>'#Trim(excelQueryByName.lname2)#',</cfif>          '#Trim(excelQueryByName.mailing)#',          '#Trim(excelQueryByName.input_by)#',          '#Trim(excelQueryByName.input_date)#',          '#Trim(excelQueryByName.input_time)#',          '#Trim(excelQueryByName.input_by_id)#',          '#Trim(excelQueryByName.country)#',          '#Trim(excelQueryByName.sh_rating)#'          ) </cfquery> Thanks in Advance.