Update SQL DB Using Database Results Wizard

Hi there,

I am trying to update a sql table using the database results wizard but I can't get it to play ball. Shouldn't this statement work

UPDATE LoggedCalls
SET  Resolution='::Resolution::'
WHERE CallNumber=::CallNumber::

Where LoggedCalls is the name of my table, resolution is the field I want to update and CallNumber is the unique primary key reference (i.e. like ID).



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Try this format

UPDATE LoggedCalls SET  Resolution='::Resolution::' WHERE (CallNumber=::CallNumber::);

This should work if Resolution field in a text and CallNumber is Numeric.

RHLimitedAuthor Commented:
That doesn't work either. Checked DB field properties and can confirm that Resolution is a text field and CallNumber is a numeric field. Is there a log file somewhere with more information on why this is failing?
Hi Craig,

Are you getting any error?

The SQL statement is correct.

Are you sure that there is a write permission to the database?

Is the Update query on the same page of the form or are you posting the form to the page with the update query?

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

RHLimitedAuthor Commented:

I am using sa login to write to the database ( I know I shouldn't but this is a development environment on my interanl LAN). I know that writing to the DB works as I have a form that succesfully adds records to this DB. I just can't update existing records. I am posting the form to a page that contains the update query. The error I receive is:

Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.

In an effort to resolve this I tried using an access database instead and followed MS KB article 240090 to the letter and received the same error. Obviously I'd rather use a SQL DB and performed this operetaion for testing purposes only.

Originally I followed the tutorial located at this address http://www.sitebuilder.ws/frontpage/tutorials/update.asp but using a SQL DB instaed of access. Check it out and you'll see how I'm trying to perform this operation.


RHLimitedAuthor Commented:
Increased points as this is driving me nuts.
RHLimitedAuthor Commented:
Increased points as this is driving me nuts.

The link that you gave me is not working.

I am getting "Page not found"

Anyway,  updating should be the same whether in Access or SQL.

Do you have a TimeStamp field?   Sometimes the timestamp field that is created by the DRW causes problems in updating.

RHLimitedAuthor Commented:
I do have a timestamp field mapped to sql field DateLogged with a data type of Date\Time length of 8. Just tried that URL and it's ok. Anyhow here's what that page says:-

speakers.asp which displays the records will contain a link to update.asp update.asp is the form will be used to modify the record from the database update_records.asp is a script containing a SQL statement that will perform the update records in speakers.mdb will then updated
1) Create a page called update.asp.

2) Insert a form in update.asp. The form will contain all of the fields that are in the database that will need updating, you will need to add the ID field which was not present in the first form. Make sure to remove the RESET button!

The ID field is generated as an auto number by Access, we will use it as the unique identifier to update the database. Make sure it is set as the Primary Key in Access.

Hint: Copy the form that was built earlier to save time. We may need to add additional fields. It never fails that the client always wants changes throughout the project.

Note: Because this page relies on the unique identifier of the ID field, modification of that could cause disastrous results. Coming soon, learn how hidden fields and response.write can solve the problem.

Database Field Data Type  Form Field  Form Object
ID auto number ID One Line Text
FName text FName One Line Text
LName text LName One Line Text
Photo text Photo One Line Text
URL text URL One Line Text
Email text Email One Line Text
Description memo Description Scrolling Text
Accomplishments memo Accomplishments Scrolling Text
Events memo Events Scrolling Text
Topics memo Topics Scrolling Text
3) "Request the data" from the database. Insert the following code into each text box <%=request ("DataField")%> of the form in update.asp by double clicking the text box, click OK to save changes. Use the table above as a guide.
4) Send the form results to update_record.asp. (We will create update_record.asp next.) Go to Form Properties and choose the Send to other option. Click on the Options button and enter update_records.asp and post. Save changes.
4) Create update_records.asp and save. We will come back to this later.
5) Create a hyperlink in the speakers.asp. This link will link to update.asp.
· Right click the <<FName>> field (actually, you can choose any field you like) and insert hyperlink. Don't close the Edit hyperlink dialog box yet!

6) Insert parameters to reflect the text boxes of update.asp. After you select update.asp, click the parameters button. Under the Query string section, click the Add button and choose the form fields from the our hyperlink with parameters should look like this.

7) Test the hyperlink. View the speakers.asp in a browser and click the hyperlink. It should bring up the update.asp page with the record of the hyperlink that you selected, otherwise go through the steps in this tutorial again closely.
(Don't click the submit button yet, because update_records.asp is not built yet.)
8) Create update_records.asp.

9) Insert the Database Results Wizard (DRW). On the menu, Insert -> Database -> Results

10) Follow the Wizard.
· Step 1 - Use the database connection that is being used for speakers.asp
· Step 2 - Choose the Custom Query option and click the Edit button (use the graphic and sql statement below as a guide), click the Verify button, although this is not 100% accurate it will points out some blatant errors.

Note: When using numbers in the data fields, use ::number_field:: in place of '::number_field::'

Cut and paste this code to save time, this code has been tested and WORKS!

SET FName='::FName::', LName='::LName::', Photo='::Photo::', URL='::URL::', Email='::Email::', Description= '::Description::', Accomplishments='::Accomplishments::', Events='::Events::', Topics='::Topics::'

· Step 3 - Click the More Options button to add a user friendly message like Record Updated.
· Step 4 - Accept the defaults and click next.
· Step 5 - Choose Display all records together and make sure the Add search form is NOT selected.

11) Redirect update_records.asp to admin.asp. This page was created in an earlier tutorial. This is done by inserting the following meta tags between the <head></head> tags of the update_records.asp.
<meta http-equiv="refresh" content="1;URL=admin.asp">
Hi Craig,

Make a copy of your table to back up the data and the structure.  Then remove the Timestamp field from the original table and try to update.

I know that timestamp field create promblems with updating.  I always split it into date and time fields.  Without using the date and time as field names since they are reserver words.

Please try that,  if it does not work we'll look for the problem.  Now we need to eliminate one doubt after another to find the problem.

RHLimitedAuthor Commented:

Removed time field from my DB and all relevant mappings in my forms but still have the same problem.


RHLimitedAuthor Commented:
Found solution.

On the form that updates the database record there was missing code. To work around this problem follow these instructions:

The workaround is to manually update the s-columnnames and s-columntypes values in the DatabaseRegionStart webbot code.

To do so, us the following steps:

1. Create a new ‘default’ Database Results Wizard page with the same table as in the page giving the error.

2. Switch to HTML view.

3. Locate the following section of code:

       <!--webbot bot="DatabaseRegionStart"

4. Copy the following text of s-columnname and s-columntype [example given below].

s-columnnames="Expr1000,EmployeeID,FirstName" s-columntypes="3,3,202"

5. Open the page that is returning the error in FrontPage 2003.

6. Switch to the HTML view and locate the following section of code:

    <!--webbot bot="DatabaseRegionStart"

7. Update the s-columnnames value to include all field names, including derived

names for fields from aggregate functions like COUNT, etc.  For example:


8. Update the s-columntypes value to include all field types, including derived

types from aggregate functions like COUNT, etc.  For example:


Closed, 400 points refunded.
Community Support Moderator
Experts Exchange

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.