asked on

Form for editing - delete previously stored number

- MSAccess
- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I have a form to provide users access to update data. There are 120 fields. The text fields in the form work correctly. The problem I am having is with the CURRENCY fields. If a user has a number previously entered in the field and attempts to delete the data, an error occurs. If they enter a '0' in the form, the form works correctly and stores the '0' and form generates no error messages.

The database is in MSAccess. There are 10 fields with exactly the same properties. I need to have the fields to allow number input with 1 or 2 decimal points (examples: 1.0, 1.5, .25). For reports I calculate totals and averages and need to use the fractional numbers accurately. The field properties for the problem field (LibFTE) are
DataType: CURRENCY
Format: FIXED
Decimal Places: 2
Required: NO
Indexed: NO
Text Align: GENERAL

There are 3 pages to the update process:
default.asp - this page requires a check for a password (works correctly)
Page1.asp - the form; displays stored data and allows editing
End.asp - updates the database

All tables are connected by a field call ChartID.

Page 1 code:

sql = "SELECT * FROM tblStaff WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn
If not objrec.eof then
	LibFTE = objrec("LibFTE")
         [ALL FIELDS ARE IDENTIFIED.....]
End If
[Standard HTML code. Then display update form]

	<p><b>What is the approved FTE?</b>&nbsp;&nbsp;
	<input type="text" name="LibFTE" size="9" value="<%=objRec("LibFTE")%>"><br>
	
Code in End.asp

dim sql, objRec, ChartID
ChartID = Request.form("ChartID")


on error resume next
sql = "SELECT * FROM tblStaff WHERE ChartID = " & ChartID
	Set objRec = Server.CreateObject("ADODB.Recordset")
	objRec.Open sql, objConn, adLockOptimistic, adCmdTable
	objRec("LibFTE") = Request.form("LibFTE")
         [all fields identified]

	objRec.Update


if err.Number <> 0 then 
   Response.Write "<font face='Arial'>Data is not updated."
   Response.Write "<p>Error updating record, please press the <b>Alt</b> + back arrow to correct."
   Response.Write "The most common error is text in the FTE fields."
   Response.End
end if

Open in new window

Ian Pattison

Does the currency field in the database allow nulls?

Are you correctly setting the null in the update statement if the form field is empty?

What error do you get?

JLohman

ASKER

The currency field in the database: the REQUIRED property is set to NO

I do not have any code setting the null in the update statement if the form field is empty. That is probably my problem. What is the code?

I get the generic PAGE CANNOT DISPLAY error.

ASKER CERTIFIED SOLUTION

Ian Pattison

membership

This solution is only available to members.

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

Start Free Trial

JLohman

ASKER

Will that statement be needed for the 12 fields I have with the same properties?

Does this statement come before the objRec.Update ?

Ian Pattison

You need to correctly set the NULL value (when required) for any field that you want the user to be able to delete/update, and that the database permits to be NULL.

You must do this before the update command.

JLohman

ASKER

Added the following before the objRec.Update

      if request.Form("FTE10") = "" then
objRec("FTE10") = NULL
else
objRec("FTE10") = Request.Form("FTE10")
      end if


Same error.... PAGE CANNOT BE DISPLAYED

Ian Pattison

Can you send a screenshot of the error please.

Is it a 500 error - or a 404 error?

Ian Pattison

do you have log entries which show what the problem is? (i.e. in your IIS log files?)

JLohman

ASKER

Sorry, no access to the IIS Log files.

Ian Pattison

Can you take a screen grab of the error - including the address bar?

JLohman

ASKER

It's behind a firewall.

Ian Pattison

So it's definitely a 500 error...

We need to see the error logs, or have the entire scripts to reproduce the error,

It's 9.30pm Saturday night... Will pick this up again tomorrow...

JLohman

ASKER

Found a solution:

      IF Request.form("LibFTE") <> "" THEN
            objRec("LibFTE") = Request.form("LibFTE")
      ELSE
            objRec("LibFTE") = null
      END IF