Solved

CFUPDATE

Posted on 2002-06-13
19
299 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now