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.
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.
- 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>
<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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Are you correctly setting the null in the update statement if the form field is empty?
What error do you get?
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.
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.
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("datecomplete
objRS("datecompleted") = NULL
else
objRS("datecompleted") = Request.Form("datecomplete
end if
That correctly puts the null into the database if the form field is blank.
Hope that helps!
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("datecomplete
objRS("datecompleted") = NULL
else
objRS("datecompleted") = Request.Form("datecomplete
end if
That correctly puts the null into the database if the form field is blank.
Hope that helps!
Will that statement be needed for the 12 fields I have with the same properties?
Does this statement come before the objRec.Update ?
Does this statement come before the objRec.Update ?
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.
You must do this before the update command.
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
if request.Form("FTE10") = "" then
objRec("FTE10") = NULL
else
objRec("FTE10") = Request.Form("FTE10")
end if
Same error.... PAGE CANNOT BE DISPLAYED
Premium Content
You need an Expert Office subscription to comment.Start Free Trial