Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CFUPDATE

Posted on 2002-06-13
19
Medium Priority
?
313 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
LVL 2

Expert Comment

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

Accepted Solution

by:
jfolker earned 600 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 600 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

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.

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…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

670 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