Val Help, Please

[skythrock]

So I have this code that reads in a CSV file which contains a student's grades for an exam.  Usually, the grades are numeric (obviously), but on occasion, there will be outstanding circumstances that will result in the grade having an asterisk appended to it (95*, for example). So in the program that reads in the grades, I need to convert the datatype of the grade variable to a numeric before entering the info in my database.

All well and good, but for some reason the code I have isn't working.  Currently the code in question looks like this (cfabort used for testing):

<cfset grade = Val('grade')>
#grade#
<cfabort>

Unfortunately, the output that is being generated is the grade with the asterisk still attached as opposed to the numeric value only. Am I incorrectly using cfset?  What is the issue?  Thanks in advance for your help!

[Reed_Powell-ttnmOb]

I'm having a hard time believing that val('grade') is not returning zero.  What you want is

     <cfset grade=val(grade)>

The quotes are telling the val() function to turn the literal string 'grade' into a numeric, which should end up being zero.

Another approace would be:

     <cfset grade="replace(grade,"*","","ALL")>

-reed

[skythrock]

Tried both suggestions, Reed, but neither of them worked. The output still has the asterisk appended.

[-__cfSearching__-]

I am with Reed on this one. I see no reason it should not work. Unless something else is going on elsewhere in the code, that we cannot see.

Can you post a small example (with values) that reproduces the issue?

[skythrock]

It must be the CSV import, then. It should be returning 0 if nothing else, right?  The relevant code is just this:

<cfquery name="readReports" datasource="Grade_Import_Projects">
    SELECT username, Exam_Name, Date_Taken, Grade
    FROM Reports.csv
    WHERE username IS NOT NULL
</cfquery>

<cfoutput query="readReports">

<cfset grade=Val(grade)>

#grade#
<cfabort>

In the schema for the CSV file, all the variables are of type "char" except for Date_Taken.

On a related note, is there a CF function that will tell me what datatype my variable is? That might shed some light on the issue....

[Dan_Bracuk]

It's not the csv import, it's your code.

<cfdump var="#val('9*')#">

returns 9.

I suggest looping though your query and using querysetcell, along with val() to make everything numeric.  Make sure you specify the currentrow.

[skythrock]

That seems to have worked, Dan. It's returning 95 instead of 95*. Is there any way to confirm that this is a numeric datatype instead of a string?

[Dan_Bracuk]

When a cfquery object is created by querying a database, the datatypes match what you select.  When you create one using query functions, and you specify datatypes, they are what you specify.  Otherwise, CF looks at the data and guesses.

In your case, unless you intend to do math, it doesn't matter.  If you need a numeric field, you can do this:

select field1, field2, 1000 numericfield

from etc

Then, instead of using querysetcell on grade, set in on numericfield.

[-__cfSearching__-]

It should be returning '0'

if nothing else, right? 

No, val('grade') should return 0. As Dan said, Val('95') would return 95 because the first portion is numeric. If however, the asterisk were at the start of the string, then val() would return 0. ie Val('95')

[skythrock]

Thanks all, the querysetcell worked!

[-__cfSearching__-]

I am glad you got things sorted out.

BTW: I am not loving this forum software. It loves to mangle email posts.