Updating a Web Database 2

[SSK]

How do I update a web database which was designed using Frontpage 2000 and has an Access compatible database (*.mdb). I want to be able to append the database regularly with new information and also to prune old records.

Thank you.

SSK [sig][/sig]

 

[jbento]

Try this.

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

Tal McMahon helped provide these instructions.

Jerome

 

[SirLars]

thank-you

thank-you

thank-you

 

[jbento]

You are very welcome. It helped me as well

Jerome

 

[Laura2]

I've tried this and it works great until I get to the &quot;submit.asp&quot; page in explorer. It keeps giving me the error of

Database Results Error
Description: [Microsoft] [ODBC Microsoft Access Drive] Too few parameters. Expected
Number: -2147217904 (0x80040E10)

Do you have any idea how to correct this?

Thanks.

Laura.

 

[jbento]

Does all field names match, meaning from the database to the webpages?

 

[Laura2]

Yes, all of the fields in the database have the same field names in my .asp pages. I'm completely at a loss. The first 2 pages work perfectly but it refuses to update the database???

 

[Laura2]

Does it matter what order the fields are in on the submit.asp page? Where you start your SQL statement with
update yourtablename
set
yourfirstfielname= etc, etc,

I have 10 fields to update and one primary field for a total of 11 fields. Does the order in which I put the first 10 fields in this argument matter?

Thanks,
Laura.

 

[jbento]

I have two forms you can look at and look at the code. I created this for my wife's class, so she could put class assignments on the web for students and parents. I have included hyperlinks to a submit.asp, submit1.asp & choose.asp. Go take a look at the code, and try to compare to what you have, and if everything looks ok, then we need to focus on the database, but do this first and let me know what you find out:

http://jerpat.org/submit.asp

http://jerpat.org/submit1.asp

http://jerpat.org/choose.asp

 

[jbento]

Note: Those forms don't work anymore, because I switched to a new hosting company that don't support Microsoft Access Databases, but the scripts are still good.

 

Hi Jerome,

Thank you so much for the files! That was very helpful. I still have the same issue. I noticed that the submit files that you sent didn't have the fp code in them like mine did. I even went as far as to recreate a new database with only that one table in it. Still no luck.

I've decided to try a whole new approach and just save the results to a new database instead of updating the existing one. That way I can just query against the two tables.

Thanks again for all your help.

Laura.

 

Hi Jerome,

Thank you so much for the files! That was very helpful. I still have the same issue. I noticed that the submit files that you sent didn't have the fp code in them like mine did. I even went as far as to recreate a new database with only that one table in it. Still no luck.

I've decided to try a whole new approach and just save the results to a new database instead of updating the existing one. That way I can just query against the two tables.

Thanks again for all your help.

Laura.

 

[artdirectoreric]

In the meantime, if you have Access, you can manually update the database. In FP, double-click the database in the fpdb folder. That will import the db into Access, you can make your changes (don't forget to Compact and Repair Database) and close out of the file, which will automatically update it on the site (which can be a little slow depending on connection speed and server traffic)

 

[Laura2]

Jerome,

Thank you so much for taking the extra time to send those files. I tried to compare them against mine and they pretty much looked the same. Your submit forms didn't include the fp code that I needed but I'm sure mine was set up properly. I even went as far as to create a new database that only housed the one table but that did not correct the problem.

I've decided to go another route and just update a different database alltogether and run a query against the two tables to get my results.

Thanks again for all your help.
Laura.

 

[jbento]

Before you go that route, check out this page:

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

This site really helped me with doing this stuff.

 

[Laura2]

Thanks for the link Jerome. I went through the entire unit of examples. Step by step and trust me it took a long time! Unfortunately, I have a deadline here to get this up and working so I've opted for the other option. After completing the examples, I was still getting the same error message. I'm convinced it has something to do with my ODBC connection in some way. Go figure!

Thanks for your help.

 

[jbento]

You know, I was having this same exact problem, and I'm sure I wrote down somewhere what I had to do. I will look for that tonight, and get back with you.

I am the persistent kind of guy, and I'm determined to help you out with this.

 

[jbento]

This might help:

Too few parameters. Expected 1
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
This error occurs only with Microsoft Access when one of the field names used in a select statement does not exist in the table being queried.

Check that your SQL query is correct and that you have not misspelled any of the field names in your select statement and that the field name exists in the table being queried.

 

[Laura2]

That's the exact error that I'm getting. I did notice something like that on the microsoft site. Unfortunately, that isn't the problem because I've triple checked my field names and they all exist in both the database table and the form. I think my machine is just cursed!

Maybe you can help me out on another issue I'm having with the new procedure I'm using. It has to do with the confirmation form. I've posted a thread under &quot;Confirmation form for Frontpage won't work with my .asp pages!!

Thank you so much for not giving up! You really must enjoy this kind of stuff as I do!

Laura.

 

[jbento]

Laura,
For some reason, I didn't get notified of your response on 8/27, and I am so sorry about that. Unfortunately, I don't even have a clue on the confirmation page problem. For some reason, I can never get the confirmation wizard to work within Frontpage. Try going to this Frontpage Forum to get an answer:

http://www.frontpagedatabase.com/Forum/topic.asp?TOPIC_ID=155.

It looks like it can be of some help to you, and yes I LOVE THIS STUFF AND I LOVE HELPING PEOPLE

If you need anymore assistance on anything please let me know.

Have a great weekend!!!