Solved

CFUPDATE

Posted on 2002-06-13
19
304 Views
Last Modified: 2013-12-24
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
Comment
Question by:CTerreri
19 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 7076289
This could be an error in assignment.

can you post your code.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 7076294
Usually associated with a stored procedure.

Like passing a string value for a numeric parameter.

CJ
0
 

Author Comment

by:CTerreri
ID: 7076608
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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 2

Expert Comment

by:vbDoc
ID: 7079004
Access will not except NULL into Number Fields.
0
 

Accepted Solution

by:
jfolker earned 150 total points
ID: 7306034
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
 
LVL 19

Assisted Solution

by:cheekycj
cheekycj earned 150 total points
ID: 7307600
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
 

Author Comment

by:CTerreri
ID: 10767649
I am increasing points to 300.
0
 

Author Comment

by:CTerreri
ID: 10767737
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 10768401
can you post your cfquery code.

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 10774115
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
 

Author Comment

by:CTerreri
ID: 10775575
<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
 
LVL 19

Expert Comment

by:cheekycj
ID: 10775667
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
 

Author Comment

by:CTerreri
ID: 10775865
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 10776822
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
 

Author Comment

by:CTerreri
ID: 10776842
Thanks for the feedback.  I will keep this in mind for the future.
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 10776901
did it work?
0
 

Author Comment

by:CTerreri
ID: 10777110
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
 
LVL 19

Expert Comment

by:cheekycj
ID: 10777132
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

Featured Post

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question