Solved

Update SQL DB Using Database Results Wizard

Posted on 2004-03-26
13
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
13 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 10697277
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
ID: 10702442
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
ID: 10728281
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 1

Author Comment

by:RHLimited
ID: 10730262
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
ID: 10730274
Increased points as this is driving me nuts.
0
 
LVL 1

Author Comment

by:RHLimited
ID: 10730276
Increased points as this is driving me nuts.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 10731759
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
ID: 10731869
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
ID: 10735363
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
ID: 10739360
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
ID: 10773087
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
ID: 10802063
Closed, 400 points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

717 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