Solved

How comparea nd change  the date fields in FP before updating Access database

Posted on 2004-09-16
12
305 Views
Last Modified: 2013-12-24
Hi Experts,

I used the front page wizard to create a database system which has some date fields.
I dont want to update date fields in to the access database which has a default value of 1/1/1900.
I would like to implement the following logic before passing on the info to access database
for updating.
****************************************
if the input date is 1/1/1900
then
     do nothing
else
   use the input date to update the appropriate field in access.
endif
****************************************
I tried to modify the update.asp file below....

<!--webbot bot="PurpleText" PREVIEW="-Important-  If you modify this Database Results region using the Database Results Wizard, then your Database Editor will no longer work.  If you accidentally open the Database Results Wizard, simply click Cancel to exit without regenerating the Database Results region." -->

<script Language="JavaScript">
<!--
      top.list.location.href = top.list.location.href;
// -->
</script>
<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes
s-dataconnection="booksonline" b-tableformat="FALSE" b-menuformat="FALSE"
s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE"
b-listlabels="FALSE" b-listseparator="FALSE"
i-ListFormat="0" b-makeform="FALSE" s-RecordSource s-displaycolumns s-criteria
s-order
s-sql="UPDATE Results SET ID = '::ID::' , Bookname = '::Bookname::' , Description = '::Description::' , PublishYear = '::PublishYear::' , Category = '::Category::' ,
ISBN = '::ISBN::' , Price = '::Price::' , DateAcquired = '::DateAcquired::' , Department = '::Department::' , BorrowedBy = '::BorrowedBy::' , ReturnDate = '::ReturnDate::' ,
BorrowDate = '::BorrowDate::' , RecordedBy = '::RecordedBy::'  WHERE (Key = ::Key::)" b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="ID=&Bookname=&Description=&PublishYear=&Category=&ISBN=&Price=&DateAcquired=&Department=&BorrowedBy=&ReturnDate=&BorrowDate=&RecordedBy=&Key=0"
s-NoRecordsFound="Record updated in table." i-MaxRecords="1" i-GroupSize="0" u-dblib="../../../_fpclass/fpdblib.inc" u-dbrgn1="../../../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" Tag="BODY" BOTID="0" preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot;
align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly;
the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" startspan --><!--#include file="../../../_fpclass/fpdblib.inc"-->

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%

fp_sQry="UPDATE Results SET ID = '::ID::' , Bookname = '::Bookname::' , Description = '::Description::' , PublishYear = '::PublishYear::' , Category = '::Category::' , ISBN = '::ISBN::' , Price = '::Price::' , Department = '::Department::' , BorrowedBy = '::BorrowedBy::' , RecordedBy = '::RecordedBy::'  WHERE (Key = ::Key::)"

if DateAcquired = "1/1/1900" Then
Respone.write DateAcquired
else
fp_sQry="UPDATE Results SET  DateAcquired = ::DateAcquired:: WHERE (Key = ::Key::)"
end if
----------------------------------------------------------------------------------------------------
I get a compiliation  errro when comparing the date
if DateAcquired = "1/1/1900"

Any help would be appreciated..

Thank you.
0
Comment
Question by:asidu
  • 7
  • 5
12 Comments
 
LVL 12

Expert Comment

by:rcmb
ID: 12078910
What you need to do is set default values in your update page. Open your update page and double click on the top yellow bar to initiate the DBRW. In step three click on defaults and assign your default value to each date field. I strongly recommend you change your default value to something like 1/1/1955 because 1900 could easily be mistaken for 2000 if you display the last two digits. By assigning default values then you can leave your form field and it will automatically insert your default value into the database.

Now a little more data - You cannot modify the SQL statement in the DBRW on the fly like you can when using VBScript. The only way you can send information to the DBRW query is through a form or a URL string (somefile.asp?ID=1).

If you need to keep the value set to something then you will need to use VBScript to write to your database. You can modify your SQL statement on the fly by taking the results of the form and then manipulating the SQL query prior to processing (FP will not allow you to do this).

With all of that said you can create two DBRWizards on the same page and then let your IF statement determine which one to use -- like so:

<% If request("DateAcquired") = "1/1/1900" Then %>

DBRW1 -- Do UPDATE without DateAcquired

<% Else %>

DBRW2 -- Do UPDATE with DateAcquired

<% End if %>

Hope this helps -- let me know if you need more clarification.

RCMB
0
 
LVL 12

Author Comment

by:asidu
ID: 12081347
Thanks for the pointers RCMB.

I note that I cannot modify the   fp_sQry twice.... only one of the statement is executed when updating the database.
So I have a constraint to my original line of logic.

Your suggestion seems logical. I could cut paste the whole chunk of code into DBRW1
and DBRW2 taking care of what I want....from the update.asp

<% If request("DateAcquired") = "1/1/1900" Then %>

DBRW1 -- Do UPDATE without DateAcquired

<% Else %>

DBRW2 -- Do UPDATE with DateAcquired

<% End if %>

Above idea could take care of one variable "DateAcquired" but in fact I have to 3 such  date feilds (Borrowdate, Returndate)
which need to be taken care off. Is there some neat way I could take care of all the similar condtions all at once.

One other related issue, where can I read the meanings of the FP generated codes.
Looking forward for some pointers.

Thank u.
0
 
LVL 12

Expert Comment

by:rcmb
ID: 12083054
The first issue we need to look at is the form -- Are you in fact creating a blank form or is the form being populated from the database?

If you have the form get its data from the database then the date fields would be filled with the data already in there and you could then have the fields not display your 1/1/1900. Then use the default values in your update page. This would solve all and ensure you keep your old data and only update changed data.

RCMB
0
 
LVL 12

Author Comment

by:asidu
ID: 12084464
The from gets the date fields from the database.

The problem has appeared when editing the database, its because I did not put in the default values
in the original design.
So I reckon the first thing I should do is to force a default value (1/1/1900)
for the exsisting data base for all null dates. Then use the logic you suggested earlier.

Is that the right logical thing to do ?

Looking forward for you adivce.

Thank you

ASIDU
0
 
LVL 12

Accepted Solution

by:
rcmb earned 500 total points
ID: 12086034
Okay lets say the user opens the form (to edit the existing data) and all of the date fields are empty.

The user makes changes as needed and inputs the "DateAcquired" but leaves the other date fields blank. (Here lies your problem)

Now the user presses the Submit button and the form is passed to the update.asp page for processing. In the update.asp you need to identify the default values for empty date fields.

By doing this it will only write the 1/1/1900 date if the submitted value is blank. This fixes the problem.

On the update.asp double click on the top yellow bar to start the wizard

In step 3 select Defaults... and everywhere you have a date field enter the value of 1/1/1900.

Finish the wizard and save the page.

Now when the user submits the form it will save all submitted values and only write 1/1/1900 to fields that contain no values.

Now the next problem is how to we keep from displaying 1/1/1900 on the form when we first open it to edit:

Just before your <body> tag insert this code:

<script language="javascript">

function SetForm(){
   for ( i=0; i < document.FORMNAME.elements.length ; i++ ){
      ThisElement = document.FORMNAME.elements[i]
      if (ThisElement.value=='1/1/1900') {
          ThisElement.value='';
      }
   }
}
</script>

Now change your body tag to look like so:

<body onLoad="SetForm()">

Make sure you change FORMNAME to your actual form name (look for <form ... in your html code and if you do not see name="???" then put in name="MyForm" and change FORMNAME to MyForm)

RCMB
0
 
LVL 12

Author Comment

by:asidu
ID: 12090246
Thanks again RCMB.

I am glad that the solution is coming up neatly.

One other realted issue is that for the display.
I use several repeated codes for masking of the dummy date. (1/1/1900) in the results_page.asp and
the detail.asp

Is there a neater way to mask of the dummy date easily a neater technic.

Look forward for some pointers.

ASIDU





0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 12

Expert Comment

by:rcmb
ID: 12091275
The only way I know to do this is to check the value before it is displayed like so:

<td>
<%
If fp_rs("DateAcquired") = "1/1/1900" Then
    response.write "No Date Provided"
Else
    response.write "" & fp_rs("DateAcquired")
End if
%>
</td>

Use this inplace of the front page generated results field.

RCMB
0
 
LVL 12

Author Comment

by:asidu
ID: 12091508
Thank you RCMB
I am doing that already !
0
 
LVL 12

Author Comment

by:asidu
ID: 12100752
RCMB,

My page finally worked on a stand alone with IIS running on a  Win2K machine.
When, I  port the codes on to a production r NT 4 server. The links work pretty fine.
But when  I edit and submit the data for a record..i get the following error.

Description: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers

I checked the access rights for users with any success.

Are there issues between FrontPage created files running on NT4 ?
or  what am I doing wrong ?
Any pointers apprecatied.

ASIDU
0
 
LVL 12

Author Comment

by:asidu
ID: 12100866
Sorry for the typo errors in the earlier post

RCMB,

My page finally worked on a stand alone with IIS running on a  Win2K machine.
When, I  port the codes on to a production  NT 4 server. The links work pretty fine.
I am able to display the results.
But when  I edit and submit the data for a record..I get the following error.

Description: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers

Seems that updates and submissions are not allowed into the database.

I checked the access rights for users without any success.

Are there issues between FrontPage created files running on NT4 ?
or  what am I doing wrong ?
Any pointers apprecatied.

ASIDU
0
 
LVL 12

Expert Comment

by:rcmb
ID: 12101013
"Operation must use an updateable query"

This is a permissions issue or read only attribute

Check the fpdb directory and make sure you have write permission and also verify the database does not have the read only attribute checked.

RCMB
0
 
LVL 12

Author Comment

by:asidu
ID: 12120273
Thank you RCMB.
You are right changing the fpdb files cleard the problem.
Glad every thing is running smoothly now.
Thanks agian.

ASIDU
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now