Solved

CFUPDATE

Posted on 2002-06-13
19
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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

Limited time offer using promo code EXPERTS25

Designed with a wealth of functionality and convenience, ATEN's new Thunderbolt™ 2 Sharing Switch takes your Thunderbolt setup to the next level. Now through August 31, 2017, Experts Exchange members get 25% off the US7220 on the ATEN USA eShop using promo code EXPERTS25.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

630 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