DBI update failure

M

MaureenKLM

FP 2002 web wtih Access 2002 DB

I used the DBIW to create an update page - editor page only. Source is a
table in the DB -

When I enter the dbi through a browser and try to update a record, I get the
following error messge:

Database Results Error
Description: Syntax error in UPDATE statement.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

One or more form fields were empty. You should provide default values for
all form fields that are used in the query.

This error message appears even if I change ABSOLUTELY NOTHING and simply
click on the update button. What could cause an error such as this?
 
M

Mark Fitzpatrick

Are any of the values empty? Usually you see this error when you're updating
an entry and not putting something into a field that is not marked as
nullable. If a field is marked nullable it can be completely empty. If it's
not marked nullable and you try and insert a new row or update an existing
one and the field in question is empty, the database will throw this error
since you can't put/update a null value into the field. Check all the fields
to make sure that either they are able to be null or empty, and that the
ones that you need to always have a value are doing validation on the client
to ensure a value is always being entered into that particular box.
 
M

MaureenKLM

Thanks for the assistance. I checked the table properties - all Memo & Text
fields are required = NO and Allow zero length = Yes. I have 3 currancy
fields that are required = no and default value = 0. Yet I continue to get
the "empty field" error message when I try to make any updates - even when
absolutely nothing is changed, the record will not be accepted "as is".

Is there anything else I should be looking for in my table? Is there
another place I should be checking "nullable" status? Anything within the
DBI pages I should check?

Continued THANKS for your assistance!
 
M

MaureenKLM

The Update.asp page created by the DBIW has the following:
I hope it helps resolve the problem I'm having. Thanks again !


<%
fp_sQry="UPDATE tblFamily SET FamFirst = '::FamFirst::' , FamLast =
'::FamLast::' , Street = '::Street::' , City = '::City::' , State =
'::State::' , Zip = '::Zip::' , Home = '::Home::' , Work = '::Work::' , Cell
= '::Cell::' , Email1 = '::Email1::' , Email2 = '::Email2::' , InsInfo =
'::InsInfo::' , Dr = '::Dr::' , PicWeb = '::picWeb::' , PicNews =
'::pic, Stmt = '::Stmt::' , VolName = '::VolName::' , VolContact =
'::VolContact::' , HeadCoach = '::HeadCoach::' , AsstCoach = '::AsstCoach::'
, ClinicCoach = '::ClinicCoach::' , Ref = '::Ref::' , Timer = '::Timer::' ,
Score = '::Score::' , EquiiptMngr = '::EquiiptMngr::' , GymCleanup =
'::GymCleanup::' , FamDonation = '::FamDonation::' , Sponsor = '::Sponsor::'
, Sponsorwebsite = '::Sponsorwebsite::' , Donation = '::Donation::' , AmtDue
= '::AmtDue::' , PdAmt = '::pdAmt::' WHERE (ID = ::ID::)"
fp_sDefault="FamFirst=&FamLast=&Street=&City=&State=&Zip=&Home=&Work=&Cell=&Email1=&Email2=&InsInfo=&Dr=&PicWeb=&PicNews=&Stmt=&VolName=&VolContact=&HeadCoach=&AsstCoach=&ClinicCoach=&Ref=&Timer=&Score=&EquiiptMngr=&GymCleanup=&FamDonation=&Sponsor=&Sponsorwebsite=&Donation=&AmtDue=&PdAmt=&ID=0"
fp_sNoRecords="Record updated in table."
fp_sDataConn="cbreg"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&FamFirst=202&FamLast=202&Street=202&City=202&State=202&Zip=202&Home=202&Work=202&Cell=202&Email1=202&Email2=202&InsInfo=203&Dr=203&PicWeb=202&PicNews=202&Stmt=202&VolName=202&VolContact=202&HeadCoach=202&AsstCoach=202&ClinicCoach=202&Ref=202&Timer=202&Score=202&EquiiptMngr=202&GymCleanup=202&FamDonation=202&Sponsor=202&Sponsorwebsite=202&Donation=6&AmtDue=6&PdAmt=6&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
 
K

Kathleen Anderson [MVP - FrontPage]

So I just ran through the Database Results Wizard on my PC and let it create
the database for me. I found that in the submission_form.asp and edit.asp
pages there is validation which may or may not match what you have defined
in your database - look for the webbot that starts with:

<!--webbot bot="Validation" s-display-name="Name" s-data-type="String"
b-value-required="True"
--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others
 
M

MaureenKLM

Thank You !

I don't have a field called "NAME" so I'm not sure where that field came
from and how it got to be required. I deleted and recreated the DBIW -
checked my edit.asp and submission.asp pages for the webbot validation code
and I don't see any Name field wiht validation = true. Every field has
validation = false yet I'm still getting the same error - it's a huge puzzle
to me. I deleted the dba nd imported a new copy and then recreated the dbiw
and still get the error - any other suggestions on how to resolve this
problem?

Thanks Again!
 
K

Kathleen Anderson [MVP - FrontPage]

The code that I posted was from the DBIW that I ran - it wasn't from your
site. What I was trying to suggest is that you look at the validation
webbots on your submission_form.asp and edit.asp pages and make sure that
none of them have b-value-required="True" when in reality your Access
database did not have it defined as a required field.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others
 
S

Stefan B Rusynko

Does your form (or DBRW) contain all the fields in that query
- and for each field there must be a value or you will get an error in the update statement

Is the ID field an autonumber field?
- if so ID=0 (in fp_sDefault) can not be used because it will always be >0
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| The Update.asp page created by the DBIW has the following:
| I hope it helps resolve the problem I'm having. Thanks again !
|
|
| <%
| fp_sQry="UPDATE tblFamily SET FamFirst = '::FamFirst::' , FamLast =
| '::FamLast::' , Street = '::Street::' , City = '::City::' , State =
| '::State::' , Zip = '::Zip::' , Home = '::Home::' , Work = '::Work::' , Cell
| = '::Cell::' , Email1 = '::Email1::' , Email2 = '::Email2::' , InsInfo =
| '::InsInfo::' , Dr = '::Dr::' , PicWeb = '::picWeb::' , PicNews =
| '::pic, Stmt = '::Stmt::' , VolName = '::VolName::' , VolContact =
| '::VolContact::' , HeadCoach = '::HeadCoach::' , AsstCoach = '::AsstCoach::'
| , ClinicCoach = '::ClinicCoach::' , Ref = '::Ref::' , Timer = '::Timer::' ,
| Score = '::Score::' , EquiiptMngr = '::EquiiptMngr::' , GymCleanup =
| '::GymCleanup::' , FamDonation = '::FamDonation::' , Sponsor = '::Sponsor::'
| , Sponsorwebsite = '::Sponsorwebsite::' , Donation = '::Donation::' , AmtDue
| = '::AmtDue::' , PdAmt = '::pdAmt::' WHERE (ID = ::ID::)"
|
fp_sDefault="FamFirst=&FamLast=&Street=&City=&State=&Zip=&Home=&Work=&Cell=&Email1=&Email2=&InsInfo=&Dr=&PicWeb=&PicNews=&Stmt=&VolName=&VolContact=&HeadCoach=&AsstCoach=&ClinicCoach=&Ref=&Timer=&Score=&EquiiptMngr=&GymCleanup=&FamDonation=&Sponsor=&Sponsorwebsite=&Donation=&AmtDue=&PdAmt=&ID=0"
| fp_sNoRecords="Record updated in table."
| fp_sDataConn="cbreg"
| fp_iMaxRecords=1
| fp_iCommandType=1
| fp_iPageSize=0
| fp_fTableFormat=False
| fp_fMenuFormat=False
| fp_sMenuChoice=""
| fp_sMenuValue=""
|
fp_sColTypes="&ID=3&FamFirst=202&FamLast=202&Street=202&City=202&State=202&Zip=202&Home=202&Work=202&Cell=202&Email1=202&Email2=202&InsInfo=203&Dr=203&PicWeb=202&PicNews=202&Stmt=202&VolName=202&VolContact=202&HeadCoach=202&AsstCoach=202&ClinicCoach=202&Ref=202&Timer=202&Score=202&EquiiptMngr=202&GymCleanup=202&FamDonation=202&Sponsor=202&Sponsorwebsite=202&Donation=6&AmtDue=6&PdAmt=6&"
| fp_iDisplayCols=16
| fp_fCustomQuery=True
| BOTID=0
| fp_iRegion=BOTID
| %>
|
|
| "Kathleen Anderson [MVP - FrontPage]" wrote:
|
| > Can you post the UPDATE SQL statement?
| >
| > --
| >
| > ~ Kathleen Anderson
| > Microsoft MVP - FrontPage
| > Spider Web Woman Designs
| > web: http://www.spiderwebwoman.com/resources/
| > Please reply to the newsgroup for the benefit of others.
| >
| >
| >
| >
| > | > > Thanks for the assistance. I checked the table properties - all Memo &
| > > Text
| > > fields are required = NO and Allow zero length = Yes. I have 3 currancy
| > > fields that are required = no and default value = 0. Yet I continue to
| > > get
| > > the "empty field" error message when I try to make any updates - even when
| > > absolutely nothing is changed, the record will not be accepted "as is".
| > >
| > > Is there anything else I should be looking for in my table? Is there
| > > another place I should be checking "nullable" status? Anything within the
| > > DBI pages I should check?
| > >
| > > Continued THANKS for your assistance!
| > >
| > > "Mark Fitzpatrick" wrote:
| > >
| > >> Are any of the values empty? Usually you see this error when you're
| > >> updating
| > >> an entry and not putting something into a field that is not marked as
| > >> nullable. If a field is marked nullable it can be completely empty. If
| > >> it's
| > >> not marked nullable and you try and insert a new row or update an
| > >> existing
| > >> one and the field in question is empty, the database will throw this
| > >> error
| > >> since you can't put/update a null value into the field. Check all the
| > >> fields
| > >> to make sure that either they are able to be null or empty, and that the
| > >> ones that you need to always have a value are doing validation on the
| > >> client
| > >> to ensure a value is always being entered into that particular box.
| > >>
| > >>
| > >> --
| > >> Hope this helps,
| > >> Mark Fitzpatrick
| > >> Microsoft MVP - FrontPage
| > >>
| > >> | > >> > FP 2002 web wtih Access 2002 DB
| > >> >
| > >> > I used the DBIW to create an update page - editor page only. Source is
| > >> > a
| > >> > table in the DB -
| > >> >
| > >> > When I enter the dbi through a browser and try to update a record, I
| > >> > get
| > >> > the
| > >> > following error messge:
| > >> >
| > >> > Database Results Error
| > >> > Description: Syntax error in UPDATE statement.
| > >> > Number: -2147217900 (0x80040E14)
| > >> > Source: Microsoft JET Database Engine
| > >> >
| > >> > One or more form fields were empty. You should provide default values
| > >> > for
| > >> > all form fields that are used in the query.
| > >> >
| > >> > This error message appears even if I change ABSOLUTELY NOTHING and
| > >> > simply
| > >> > click on the update button. What could cause an error such as this?
| > >> >
| > >> >
| > >>
| > >>
| > >>
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database Connection on Server 9
Database Interface Wizard 3
Password on DBI Submission Form 2
How do I clear a form? 0
DIW Query Source 5
Database Update 16
ASP vs ASP.Net & database 2
DBRW Issue - Syntax Error 11

Top