• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

CFUPDATE

I am getting the following error when trying to perform an update on an MS Access 2.0 table.  

[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification (null)

What does this mean exactly?
0
CTerreri
Asked:
CTerreri
2 Solutions
 
cheekycjCommented:
This could be an error in assignment.

can you post your code.

CJ
0
 
cheekycjCommented:
Usually associated with a stored procedure.

Like passing a string value for a numeric parameter.

CJ
0
 
CTerreriAuthor Commented:
UPDATE FORM:

 
<FORM ACTION="update_scholarship-OK.cfm" METHOD="post">
     
      <table width="100%" border="1" cellpadding="15" cellspacing="0">
        <tr>
          <td width="322" valign="top" height="213">
            <p><font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">Student
              ID </font><font color="#000000"><CFOUTPUT QUERY="qscholarship"><INPUT TYPE="hidden" NAME="student_id"  VALUE="#URL.student_id#" SIZE="10"></CFOUTPUT> <cfoutput>#qscholarship.student_id#</cfoutput></font></p>
            <p><font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">ID
              Number &nbsp;&nbsp;&nbsp;Code &nbsp;&nbsp;&nbsp;Award Date </font></p>
            <p> <CFOUTPUT QUERY="qscholarship"><INPUT TYPE="text" NAME="sch_id"  VALUE="#qscholarship.sch_id#" SIZE="2">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                  <INPUT TYPE="text" NAME="sch_cd"  VALUE="#qscholarship.sch_cd#" SIZE="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                  <INPUT TYPE="text" NAME="award_date"  VALUE="# LSDateFormat(qscholarship.award_date,'M/DD/YY') #" SIZE="8"></CFOUTPUT>
                     
                     
            <p><font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">Amount
              &nbsp;&nbsp;&nbsp;&nbsp;Pay Date</font></p>
            <p> <CFOUTPUT QUERY="qscholarship"><INPUT TYPE="text" NAME="sch_amt"  VALUE="# LSCurrencyFormat(qscholarship.sch_amt, 'none')#" SIZE="5">
                  <INPUT TYPE="text" NAME="pay_date"  VALUE="#LSDateFormat(qscholarship.pay_date, 'M/DD/YY')#" SIZE="8">
                 
                     
            <p><font face="Arial, Helvetica, sans-serif" size="2" color="##9966cc">Comments</font></p>
            <p>
                <INPUT TYPE="hidden" NAME="timestamp"  VALUE="05/15/02"></CFOUTPUT>
                <INPUT TYPE="submit" NAME="submit"  VALUE="update">
                   
            </td>
        </tr>
      </table>
       
       </FORM>


update_scholarship-OK.cfm:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">



 
 
<CFTRANSACTION>

     <CFUPDATE DATASOURCE="connstudent" TABLENAME="scholarship"
     DBTYPE="ODBC" FORMFIELDS="student_id, sch_id">

</CFTRANSACTION>

<html>
<head>
     <title>Untitled</title>
</head>

<body>

<CFLOCATION URL="student_records.cfm" ADDTOKEN="No">

</body>
</html>

Once the user hits submit, the cast error appears.  By the way this does not involve stored procedures, it is simply a Microsoft 2.0 Table. The datatype of sch_id is 'number'.  

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
vbDocCommented:
Access will not except NULL into Number Fields.
0
 
jfolkerCommented:
Try using real SQL queries instead of <CFUPDATE>, etc. You'll experience fewer problems and it'll be more beneficial to you in the future...
0
 
cheekycjCommented:
I agree with jfolker.. if you post your datatypes for the fields in your table. We can help you contruct an update statement for you.

CJ
0
 
CTerreriAuthor Commented:
I am increasing points to 300.
0
 
CTerreriAuthor Commented:
I have decided to use the CFQUERY tag and include an UPDATE within the tag.  This is much more reliable and get's around this CAST specification problem which I still cannot identify the source of.
0
 
cheekycjCommented:
can you post your cfquery code.

CJ
0
 
cheekycjCommented:
Its odd that the question asker just increased the pts and then without providing feedback is asking to delete the question.

if they want to have this deleted.. thats fine by me.

CJ
0
 
CTerreriAuthor Commented:
<CFQUERY NAME="qupdate" DATASOURCE="#db#" DBTYPE="ODBC">
UPDATE scholarship
      SET scholarship_code = #form.scholarship_code#,
            award_date = '#form.award_date#',
            scholarship_amount = #form.scholarship_amount#,
            pay_date = '#LSDateFormat(form.pay_date, 'M/DD/YY')#',
            comments = '#form.comments#',
            user_id='#session.login#',
            timestamp='#lsdateformat(now())#'
            
where student_id='#student_id#'
and scholarship_id=#scholarship_id#
      

</CFQUERY>
0
 
cheekycjCommented:
just for debugging purposes before the cfquery tag.. add this:

<cfoutput>
UPDATE scholarship
     SET scholarship_code = #form.scholarship_code#,
          award_date = '#form.award_date#',
          scholarship_amount = #form.scholarship_amount#,
          pay_date = '#LSDateFormat(form.pay_date, 'M/DD/YY')#',
          comments = '#form.comments#',
          user_id='#session.login#',
          timestamp='#lsdateformat(now())#'
         
where student_id='#student_id#'
and scholarship_id=#scholarship_id#
     

</CFoutput>
<cfabort>

could you post what that displays.  And what the field type for each column is?

BTW, does this mean you do NOT want this question deleted?

Thanx,
CJ
0
 
CTerreriAuthor Commented:
Here is the post:

UPDATE scholarship SET scholarship_code = 1, award_date = '12/15/04', scholarship_amount = 500.00, pay_date = '12/25/04', comments = 'test', user_id='cterreri', timestamp='Apr 7, 2004' where student_id='8801069' and scholarship_id=33

The datatype for column is:

student_id/varchar(9)
scholarship_sequence_number/int
timestamp/datetime
scholarship_code/int (4)
scholarship_id/int (4)
scholarship_amount/varchar(50)
award_date/datetime
pay_date/datetime
user_id/varchar(14)
comments/varchar(100)

I must say that I have changed the DB to MS SQL Server 2K and that is where I am testing this code now.  At the time I posted this probem I was still using MS Access.

As far as keeping this question alive, you will notice that I went ahead and awarded the points to jfolker and to you since each of you recommended that I use SQL queries instead of CFUPDATE and that is what I have done here and all other subsequent updates that I have coded since then.
0
 
cheekycjCommented:
the possible problems were all date/time fields and scholarship amount.

try this:
<CFQUERY NAME="qupdate" DATASOURCE="#db#" DBTYPE="ODBC">
UPDATE scholarship
     SET scholarship_code = #form.scholarship_code#,
          award_date = #CreateODBCDate(form.award_date)#,
          scholarship_amount = '#form.scholarship_amount#',
          pay_date = #CreateODBCDate(LSDateFormat(form.pay_date, 'M/DD/YY')#,
          comments = '#form.comments#',
          user_id='#session.login#',
          timestamp= #CreateODBCDateTime(now())#
         
where student_id='#student_id#'
and scholarship_id=#scholarship_id#
     

</CFQUERY>

CJ
0
 
CTerreriAuthor Commented:
Thanks for the feedback.  I will keep this in mind for the future.
0
 
cheekycjCommented:
did it work?
0
 
CTerreriAuthor Commented:
I am not having any problems with this code.  Once I switched over to the SQL Query method rather than CFUPDATE I know longer have a problem.  I hope I didn't steer you wrong in thinking that there still was a problem.  
0
 
cheekycjCommented:
Oh, I thought there still was an issue.  

SQL Server is more robust than Access so it probably can handle basic casting better.  Though its still better to send better data in.  You can try the code I posted.. it should work.



CJ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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