|
| |
|
|
steveg
Posts: 274
Joined: 10/20/2004
Status: offline
 |
RE: Editing Problem - 8/9/2005 14:12:34
Hi Duane
I found the defaults and removed them all and got an error which pointed to the date. I added the date default back in and it was fine, but as you say defaults to 01/01/2001. When I remove the date the eroor mesage is:
Database Results Error
Description: Syntax error in date in query expression '##'.
Number: -2147217913 (0x80040E07)
Source: Microsoft JET Database Engine
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
Steve
|
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Editing Problem - 8/9/2005 15:16:56
Null dates are a bit problematic because of the delimiters. For example, if you want to update a date field with today's date, the SQL would look something like this:
UPDATE tblName SET dateFld = #8/9/2005#
The number signs (#) are called delimiters and are places around the value being inserted into the date/time field. When inserting text, one uses single quotes instead of the number sign. When inserting into a numeric field, no delimiter is used.
Null values use no delimiter. So, using the above example, inserting a null would look like this:
UPDATE tblName SET dateFld = Null
We need some code that will include the delimiters when we need them and not include them when we don't. So, open the page that performs the update and replace the entire page with this:
<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<table width="100%" border="1">
<thead>
</thead>
<tbody><!--#include file="../../../_fpclass/fpdblib.inc"-->
<%
If IsDate(Request.Form("prep_person_date") Then
MyDate="#" & Request.Form("prep_person_date") & "#"
Else
MyDate="Null"
End If
fp_sQry="UPDATE RESULTS SET gcn_a='::gcn_a::', gcn_b='::gcn_b::', gcn_c='::gcn_c::', ec_a='::ec_a::', ec_b='::ec_b::', ec_c='::ec_c::', qty_a='::qty_a::', qty_b='::qty_b::', qty_c='::qty_c::', prep_person_name="&MyDate&", prep_person_comments="&MyDate&", prep_person_date='::prep_person_date::' WHERE ID=::ID::"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=16 align=left width=""100%"">Record Updated Successfully!</td></tr>"
fp_sDataConn="online_physicians_prior_authorisation"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
</body>
</html>
You will see this piece of code:
If IsDate(Request.Form("prep_person_date") Then
MyDate="#"&Request.Form("prep_person_date")&"#"
Else
MyDate="Null"
End If
The first line determines whether or not the value entered into prep_person_date is indeed a date. If it is, we create a variable whose value is the date entered surrounded by the delimiter(#). If the value is not a date, we assign Null as the variables value. Since we are now using a variable instead of the form field value, we have to edit the SQL. We change:
prep_person_date='::prep_person_date::'
to
prep_person_date="&MyDate&"
Give the above a try and see if it does the trick.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
steveg
Posts: 274
Joined: 10/20/2004
Status: offline
|
RE: Editing Problem - 8/10/2005 6:00:00
Hi Duane
I tried the code and had a problem. I am working in a secure environment (https) and when I click submit on the edit_test.asp form it should take me to editform.asp (as it was previously) but now I get the standard message that says that I will be going to an environment where everything may not be secure, when I accept it takes me to a 500 error page. This would normally imply that I am trying to go to a page in an http environment and that the page does not exist.
I have been through both sets of code and cannot see anywhere that it tries to re-direct elsewhere and as you know, it was working before, albeit without the dates being handled correctly. Wierd!
I also tried to keep the existing code and just add in your diferences. When I did so, I noticed that you had substituted Mydate into the prep_person_name and comments fiels as well as the prep_person_date field. Did you intend this? Even doing things this way, it still had the same problems as above.
The above would point to it being a problem with the edit_test.asp file that posts to the editform file but we have not made any changes to that.
I am somewhat confused.
Steve
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Editing Problem - 8/10/2005 9:34:33
You are right about the prep_person_comments change being an error on my part. However, the 500 error must come from somewhere else as it is generating before you even get to the page we changed.
One error I do see in the code I supplied is a missing closing parenthesis before Then. See below:
If IsDate(Request.Form("prep_person_date")) Then
MyDate="#"&Request.Form("prep_person_date")&"#"
Else
MyDate="Null"
End If
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
steveg
Posts: 274
Joined: 10/20/2004
Status: offline
|
RE: Editing Problem - 8/10/2005 10:24:35
Hey Duane
Don't understand why but the missing parenthesis seems to have solved the problem????
It accepts the date being empty and confirms with Record successfully updated!
It looks as though I am just about there!!! Phew! However, I will probably hit some snag in the next day or so. I would just like to expres my sincere thanks to the team, with a particular mention to Duane who stuck with me when he had every right to cut me loose.
It really is reassuring to know that there is somebody there to fall back on when you need them.
Once again, thanks guys!
Steve
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
