FrontPage and a database
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?
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?
ClassyLinks
What code are you using currently to insert it into the db?
ASKER
I have not written any code at all for this, I am using the Form Properties window to send to a database.
ASKER
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("R
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open Application("db2_Connectio
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.
<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="_fpclas
S-RecordSource="KPA" U-Database-URL="db2.mdb" S-Builtin-Fields="Timestam
S-Builtin-DBFields="Stop" --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.in
bot="SaveDatabase" endspan -->
<p align="center"><img border="0" src="stop_sign.gif"></p>
<p align="center"> </p>
<p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>
</body>
</html>
<%
' FP_ASP ASP Automatically generated by a Frontpage Component. Do not Edit.
On Error Resume Next
strErrorUrl = ""
If Request.ServerVariables("R
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear
Set fp_conn = Server.CreateObject("ADODB
FP_DumpError strErrorUrl, "Cannot create connection"
Set fp_rs = Server.CreateObject("ADODB
FP_DumpError strErrorUrl, "Cannot create record set"
fp_conn.Open Application("db2_Connectio
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.
<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="_fpclas
S-RecordSource="KPA" U-Database-URL="db2.mdb" S-Builtin-Fields="Timestam
S-Builtin-DBFields="Stop" --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--#include file="_fpclass/fpdbform.in
bot="SaveDatabase" endspan -->
<p align="center"><img border="0" src="stop_sign.gif"></p>
<p align="center"> </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.
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.
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
ASKER
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.
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.
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.
ASKER
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.
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?
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?
ASKER
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.
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.
ASKER
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
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("actio
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"> </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.
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("actio
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"> </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.
ASKER
Im sorry, what do you mean by "Replace the form"?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("actio
--------------------------
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
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("actio
--------------------------
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
ASKER
is the question mark supposed to be in this line...
<form method="POST" action="KPA Stop.asp?action=stop">
<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?
ASKER
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@classylink
email me the pages & the db please to mailto:eeFPhelp@classylink
Thanks for the A.
For anyone accessing the PAQ here is what we did:
<%
Set fp_conn = Server.CreateObject("ADODB
fp_conn.ConnectionString="
Set fp_rs = Server.CreateObject("ADODB
fp_conn.Open
fp_rs.Open "KPA", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
If request.querystring("actio
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"> </p>
<p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>
<%end if%>
For anyone accessing the PAQ here is what we did:
<%
Set fp_conn = Server.CreateObject("ADODB
fp_conn.ConnectionString="
Set fp_rs = Server.CreateObject("ADODB
fp_conn.Open
fp_rs.Open "KPA", fp_conn, 1, 3, 2 ' adOpenKeySet, adLockOptimistic, adCmdTable
If request.querystring("actio
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"> </p>
<p align="center"><input type="submit" value="Stop Timer" name="stop"></p>
</form>
<%end if%>
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
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