Solved

Update SQL DB Using Database Results Wizard

Posted on 2004-03-26
13
359 Views
Last Modified: 2013-12-24
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).

Thanks

Craig

0
Comment
Question by:RHLimited
  • 7
  • 4
13 Comments
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
Hi,

Try this format

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

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

Regards
hhammash
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
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?
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
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?

Regards
hhammash
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
hhammash,

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.

Regards

Craig
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
Increased points as this is driving me nuts.
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
Increased points as this is driving me nuts.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 14

Expert Comment

by:hhammash
Comment Utility
Hi,

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.

Regards
hhammash
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
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!

UPDATE bios
SET FName='::FName::', LName='::LName::', Photo='::Photo::', URL='::URL::', Email='::Email::', Description= '::Description::', Accomplishments='::Accomplishments::', Events='::Events::', Topics='::Topics::'
WHERE ID= ::ID::

· 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">
 
Craig
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
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.

Regards
hhammash
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
Morning,

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

Cheers

Craig
0
 
LVL 1

Author Comment

by:RHLimited
Comment Utility
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:

    s-columnnames="Expr1000,EmployeeID,FirstName"

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

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

    s-columntypes="3,3,202"

Thanks
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
Closed, 400 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now