asked on

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?

cheekycj

This could be an error in assignment.

can you post your code.

CJ

cheekycj

Usually associated with a stored procedure.

Like passing a string value for a numeric parameter.

CJ

CTerreri

ASKER

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    Code    Award Date </font></p>
<p> <CFOUTPUT QUERY="qscholarship"><INPUT TYPE="text" NAME="sch_id" VALUE="#qscholarship.sch_id#" SIZE="2">           
<INPUT TYPE="text" NAME="sch_cd" VALUE="#qscholarship.sch_cd#" SIZE="3">       
<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
    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'.

vbDoc

Access will not except NULL into Number Fields.

ASKER CERTIFIED SOLUTION

jfolker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

cheekycj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CTerreri

ASKER

I am increasing points to 300.

CTerreri

ASKER

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.

cheekycj

can you post your cfquery code.

CJ

cheekycj

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

CTerreri

ASKER

<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>

cheekycj

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

CTerreri

ASKER

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.

cheekycj

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

CTerreri

ASKER

Thanks for the feedback. I will keep this in mind for the future.

cheekycj

did it work?

CTerreri

ASKER

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.

cheekycj

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