Form for editing - delete previously stored number

JLohman
JLohman used Ask the Experts™
on
- 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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CSIPComputingHead of IT

Commented:
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?

Author

Commented:
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.
Head of IT
Commented:
Ok, I have an ASP site that uses a date field.

The date field is set to "Required=NO" just like yours.

If I want a user to be able to remove an existing date, I need to do this:

if request.Form("datecompleted") = "" then
      objRS("datecompleted") = NULL
      else
      objRS("datecompleted") = Request.Form("datecompleted")
end if

That correctly puts the null into the database if the form field is blank.

Hope that helps!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

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

Does this statement come before the       objRec.Update  ?
CSIPComputingHead of IT

Commented:
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.

Author

Commented:
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
CSIPComputingHead of IT

Commented:
Can you send a screenshot of the error please.

Is it a 500 error - or a 404 error?
CSIPComputingHead of IT

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

Author

Commented:
Sorry, no access to the IIS Log files.
CSIPComputingHead of IT

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

Author

Commented:
It's behind a firewall.  Screen image
CSIPComputingHead of IT

Commented:
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...

Author

Commented:
Found a solution:

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial