editing Access database from web

[dwighta]

I have managed to connect a database to an intranet site, using Access 97 and FP 2000. I am able to see the data, search the database, and create new records (from different FP pages, depending on the configuration.

Now, is there a way to edit existing records in the database? If something needs to be changed in a record, can I do it from the web?

Dwight Agner

 

[bassguy]

You can update existing records in your database but you have to be creative in what you tell frontpage to do.....frankly, it may be easier to code yourself in ASP. But, as this is a frontpage forum, Here goes.

I am assuming that your table has a primary key field that will uniquely identify each row and that you are only updating information in one table at a time.

You will need 3 pages to complete an update.
I will call them choose.asp, edit.asp, and submit.asp

Page 1
choose.asp is a page to select the record to be edited.

First build a table using INSERT DATABASE RESULTS. Build in appropriate search capabilities to narrow the list. Show all of the fields that you would like to edit.

Make a hyperlink to edit.asp by choosing one of the fields <<yourfieldname>> in your table.

Here is an important part..
in the Hyperlink properties wizard click on the Parameters button....then click ADD. In the NAME field select the field that is your primary key. The Value Field should populate with something that looks like <%=FP_FieldURL(fp_rs,&quot;yourprimarykey&quot%>. After these 2 are populated click OK.

Continue to add parameters for all of the fields that you wish to edit.

This Hyperlink will now Launch EDIT.asp and will bring along with it the data that you selected.


Page 2
Edit.asp is a form that will allow for the changes into your data then will submit it to the final page for updating your database.
Make a form with the appropriate number of text boxes for the table you are editing.
Double Click on the text box to bring up the text box properties dialog. Name the text boxes with the field names in your table....In the Value field type in <%=request(&quot;yourfieldname&quot%> for all of the fields that you are editing. Do not make a text box for the Primary key field. The Primary Key needs to be contained in a Hidden field of the form. You can do this by double Clicking on the form to open the Form Properties Dialog Box. Click the Advanced button to open the Advanced form properties Dialog. Click the Add Button. In the Name Field Put the Name the Primary key field in the Value field put <%=request(&quot;yourprimarykey&quot%> After you enter the Primary key information click OK until you are back to the FORM PROPERTIES dialog and select the button that says &quot;SEND TO OTHER&quot;
then Click the Options button and enter in the name of your third page....in this example submit.asp Save This Page.

Page 3
Submit.asp this page uses the INSERT DATABASE RESULTS wizard but we customize the query statement to push your new data up instead of requesting data down.
Open the Database Results Wizard

In step 1 of the wizard select your database.

In step 2 select &quot;CUSTOM QUERY&quot;. You will have to write a SQL update Statement in the text box. it should look like this:

update yourtablename
set
yourfirstfieldname='::yourfirstfieldname::',
yoursecondfieldname='::yoursecondfieldname::'
where
yourprimarykey=::yourprimarykey::

Now.....the ' (tick) before and after the :: are used for Text data. My primary key is a numeric field so I do not put it inside of ticks.


In step 3 click the MORE OPTIONS button. In the text field that says &quot;No records returned.&quot; Type over with your Success message &quot;Record Edited&quot;. Hit the OK button.

In step 4 chose &quot;TABLE - ONE RECORD PER ROW&quot; and Uncheck all of the check boxes.

In Step 5 Choose &quot;Display All rows Together&quot; and uncheck &quot;Add Search Form&quot;

Click Finish

I hope that this helps....there are a lot of steps and I think I got them all in there...

Tal McMahon

 

[jbento]

I was having problems with this as well, but &quot;bassguy&quot; solution solved my problem. That is working well for me now. Thanks bassguy.

Jerome

 

[bassguy]

Thanks for the Kudos I would suggest an ASP book if you are doing a lot of Database stuff...frontpage works but comes with a lot of overhead (all of the crosschecking and error handling). just my 2 cents worth

Bassguy

 

[jbento]

You're welcome for the kudos. What asp book would you suggest?