We help IT Professionals succeed at work.

FrontPage and a database

belch
belch asked
on
How do I get a FrontPage form to update a record in an Access database.

This is what I have: I have a page that when the submit button is clicked it sends information including a time stamp (start time) to the database as a new record. When the second submit button is clicked it sends another time stamp (stop time) to the database as a new record.

How can I combine these two into one record?
Comment
Watch Question

What code are you using currently to insert it into the db?

Author

Commented:
I have not written any code at all for this, I am using the Form Properties window to send to a database.

Author

Commented:
This is what is on the HTML tab.

<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
     Err.Clear

     Set fp_conn =  Server.CreateObject("ADODB.Connection")
     FP_DumpError strErrorUrl, "Cannot create connection"

     Set fp_rs = Server.CreateObject("ADODB.Recordset")
     FP_DumpError strErrorUrl, "Cannot create record set"

     fp_conn.Open Application("db2_ConnectionString")
     FP_DumpError strErrorUrl, "Cannot open database"

     fp_rs.Open "KPA", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
     FP_DumpError strErrorUrl, "Cannot open record set"

     fp_rs.AddNew
     FP_DumpError strErrorUrl, "Cannot add new record set to the database"
     Dim arFormFields0(0)
     Dim arFormDBFields0(0)
     Dim arFormValues0(0)


     FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0

     FP_SaveFieldToDB fp_rs, Now, "Stop"

     fp_rs.Update
     FP_DumpError strErrorUrl, "Cannot update the database"

     fp_rs.Close
     fp_conn.Close

     FP_FormConfirmation "text/html; charset=windows-1252",_
                              "Form Confirmation",_
                              "Thank you for submitting the following information:",_
                              "KPA%20Stop.asp",_
                              "Return to the form."

End If
End If

%>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body background="bckgd.jpg">

<form method="POST" action="--WEBBOT-SELF--">
  <!--webbot bot="SaveDatabase" startspan SuggestedExt="asp"
  U-ASP-Include-Url="_fpclass/fpdbform.inc" S-DataConnection="db2"
  S-RecordSource="KPA" U-Database-URL="db2.mdb" S-Builtin-Fields="Timestamp"
  S-Builtin-DBFields="Stop" --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot
  bot="SaveDatabase" endspan -->
  <p align="center"><img border="0" src="stop_sign.gif"></p>
  <p align="center">&nbsp;</p>
  <p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>

</body>

</html>


Ok...here's a great link to help:

http://www.sitebuilder.ws/frontpage/tutorials/update.htm

It shows you how to pull the existing value from the db in a form and modify it, then when you submit that form your changes to the db occur.

Let me know if you have any problems with it.

Author

Commented:
Can you explain steps 5, 6, and 7. I am not following it.
Sure.

5.  Speakers.asp is their page which displays the info in the db.  They are suggesting that you make the First name a hyperlink to the update.asp...to allow someone to make the changes.

6.  You are adding a parameter which will tell update.asp which record to display.

7. Click the hyperlink to ensure that update.asp does indeed display the correct info for that record.

Author

Commented:
This does not seem to be what I need. I went the tutorial and now I am getting errors. I don?t want the person to type in the ID number of the record just to enter the stop time. I am trying to keep this simple.

I just need the stop time to be entered in the last record in the table, I would think there would be a simple way to do that besides changing all the pages.

Author

Commented:
This does not seem to be what I need. I went the tutorial and now I am getting errors. I don?t want the person to type in the ID number of the record just to enter the stop time. I am trying to keep this simple.

I just need the stop time to be entered in the last record in the table, I would think there would be a simple way to do that besides changing all the pages.

Author

Commented:
This does not seem to be what I need. I went the tutorial and now I am getting errors. I don?t want the person to type in the ID number of the record just to enter the stop time. I am trying to keep this simple.

I just need the stop time to be entered in the last record in the table, I would think there would be a simple way to do that besides changing all the pages.
You aren't changing all the pages, just modifying a database record.

The simplist way to do it (IMHO) is NOT to try to depend on the FP wizards at all and to code it all with ASP.

The problem is that you have two submissions of the same form.  They will always both create a new record.  They are being submitted to the same asp script.

If you want to modify a record you are going to have to create a new form or tell it somehow to go to a new asp script to do the processing.

The web page can't tell you that this is the second time the form was submitted, therefore cannot process on that kind of event.

What is it you are attempting to time?  Perhaps there is a javascript or other way of doing it rather than trying to force a FP wizard to do something it wasn't designed for.

Author

Commented:
I do not know how to code with ASP, so I guess I am in trouble.

Actually I have two different forms (on different pages) adding info to the same table. I think (but I am not sure) that the asp scripts are on different pages.

This is what I am trying to attempt:
I am calculating the amount of time spent on 13 different projects (just numbered 1 - 13). So I want someone to go to the fist page and put their username in and select the project number and click start. This logs the data into the table (user, project #, start time) then pulls the next web page that just simply has a stop button, which I would like to have added to the previous record.
You are probably better to have the server do the timing and not use a start/stop time from the form....but this would require application variables and some for advanced asp coding.

Is the project you are timing online, or are you counting on the user to be doing something offline and come back to let you know when they are done?

Author

Commented:
they will be doing something offline, then coming back to stop the "timer".
ok.....then you can't use application variables afterall.

hmmmmmmm...

Ok....here goes...

I need from you the name of this file (the one you posted the code for above), the name of your database, which folder in your web the db lives in and the exact fieldname you want the stop time to go into.

Author

Commented:
name of this file - KPA Stop.asp
database - db2
folder - c:\Inetpub\wwwroot\myweb
Field name - table is KPA, field is Stop.

whould it be eaiser to send you the database?

thanks
No it's ok.  Let's see if we can get this baby to fly.
first, delete the:
fp_rs.Close
fp_conn.Close

 from the code...then


Replace the form with this (replace the word table with the tablename):
<%If request.querystring("action") = stop then
fp_rs.movelast
sql="Update table set Stop = '" & formatdatetime(now) &"' WHERE fp_rs('"stop"')"
conn.execute sql
End If%>


<form method="POST" action="KPA Stop.asp?action=stop">
 <p align="center"><img border="0" src="stop_sign.gif"></p>
 <p align="center">&nbsp;</p>
 <p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>


I'm pretty sure you will get an error on that, but I'm trying to to recreate the db connection....I can do it if this doesn't work, but give it a try first.

Author

Commented:
Im sorry, what do you mean by "Replace the form"?
Replace this part of your original HTML:

<form method="POST" action="--WEBBOT-SELF--">
 <!--webbot bot="SaveDatabase" startspan SuggestedExt="asp"
 U-ASP-Include-Url="_fpclass/fpdbform.inc" S-DataConnection="db2"
 S-RecordSource="KPA" U-Database-URL="db2.mdb" S-Builtin-Fields="Timestamp"
 S-Builtin-DBFields="Stop" --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.inc"--><!--webbot
 bot="SaveDatabase" endspan -->
 <p align="center"><img border="0" src="stop_sign.gif"></p>
 <p align="center">&nbsp;</p>
 <p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>


With what I put above.

Author

Commented:
this is the error i got:


The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

--------------------------------------------------------------------------------

Please try the following:

Click the Refresh button, or try again later.

Open the cpmamb home page, and then look for links to the information you want.
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

--------------------------------------------------------------------------------

Technical Information (for support personnel)

Error Type:
Microsoft VBScript compilation (0x800A03EA)
Syntax error
/timer/KPA Stop.asp, line 12, column 35
If request.querystring("action") = stop then
----------------------------------^


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)

Page:
GET /timer/KPA Stop.asp

Time:
Wednesday, October 03, 2001, 2:33:15 PM


More information:
Microsoft Support
 

Author

Commented:
is the question mark supposed to be in this line...

<form method="POST" action="KPA Stop.asp?action=stop">
yes...that's how we tell it to specify the querystring...do you have it in there?

Author

Commented:
yes i do - and that is the error message i get.
Ok...this is too hard for me to do remotely.  Giving me a brain cramp!

email me the pages & the db please to mailto:eeFPhelp@classylinks.ca
Thanks for the A.

For anyone accessing the PAQ here is what we did:

<%
     Set fp_conn =  Server.CreateObject("ADODB.Connection")
     fp_conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ="& Server.MapPath("db2.mdb")&";"

     Set fp_rs = Server.CreateObject("ADODB.Recordset")

     fp_conn.Open
     fp_rs.Open "KPA", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable

If request.querystring("action") = "stop" then
fp_rs.movelast
getID = fp_rs("ID")
sql="Update KPA set Stop = '" & formatdatetime(now) &"' WHERE ID = "&getID&""
fp_conn.execute sql
response.write "Time sucessfully stopped!"
Else%>


<form method="POST" action="KPA Stop.asp?action=stop">
<p align="center"><img border="0" src="stop_sign.gif"></p>
<p align="center">&nbsp;</p>
<p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>
<%end if%>

Commented:
Hi:

Can you help me with a similar problem, I have used the Sitebuilder tutorial, what is the purpose of sending info to an update_record.asp page and having it refresh the info to admin.asp page, if all the records in the speakers table show up. the reason I ask this is that I need the admin.asp page to be filtered to show only record from the previous form.

Another words:

editsearch.asp -
this form has 2 entry fields, UserName and Password it passes info to editsearchresults.asp

editsearchresults.asp -
this form is a database results page, with a few fields showing information,(LastName, Address, City, etc.) in the database results properties the criteria is set like this
UserName equals [UserName] And
Password equals [Password] And

This works great it only shows the record with this matching info.
As in the tutorial I made a hyperlink in the LastName field
set my parameters and sent this info to editrecord.asp

editrecord.asp -
this form is a database results page, in the database results properties a custom query is set (the query is verified and seems to work) I have defaults also set with a 0. This page has no fields showing information. The page then refreshes to admin.asp

admin.asp -
this form is a database results page, with one one table with 3 columns and field
LastName in column 1
Edit in column 2
Delete in column 3

When I click on edit it brings up form edit.asp and the correct record, I can edit the record and all of this works great.

The problem is admin.asp shows every record in the database table.

ex:
______________________________________________________
Joe              Edit                 Delete
______________________________________________________
Fred             Edit                 Delete
______________________________________________________
Bob              Edit                 Delete
______________________________________________________

What I need is the record from editsearchresults.asp to be the only one showing.

Sorry for the long explaination. I will post this as a question, but I needed to referance the sitebuilders website.
Thanks
Paulvb