Great got things working except that Step 1's listing is too large to be pratical. I created 4 drop down boxes which help drill down to the specific listing needed. I have all of that working, but I get the error "Database Results Wizard Error The operation failed. If this continues, please contact your server administrator." until all of the drop down boxes have been selected. I am assuming that the Database Results area is attempting to update each time one of the drop down boxes are selected and all of the Parameters are not exstablished yet. Is there a way to force the Database Results area to wait until all of the boxes have a value in it then automatically update? I suppose that I could put in on another page, but I wanted to give the user the ability to quickly change one of the drop down boxes if they did not see the results they needed to edit.
Main Topics
Browse All Topics
by: wcameronPosted on 2005-11-21 at 10:44:08ID: 15335999
The process takes three steps. The first is to create a pick list to list multiple records so that you can select one to be edited. We'll show you how to create this pick list and then link to a second page called ProductEdit.asp. This page will contain a form that will allow you to make changes to your record. This page does not actually make the changes though. It sends your changes to a third page called ProductUpdate.asp. This page contains the code that will update the appropriate record in the database.
So let's get started
Step 1 - Select the Record
If you want to edit a record, you need to have a way to select a single record.
Insert a Database Results Wizard that will list all of the products in your catalog. Then add an additional column with Edit Record as the heading.
Now in the column, beside the product name field, enter the word Edit. Highlight it and select Insert Hyperlink. Point the link to ProductEdit.asp and select Parameter. Insert the parameter ProductID = ProductID. Now clicking on this link will take you to the ProductEdit form with the data for this value already loaded.
Step 2 - Create the Database Change Form
On a blank page, insert a form. Now, select Insert-Database-Results:
Select the Northwind connection and click next.
Select Products as the Record Source and click next:
On this screen, you can edit the list of fields if you wish. In this case, we'll use all the fields. Click the More Options button:
Now click on the Criteria button:
When the criteria screen opens, click add:
Since ProductID is our key, select ProductID as the field name. The screen should look like the above graphic. Click OK:
The criteria screen now reflects the fact that the form will need to be passed the ProductID parameter before loading. This will tell it which records data to populate the field with. Click OK and then Ordering in the More Options screen. Click OK in the More Options screen and Next to take you to Step 4 of the DRW.
Be sure to select List - one field per item from the formatting options and Text fields from the List options. Deselect the horizontal separator option.
Click Next.
Select Display all records together and then finish.
The form now has text fields that when passed the correct parameter will be populated with the data currently stored in the database record.
Dealing with the ProductID Field
There are two possible ways to deal with the ProductID Field
Leave the ProductID field as it is. I usually avoid this option because the ProductID field is usually just an autonumber field and as such is not an editable field anyway. If isn't editable, than your users really don't need to see it.
The preferred option. Delete the row that includes the ProductID field. If you DO delete it, you will need to send the value to the ProductUpdate.asp page as a hidden value.
Once you've created the form, you need to send it to the page ProductUpdate.asp. Open the form properties and Select Send to other.
Click the options button and under Action type ProductUpdate.asp. This tells the form to send the changes to the ProductUpdate.asp which we'll create in the next section.
One more thing though. If, as I advised above, you deleted the ProductID row from the Product Edit form, than you will need to passed the ProductID field as a hidden value to the ProductUpdate.asp page.
Remember, this next step is ONLY necessary if you deleted the ProductID row from the Product Edit form as was suggested above.
In the Form Properties Screen click Advanced to open the Advanced Form Properties. Now Click the Add Button so that we can add the ProductID field as a hidden variable. Type ProductID into the Name box and into the Value box type:
<%=Request("ProductID")%>
It should look like the following image.
Click OK and the advanced properties window will now show the hidden field. Click OK twice to close out of the Form Properties windows.
Save your file. You're now ready for the final step.
Step 3 - Create ProductUpdate.asp
The final step will take the form changes and update the actual database record.
Open a blank page. Now we'll create a Database Results Wizard (DRW) that will update a record in the current database.
Select the Northwind connection from the list of database connections and click "Next".
Select Custom Query and then Edit. Type the following into the Edit window. You must type it exactly as it appears below:
UPDATE Products
SET
ProductName='::ProductName
SupplierID='::SupplierID::
CategoryID='::CategoryID::
QuantityPerUnit='::Quantit
UnitPrice='::UnitPrice::',
UnitsInStock='::UnitsInSto
UnitsOnOrder='::UnitsOnOrd
ReorderLevel='::ReorderLev
WHERE ProductID=::ProductID::
Don't forget that the end of the last field name (in this case ReorderLevel) you DO NOT put a comma at the end of the line. Also, be sure there is no comma on the final line with the WHERE statement.
Here are a few rules about formatting the various types of fields so that they update correctly.
Field Type Formatting
Text '::TextFieldName::'
Number ::NumberFieldName::
Dates '::DateFieldName::'
Boolean* ::BooleanFieldName::
*Boolean fields are those fields that use a yes/no true/false or on/off value. They include most radio and check boxes.
For more information on the correct SQL for various field types and situations, check out this Microsoft Support Document:
http://support.microsoft.c
As you can see, different types of field need different formatting. Be sure to match your format with the correct data type.
Once you have typed in the custom SQL code, click the "Verify Query" button to make sure the code can be verified. This will help ensure that you have typed the code correctly and that you have not misspelled your field names.
If you get a message indicating that it cannot verify the query, delete all the field values except for the first one. See if it can be verified. Then add the second field. Add each field one by one until you get the error message, this will tell you where the problem lies. Once all of your SQL has been correctly entered, you should be able to verify the query.
Click OK, and then More Options
Whenever using an edit script, it's critical to have default values entered into every field. They don't need to be relevant, but your form needs data to input if you leave a field blank. The update script cannot deal with blank data.
Always enter a default value to prevent getting error messages in your browser. If You DO get an error, the message may resemble the following.
One or more form fields were empty. You should provide default values for all form fields that are used in the query.
or even more frightening:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers
The above message simply means that the DRW expected to receive a parameter, but did not get one. By entering a default, you will always make sure it gets a parameter, even if it does not correspond to a record in the database.
In the Defaults screen, double-click on Product Name to open the Default Value screen.
For each field, type a default value. For the Product Name Field, input None. Now enter a value for each field in the form.
If you have a date field, you need to use some FrontPage Magic. For the default date I use an irrelevant date like 01-01-1950. This must be a date that will not be used in any of your records. This means that in records for which no date is entered, the date 01-01-1950 will be stored in your database.
How do you keep that date from showing up in your FrontPage. You do that using the iif statement as described in this tutorial.
In this case, in a blank column in your query use the following syntax:
Date: IIf(IsNull([DateField]) Or [DateField]=#1/1/1950#,””,
Assuming the date field in your form is called DateField, then this will take that data and create a new field called Date. If the date field is null OR if the date is equal to 1/1/1950 then it will simply display nothing (signified by two double brackets side by side).
This should solve your problem. Don’t forget to include the date value in # rather then “.
Click OK to return to the More Options screen and OK again to return to Step 3 of the DRW. Click Next to move to Step 4. Select List - one field per item and paragraphs as the list option.
Click next. In Step 5 select Display all records together and deselect Add Search Form. Finally click Finish.
Finally, click finish and you'll see a DRW that seems to contain nothing. Do not delete it. It is ready to go. Save this page as ProductUpdate.asp.
You should now be ready to go - give it a try.