Database Update

  • Thread starter Thread starter Trillian
  • Start date Start date
T

Trillian

(I put an identical post in the Programming Newsgroup)

Using the Database Interface Wizard I created the
add/edit/delete pages for my Access web in Frontpage. The
ADD option seems to work fine. I haven't tried deleting.
But - when I've tried to submit an edited record the
upload (?) takes minutes - I've actually not let it
finish. I get the dreaded hourglass mouse pointer, no
errors, and the message in the status bar says it's
opening the "Update" page. Shouldn't this be a fairly
quick process even with a large db?
 
-----Original Message-----
(I put an identical post in the Programming Newsgroup)
Click to expand...

Poor form, but OK this time.
Using the Database Interface Wizard I created the
add/edit/delete pages for my Access web in Frontpage. The
ADD option seems to work fine. I haven't tried deleting.
But - when I've tried to submit an edited record the
upload (?) takes minutes - I've actually not let it
finish. I get the dreaded hourglass mouse pointer, no
errors, and the message in the status bar says it's
opening the "Update" page. Shouldn't this be a fairly
quick process even with a large db?
Click to expand...

Define "large".

In any event, though, a response time of several minutes
seems excessive. If you wait long enough, does the update
finally complete? If so, you might have a really bit
dtabase there.

It it always fails, you may have a poorly named field
that's causing the update to fail. Usually this would
produce an error message but it could also be silent. The
usual suspects are:

- Access date values not surrounded by pound signs.
- Field names of date or timestamp.
- Field names that contain spaces or special characters
- String values that contain apostrophes.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
I have no "badly" named fields, I did check that
possibility even without receiving an error.

I have two data fields that are text but that I format as
hyperlinks when displayed - they contain double quotes
like so:

<img border="0" src="images/crown5.gif" align="left"
width="39" height="59">

The mdb is a little over 2 MB and will get larger (that's
probably small, I know).

I did run a delete on a test record and the process was
instaneous.

I'm open to suggestions (and I won't post double again -
thanks!)
 
Trillian said:
I have no "badly" named fields, I did check that
possibility even without receiving an error.

I have two data fields that are text but that I format as
hyperlinks when displayed - they contain double quotes
like so:

<img border="0" src="images/crown5.gif" align="left"
width="39" height="59">

The mdb is a little over 2 MB and will get larger (that's
probably small, I know).

I did run a delete on a test record and the process was
instaneous.

I'm open to suggestions (and I won't post double again -
thanks!)
Click to expand...

By "String values that contain apostrophes" I meant values entered
into HTML forms. If you enter a surname like Smith, the SQL statement
ends up with a literal like 'Smith', no problem. But if you enter
O'Mally, you end up with 'O'Mally'. SQL interprets this as the literal
'O' followed by the syntactically incorrect Mally', problem.

In any event:
1. Open your update.asp page.
2. Right-click the database results region, then choose Database
Results
Properties.
3. Click Next, Custom Query, and Edit.
4. Select the contents of the SQL statement box, then copy it to the
clipboard (i.e. press Ctrl+C or right-click and choose Copy.)
5. Post the SQL statement to this discussion thread.
6. In FrontPage, Press Cancel and Cancel to close the Database Results
Wizard.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
Here's the Custom Query:

UPDATE TFQFILMS SET FILMTITLE = '::FILMTITLE::' ,
YEARRELEASE = ::YEARRELEASE:: , YEARRERELEASE
= ::YEARRERELEASE:: , LANGUAGE = '::LANGUAGE::' , IMDBCODE
= '::IMDBCODE::' , IMDBLINK = '::IMDBLINK::' , REVIEW
= '::REVIEW::' , SUMMARYREVIEW = '::SUMMARYREVIEW::' ,
FQRATING = ::FQRATING:: , FQCROWN = '::FQCROWN::' , TOPTEN
= ::TOPTEN:: , TOPTENTEXT = '::TOPTENTEXT::' , AWARDTEXT
= '::AWARDTEXT::' , OSCARPICTURE = ::OSCARPICTURE:: ,
OSCARWINNER = ::OSCARWINNER:: , OSCARWINNERTEXT
= '::OSCARWINNERTEXT::' , OSCARNOMINEE
= ::OSCARNOMINEE:: , OSCARNOMINEETEXT
= '::OSCARNOMINEETEXT::' , AWARD = ::AWARD:: ,
AWARDDESCRIPTION = '::AWARDDESCRIPTION::' WHERE (FILMID
= ::FILMID::)
 
Trillian said:
Here's the Custom Query:

UPDATE TFQFILMS SET FILMTITLE = '::FILMTITLE::' ,
YEARRELEASE = ::YEARRELEASE:: , YEARRERELEASE
= ::YEARRERELEASE:: , LANGUAGE = '::LANGUAGE::' , IMDBCODE
= '::IMDBCODE::' , IMDBLINK = '::IMDBLINK::' , REVIEW
= '::REVIEW::' , SUMMARYREVIEW = '::SUMMARYREVIEW::' ,
FQRATING = ::FQRATING:: , FQCROWN = '::FQCROWN::' , TOPTEN
= ::TOPTEN:: , TOPTENTEXT = '::TOPTENTEXT::' , AWARDTEXT
= '::AWARDTEXT::' , OSCARPICTURE = ::OSCARPICTURE:: ,
OSCARWINNER = ::OSCARWINNER:: , OSCARWINNERTEXT
= '::OSCARWINNERTEXT::' , OSCARNOMINEE
= ::OSCARNOMINEE:: , OSCARNOMINEETEXT
= '::OSCARNOMINEETEXT::' , AWARD = ::AWARD:: ,
AWARDDESCRIPTION = '::AWARDDESCRIPTION::' WHERE (FILMID
= ::FILMID::)
Click to expand...

Well, after some cosmetic formatting, this SQL statement work out to:

UPDATE TFQFILMS SET FILMTITLE = '::FILMTITLE::' ,
YEARRELEASE = ::YEARRELEASE:: ,
YEARRERELEASE = ::YEARRERELEASE:: ,
LANGUAGE = '::LANGUAGE::' ,
IMDBCODE = '::IMDBCODE::' ,
IMDBLINK = '::IMDBLINK::' ,
REVIEW = '::REVIEW::' ,
SUMMARYREVIEW = '::SUMMARYREVIEW::' ,
FQRATING = ::FQRATING:: ,
FQCROWN = '::FQCROWN::' ,
TOPTEN = ::TOPTEN:: ,
TOPTENTEXT = '::TOPTENTEXT::' ,
AWARDTEXT = '::AWARDTEXT::' ,
OSCARPICTURE = ::OSCARPICTURE:: ,
OSCARWINNER = ::OSCARWINNER:: ,
OSCARWINNERTEXT = '::OSCARWINNERTEXT::' ,
OSCARNOMINEE = ::OSCARNOMINEE:: ,
OSCARNOMINEETEXT = '::OSCARNOMINEETEXT::' ,
AWARD = ::AWARD:: ,
AWARDDESCRIPTION = '::AWARDDESCRIPTION::'
WHERE (FILMID = ::FILMID::)

and this looks OK. You're filling the HTML form fields for all the
numeric fields, right? These are the ones that don't have apostrophes
around the values, such as YEARRELEASE, YEARRERELEASE, FQRATING, and
so forth.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
Okay, problem solved.
I enclosed all fields in brackets like: [FILMID]=::FILMID::
and they update correctly. The db was created in Access
and supposedly this is a FrontPage-created db problem, but
it works, so I'll do it. And, I added the below to the
end of the update.asp page and the process now "finishes".
<%
response.Redirect("list.asp")
%>

Thanks for your assistance.
 
Back
Top