|
| |
|
|
pipwax
Posts: 35
Joined: 10/3/2003
Status: offline
 |
Creating A form used to update my Database - 9/25/2004 16:55:09
I have a database set up Here. Now what I would like to to do is create a form for updating my database can anyone help.
|
|
|
|
BeTheBall
Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Creating A form used to update my Database - 9/25/2004 21:54:24
The easiest way to do that is to create the form with the database results wizard. I am making the assumption that you will have some sort of link or form that will carry the ID of the record to your update form. Then, to create the form, open a new page. Click Insert - Database - Results. This launches the database results wizard. In step 1, choose the connection to your database. Step 2, choose your table. In step 3, choose only those fields you want the user to be able to update. For example, you don't want the user trying to change the ID of the record, so exclude it from the values you want to display. Once you select the fields to display, click More Options - Criteria - Add. This is where you determine which record you are going to edit. Since we will pass the ID number in from a link or form, choose ID - Equals - ID and leave the "Use this search form field" checkbox checked. Then, click OK three times followed by "Next" to move to Step 4. In Step 4, choose the formatting option of, "List - One field per item". Then, in the dropdown labeled, "List Options", choose "Text fields". Move to step 5 and choose "Display all records together" and UNCHECK the "Add search form" box. Click finish and save with a .asp extension. Now, since we excluded the field that displays the ID, we must add it as a hidden field so when we perform the actual update, the code knows what record it needs to update. To add the hidden field, right-click the form and choose, "Form Properties", then "Advanced" and the "Add". Under the name, enter "ID" and under the value, enter <%=FP_FieldHTML(fp_rs,"ID")%>. Finally, right-click the form again and again choose "Form Properties". Then click the "Options" button and under "Action", enter the name of a new .asp page whereon we will place code to update the record, something like Update.asp. That will give you your form prepopulated with the data from whatever record's ID is passed to the page.
Now open a new page. Again choose, Insert - Database - Results. Step 1, choose your database connection. Step 2, mark the "Custom query" radio button and click the "Edit" button. Now, this is where you have to insert the query that will update the database. The format is this:
UPDATE TableName SET dbfield1 = '::formfield1::', dbfield2 = '::formfield2::' WHERE ID = ::ID::
When you finish entering your query, click the "Verify query" button. If it verifies, proceed to Step 3. In step 3, click the "More Options" button and replace the "No Records Returned" message with "Update Successful!", or something like that. Then proceed to Step 5 where you should mark the "Display all records together" radio button and unmark the "Add search form" box.
Now, you may wish to modify the form itself to replace text boxes with say a textarea or dropdown, but I would see if you can tackle this first and then edit the form later.
Good luck!
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
dshayden
Posts: 6
Joined: 10/16/2004
Status: offline
|
RE: Creating A form used to update my Database - 10/16/2004 16:24:55
I am trying to follow these directions to add an edit and then later a delete option on a working database. The database is created by a form entry. This site is at http://haydens.brinkster.net and the entry form is "enterdates.asp."
I added a link to the display table, and linked it to a new page "edit.asp." The link successfully passes the parameter of the item for editing and displays the entry to be edited on the edit.asp page.
On the edit page are four fields, and the ID field is "hidden" with the code <%=FP_FieldHTML(fp_rs,"ID")%> in the advanced button for form properties.
The action for the form is update.asp. But when some text is changed and submitted, I get "database results error/ the operation failed." This even though the custom query on the update.asp page
( UPDATE Results SET dates = #::dates::#, dates1 = #::dates1::#, name= '::name::', comments = '::comments::' WHERE ID = ::ID:: ) verifies. I have tried it both with the hatch marks and with the single quote for the two date fields.
Can you tell what I might be doing wrong? I am using FP2002, but Access 2003, if that makes a difference.
Thanks.
< Message edited by dshayden -- 10/16/2004 17:06:10 >
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Creating A form used to update my Database - 10/16/2004 19:42:28
This is a very interesting thread.
I have been trying to create a form that will allow for updating our database using the DRW.
In fact, one Database Result drop down allow for choosing a name.
The next displays any contact entries for that name (ContactDate, ContactForm, Notes).
The third is a form created via the DRW that displays the three editable fields and stores the related record ID as <%=FP_FieldHTML(fp_rs,"ID")%>. This form posts to another page with a Database Result that updates the table. The SQL is:
Update Recruitment_Contacts
Set ContactDate='::ContactDate::', ContactForm='::ContactForm::', Notes='::Notes::'
WHERE (ID = '::ID::')
However, I keep getting an error on the result page:
Database Results Error
Your page contains a query with user input parameters that could not be resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
I looked at the article, but I'm using FrontPage 2002. Do you have an idea what I should do?
Thanks.
Stephen
| |
|
|
|
dshayden
Posts: 6
Joined: 10/16/2004
Status: offline
|
RE: Creating A form used to update my Database - 10/16/2004 23:39:23
Thanks for the quick reply. I put the brackets around name, but still no difference. Any other ideas?
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Creating A form used to update my Database - 10/17/2004 12:58:35
Spooky!
I tried replacing the files - no joy.
I tried copying the s-columnnames and s-columntypes and it worked!
I have two related questions, but I think it's better to start a new thread for them.
Thanks.
Stephen
| |
|
|
|
sgreen0
Posts: 726
From: Long Beach, CA, USA
Status: offline
|
RE: Creating A form used to update my Database - 10/17/2004 18:10:20
As a follow-up, I managed to get all the related Database Results on one page.
The third one (a form) was posting to a separate page (to update the table with the edited fields). The DR on that page returns Update Successful! and has a back button.
I tried having that form post to the same page and that worked, too. But as it updates the page it returns to the "neutral" state. Is there a way I can have the page return to the same record it was on as I was editing?
Is that even slightly clear? I can attach the page if it would help.
Thanks.
Stephen
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 