[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

Validation on file contents with loop

I am using the method below for uploading an excel, reading the contents, performing validation and then writing the data to a table. I had to change my method a bit so this is different than my previous post.

I am looping though the data to perform validation (you will see the comments in my code) but the validation is not working in this example. It is being skipped somehow and only a coldfusion error shows instead of my error.

Could someone help with an example of how I can validate the data before it gets inserted?

<cfset showForm = true>
<cfset PhysicalPath = GetDirectoryFromPath(GetCurrentTemplatePath())>

<cfif isdefined("form.submit_upload")>
<CFFILE action="UPLOAD" accept="application/vnd.ms-excel" filefield="xlsfile" destination="#PhysicalPath#" nameconflict="MAKEUNIQUE">

<cfset theFile = serverDirectory & "/" & serverFile />

<p>The File: <cfdump var="#thefile#" /></p>


<cfif isSpreadsheetFile(theFile)>
	<cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
	
	<!--- temp dumping for debugging --->
	<cfdump var="#data#" />
	<!--- end temp dumping for debugging --->
	
	<cffile action="delete" file="#theFile#">
	<cfset showForm = false />
	
	<cfloop query="data" startRow="2">
		<!--- extract values from each column --->
		<cfset variables.Fielda = data.Fielda[currentRow] />
		<cfset variables.Fieldb = data.Fieldb[currentRow] />
	</cfloop>
	
	
	<cfif len(variables.Fielda) lt 1>
		
			<p>ERROR: The spreadsheet appeared to have no data.</p>
			<p>Please go back and correct the entry.</p>
		
	<cfelse>
	
	      <cfloop query="data" startRow="2">
	      	<!--- extract values from each column --->
	      	<cfset variables.Fielda = data.Fielda[currentRow] />
	      	<cfset variables.Fieldb = data.Fieldb[currentRow] />
	      	
	      	
	      	<!--- BEGIN VALIDATION --->
	      		 <CFIF IsDate(variables.Fielda) IS "No">
	      		<p>ERROR: One or more of your dates is not correct.</p>
	      		
	      	<cfif fileExists(theFile)>
	      		<cffile action="delete" file="#theFile#">
	      	</cfif>
	      	
	      	<cfabort />
	      	</CFIF>
	      </cfloop>
			<!--- END VALIDATION --->
			
			<!--- insert values into db table ---->
			
		<cfloop query="data" startRow="2">
			<!--- extract values from each column --->
			<cfset variables.Fielda = data.Fielda[currentRow] />
			<cfset variables.Fieldb = data.Fieldb[currentRow] />
		
			<!--- insert values into db table ---->
	<cfquery name="addRow" datasource="#datasource#" dbtype="ODBC">
		INSERT INTO MyTable ( Fielda, Fieldb ) 
		VALUES ( 
	<cfqueryparam value="#variables.Fielda#" cfsqltype="cf_sql_varchar">, 
	<cfqueryparam value="#variables.Fieldb#" cfsqltype="cf_sql_varchar"> ) 
	</cfquery>
		</cfloop>
		
		<cfif fileExists(theFile)>
			<cffile action="delete" file="#theFile#">
		</cfif>
		
	<cfquery name="getData" datasource="#datasource#" dbtype="ODBC">
			
			SELECT Fielda, Fieldb
			FROM MyTable
		</cfquery>
		<p>Upload Completed Successfully</p>
	</cfif>
</cfif>
</cfif>

<cfif showForm>
	<!--- Form for upload --->
	<form action="Upload.cfm" method="POST" name="upload" enctype="multipart/form-data">
		<input type="file" name="xlsfile">
		<input type="submit" name="submit_upload" value="Upload">
	</form>
</cfif>

Open in new window

0
earwig75
Asked:
earwig75
  • 8
  • 7
1 Solution
 
srikanthmadishettiCommented:
What is the CF error you are getting
0
 
earwig75Author Commented:
Everything works if I upload a file without any errors. If i intentionally put in a bad date I want my error to display... instead the error is something like:

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string.  
 
The error occurred in C:/WEB/Upload.cfm: line 123

The error happens on the INSERT... which I do not want to even run if something is found to be bad in my first loop. It is almost like the first loop is not running.

Thanks again.
0
 
srikanthmadishettiCommented:
Please share line no 123  of ur upload.cfm
 IsDate() would return true if date is passed as 12/08 which it would consider as december of 2008,, but this would definitely fail in an SQL insert if date field is defined, so you just need to be careful.
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
earwig75Author Commented:
The line that it is failing on is another parameter completely that's why I didn't include it. The code im using here is a sample. The error happens on the insert but it shouldn't even get that far. I am uploading a date in the spreadsheet as 5/25/2012a to cause it to break and test my validation.
0
 
earwig75Author Commented:
Thanks but that is not the problem. I have other validations that are not working as well. One that checks to make sure a field is a certain length doesn't work either. I used isdate with the same date in the past and it failed properly.
0
 
srikanthmadishettiCommented:
The way ur looping is wrong
0
 
srikanthmadishettiCommented:
your action code should be some thing like this

<cfif isdefined("form.submit_upload")><!--- 1 if  --->
  <CFFILE action="UPLOAD" accept="application/vnd.ms-excel" filefield="xlsfile" destination="#PhysicalPath#" nameconflict="MAKEUNIQUE">
  <cfset theFile = serverDirectory & "/" & serverFile />
  <p>The File:
    <cfdump var="#thefile#" />
  </p>
  <cfif isSpreadsheetFile(theFile)> <!--- 2 if  --->
    <cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
    <!--- temp dumping for debugging --->
    <cfdump var="#data#" />
    <!--- end temp dumping for debugging --->
    <cffile action="delete" file="#theFile#">
    <cfset showForm = false />
    <cfif data.recordcount lt 2>
      <p>ERROR: The spreadsheet appeared to have no data.</p>
      <p>Please go back and correct the entry.</p>
    </cfif>
    <cfloop query="data" startRow="2">
      <!--- extract values from each column --->
      <cfset variables.Fielda = data.Fielda[currentRow] />
      <cfset variables.Fieldb = data.Fieldb[currentRow] />
      <!--- BEGIN VALIDATION --->
      <CFIF not IsDate(variables.Fielda)>
        <p><cfoutput>ERROR: Row #currentrow# date is not correct.</cfoutput></p>
        <cfif fileExists(theFile)>
          <cffile action="delete" file="#theFile#">
        </cfif>
        <cfabort />
      </CFIF>
      <!--- END VALIDATION --->
      <!--- insert values into db table ---->
      <cfquery name="addRow" datasource="#datasource#" dbtype="ODBC">
      INSERT INTO MyTable ( Fielda, Fieldb ) 
      VALUES (
      <cfqueryparam value="#variables.Fielda#" cfsqltype="cf_sql_varchar">
      ,
      <cfqueryparam value="#variables.Fieldb#" cfsqltype="cf_sql_varchar">
      )
      </cfquery>
    </cfloop>
    <cfif fileExists(theFile)>
      <cffile action="delete" file="#theFile#">
    </cfif>
    <cfquery name="getData" datasource="#datasource#" dbtype="ODBC">
    SELECT Fielda, Fieldb
    FROM MyTable
    </cfquery>
    <p>Upload Completed Successfully</p>
  </cfif> <!--- 1 if close  --->
</cfif> <!--- 2 if close --->

Open in new window


This is not tested but should work
0
 
earwig75Author Commented:
@srikanthmadishetti

That will not work because the validation is happening during a loop. With that method entries would go into the database before it breaks. I need the validation to happen before the insert, not within the same loop.
0
 
srikanthmadishettiCommented:
What is your req ,

If any of the date is not correct you don't want to insert any data ?

Validation should happen during the loop that is correct way of checking , you check is this row has correct data then go a head and insert else just skip that row and display a message

the way u are doing is running mutliple loops which is not good at all.
0
 
srikanthmadishettiCommented:
for example your this code is totally wrong

<cfloop query="data" startRow="2">
            <!--- extract values from each column --->
            <cfset variables.Fielda = data.Fielda[currentRow] />
            <cfset variables.Fieldb = data.Fieldb[currentRow] />
      </cfloop>
      
      
      <cfif len(variables.Fielda) lt 1>
            
                  <p>ERROR: The spreadsheet appeared to have no data.</p>
                  <p>Please go back and correct the entry.</p>
            
      <cfelse>

Now ur variables.Fielda will have just last row value . there is no meaning in running that loop instead you can just validate this by  simply checking record count

<cfif data.recordcount lt 2>
      <p>ERROR: The spreadsheet appeared to have no data.</p>
      <p>Please go back and correct the entry.</p>
    </cfif>
0
 
srikanthmadishettiCommented:
<cfif isdefined("form.submit_upload")><!--- 1 if  --->
  <CFFILE action="UPLOAD" accept="application/vnd.ms-excel" filefield="xlsfile" destination="#PhysicalPath#" nameconflict="MAKEUNIQUE">
  <cfset theFile = serverDirectory & "/" & serverFile />
  <p>The File:
    <cfdump var="#thefile#" />
  </p>
  <cfif isSpreadsheetFile(theFile)> <!--- 2 if  --->
    <cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
    <!--- temp dumping for debugging --->
    <cfdump var="#data#" />
    <!--- end temp dumping for debugging --->
    <cffile action="delete" file="#theFile#">
    <cfset showForm = false />
	
    <cfif data.recordcount lt 2>
      <p>ERROR: The spreadsheet appeared to have no data.</p>
      <p>Please go back and correct the entry.</p>
    </cfif>
	
    <cfloop query="data" startRow="2">
      <!--- BEGIN VALIDATION --->
      <CFIF not IsDate(data.Fielda)>
        <p><cfoutput>ERROR: One or more of your dates is not correct</cfoutput></p>
        <cfif fileExists(theFile)>
          <cffile action="delete" file="#theFile#">
        </cfif>
        <cfabort />
      </CFIF>
	</cfloop>
	
	<cfloop query="data" startRow="2">
      <!--- END VALIDATION --->
      <!--- insert values into db table ---->
      <cfquery name="addRow" datasource="#datasource#" dbtype="ODBC">
      INSERT INTO MyTable ( Fielda, Fieldb ) 
      VALUES (
      <cfqueryparam value="#data.Fielda#" cfsqltype="cf_sql_varchar">
      ,
      <cfqueryparam value="#data.Fieldb#" cfsqltype="cf_sql_varchar">
      )
      </cfquery>
    </cfloop>
	
    <cfif fileExists(theFile)>
      <cffile action="delete" file="#theFile#">
    </cfif>
	
    <cfquery name="getData" datasource="#datasource#" dbtype="ODBC">
    SELECT Fielda, Fieldb
    FROM MyTable
    </cfquery>
    <p>Upload Completed Successfully</p>
  </cfif> <!--- 1 if close  --->
</cfif> <!--- 2 if close --->

Open in new window



this one will validate for all entries first and aborts with message if any wrong date
0
 
earwig75Author Commented:
I tried the first method, with validation in the loop and the same thing happens:

Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string.
0
 
earwig75Author Commented:
I put a javascript alert in the validation cfif and it never ran... leading me to believe that is being skipped over for some reason.
0
 
earwig75Author Commented:
I was able to work with this - I looped through once for the validation and then looped through again before performing the insert. Thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now