asked on

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

SRIKANTH MADISHETTI

What is the CF error you are getting

earwig75

ASKER

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.

SRIKANTH MADISHETTI

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.

earwig75

ASKER

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.

SRIKANTH MADISHETTI

check this URL

http://www.neiland.net/blog/article/be-careful-with-coldfusion-date-validation/

earwig75

ASKER

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.

SRIKANTH MADISHETTI

The way ur looping is wrong

ASKER CERTIFIED SOLUTION

SRIKANTH MADISHETTI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

earwig75

ASKER

@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.

SRIKANTH MADISHETTI

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.

SRIKANTH MADISHETTI

for example your this code is totally wrong

<cfloop query="data" startRow="2">
 
 <cfset variables.Fielda = data.Fielda[currentRow] />
 <cfset variables.Fieldb = data.Fieldb[currentRow] />
 </cfloop>


 <cfif len(variables.Fielda) lt 1>

 ERROR: The spreadsheet appeared to have no data.
 Please go back and correct the entry.

 <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>
ERROR: The spreadsheet appeared to have no data.
Please go back and correct the entry.
</cfif>

SRIKANTH MADISHETTI

<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

earwig75

ASKER

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.

earwig75

ASKER

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.

earwig75

ASKER

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.