Data Base Interface Wizard update problem

Thanasis \(sch\) · Jan 13, 2005

Hi to everyone,

i use FP2003 DIW feature to manage my database (add,delete,update).

My access database has the following fields:

article_id(autonumber)
article_title(text)
article_date(date/time) with default value now()
article_body(memo)
link1(hyperlink)
link2(hyperlink)
image(text)

Everything(i.e add,delete) works fine except for update operation.

When i'm trying to update my database i get the following error message:

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.

I provide below the FP2003 generated code inside update.asp

Thanks in advance
thanasis

<!--webbot bot="DatabaseRegionStart"
s-columnnames="article_id,article_date,article_title,artcile_body,link1,link2,image"
s-columntypes="3,135,202,203,203,203,202" s-dataconnection="news"
b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue
b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE"
b-listlabels="FALSE" b-listseparator="FALSE" i-ListFormat="0"
b-makeform="FALSE" s-RecordSource s-displaycolumns s-criteria s-order
s-sql="UPDATE news SET article_date = '::article_date::' , article_title =
'::article_title::' , artcile_body = '::artcile_body::' , link1 =
'::link1::' , link2 = '::link2::' , image = '::image::' WHERE (article_id =
::article_id:

" b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="article_date=&article_title=&artcile_body=&link1=&link2=&image=&article_id=0"
s-NoRecordsFound="Record updated in table." i-MaxRecords="1" i-GroupSize="0"
u-dblib="../../../_fpclass/fpdblib.inc"
u-dbrgn1="../../../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" Tag="BODY" startspan BOTID="0"
preview="<table border=0 width="100%"><tr><td
bgcolor="#FFFF00"><font color="#000000">This is
the start of a Database Results region. The page must be fetched from a web
server with a web browser to display correctly; the current web is stored on
your local disk or
network.</font></td></tr></table>" -->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE news SET article_date = '::article_date::' , article_title =
'::article_title::' , artcile_body = '::artcile_body::' , link1 =
'::link1::' , link2 = '::link2::' , image = '::image::' WHERE (article_id =
::article_id:

"
fp_sDefault="article_date=&article_title=&artcile_body=&link1=&link2=&image=&article_id=0"
fp_sNoRecords="Record updated in table."
fp_sDataConn="news"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&article_id=3&article_date=135&article_title=202&artcile_body=203&link1=203&link2=203&image=202&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

Kathleen Anderson [MVP - FrontPage] · Jan 13, 2005

You have a type in your SQL: artcile_body = '::artcile_body::'

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

Thanasis \(sch\) · Jan 13, 2005

hi kathlenn,

thanks for reply. I didn't generate the code. DIW has generated it.

So i guess the problem might be somewhere else.

thanks

Paul M · Jan 13, 2005

Hi Thanasis
I had the same problem. It seems the update won't update empty database
fields. I got over it by:
Opening the database in Access(double click the database in the fpdb folder.
open the table where the data is stored.
In Access click the view button on the left and in the default value pane at
the bottom type something like "No Details" (don't forget the " marks)and
put a 0 in the number fields.
Do this for all the fields of your database
By doing this a value is added to the field as a default until it is
populated by an entry from your form.
Hope this helps
Best wishes
Paul M

Thanasis \(sch\) · Jan 13, 2005

hi thanks a lot,
i tried it but doesn't work.Maybe the autonumber field causes the problem.
I can't provide an initial value for it.
DIW causes many problems.

thanks again

Thanasis \(sch\) · Jan 13, 2005

Hi kathleen,

i changed my access database which now has the following fields:

id(autonumber)
title(text) --> I have set default value to "N/A"
imerominia(date/time) --> I have set default value to now()
body(memo)--> I have set default value to "N/A"
link(hyperlink)--> I have set default value to N/A
image(text)--> I have set default value to "N/A"

i have also tried without providing default values but i was getting the
same error message when updating data base.

I provide below the FP2003 generated code inside update.asp

thanks in advance
thanasis

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a FrontPage Component. Do not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>news -- Updated Record</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body bgcolor="#FFFFFF">


<script Language="JavaScript">

</script>

<!--webbot bot="DatabaseRegionStart"
s-columnnames="id,imerominia,title,body,link,image"
s-columntypes="3,135,202,203,203,202" s-dataconnection="news"
b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue
b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE"
b-listlabels="FALSE" b-listseparator="FALSE" i-ListFormat="0"
b-makeform="FALSE" s-RecordSource s-displaycolumns s-criteria s-order
s-sql="UPDATE news SET imerominia = '::imerominia::' , title = '::title::' ,
body = '::body::' , link = '::link::' , image = '::image::' WHERE (id =
::id:

" b-procedure="FALSE" clientside SuggestedExt="asp"
s-DefaultFields="imerominia=&title=&body=&link=&image=&id=0"
s-NoRecordsFound="Record updated in table." i-MaxRecords="1" i-GroupSize="0"
u-dblib="../../../_fpclass/fpdblib.inc"
u-dbrgn1="../../../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" Tag="BODY" startspan BOTID="0"
preview="<table border=0 width="100%"><tr><td
bgcolor="#FFFF00"><font color="#000000">This is
the start of a Database Results region. The page must be fetched from a web
server with a web browser to display correctly; the current web is stored on
your local disk or
network.</font></td></tr></table>" -->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="UPDATE news SET imerominia = '::imerominia::' , title = '::title::'
, body = '::body::' , link = '::link::' , image = '::image::' WHERE (id =
::id:

"
fp_sDefault="imerominia=&title=&body=&link=&image=&id=0"
fp_sNoRecords="Record updated in table."
fp_sDataConn="news"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&id=3&imerominia=135&title=202&body=203&link=203&image=202&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



<p></p>



</body>

</html>

=====================================================================

Thanasis \(sch\) · Jan 13, 2005

i have also tries microsoft article 287147, but i didn't find a timestamp
field inside my html code

regards
thanasis

Paul M · Jan 13, 2005

Hi
The auto number field should generate a number which will automaticaly enter
avalue into the field, so it is never empty.
Are you still getting the error?
I also had a problem with the time /date field which gave me a syntax error
I removed this field altogether from the access database then deleated the
DIW and re created the DIW it again this time without the time/date field
which solved the problem.
Something to try
Best wishes
paul M

Paul M · Jan 13, 2005

Correction : by time/date I mean timestamp field
paul m

Thanasis \(sch\) · Jan 14, 2005

Hi,
thanks again.
Excuse me what do you mean by timestamp field?In my access database i need a
field of type date/time in order to store the date
of an article?If i remove this field how could i store the date?Must i add a
separate field with type timestamp?
I think that access doesn't have such a type(timestamp) !!!It only has a
type called date/time
Could you please explain?

Thanasis

Paul M · Jan 14, 2005

Hi Thanasis
Did you let frontpage generate the database from a form using the create
database option? If you did it creates an additional field called
Timestamp.I had a problem once with a syntax error it seems that for some
reason in this instance it wouldnt accept timestamp because it is a SQL
reserved word, but it works in my other databases Strange?
If you are trying to use the DIW on a database created within Access there
may be some conflict somewhere.
You could try creating the database from scratch using frontpage.
Create a form in frontpage and add the fields you intend to use for the
database.
Right click the form then in the form properties click send to database then
options and click create database. a database will be created in the fpdb
folder using the fields of the form.
you can then use the DIW to connect to it as usual
hope this helps
Paul M

Thanasis \(sch\) · Jan 14, 2005

Hi Paul,
thanks again for reply. I didn't follow the steps you described.For this
reason i didn't see any timestamp field.
I describe to you the steps i have followed.

1) I created a web site (static) without any database(db)

2) I created an access db outside my site (say news.mdb)

3)I open my site with FP2003 then i click on File-->New-->I choose more web
site templates (on the right vertical pane)
-->i choose DataBase Interface Wizard and i set the check box ADD TO CURRENT
WEB SITE.

4) i follow the wizards' steps. The wizard imports the external file
news.mdb and puts it inside the fpdb folder and creates the necessary files
and folders in order to manage my db (list,add,delete and update records)

5)When the wizard will have finished it's steps it will have created inside
my web site the following folders and files:

NEWS_INTERFACE
|-------NEWS
|-------EDITOR
| |-------confirm.asp
| |-------database_editor.asp
| |-------database_editor.asp
| |-------delete.asp
| |-------detail.asp
| |-------edit.asp
| |-------list.asp
| |-------new.asp
| |-------submission_form.asp
| |-------update.asp
|
|-------results_page.asp

So inside the update.asp i don't see any timestamp field.
I think the above procedure is convenient cause the wizard creates all the
required forms automatically.
But it doesn't work

Thanks again
thanasis

Paul M · Jan 14, 2005

Hi Thanasis
Unfortunately microsoft are not always perfect and can have anoying quirks.
It might be worth creating the database within front page from a form. then
you know that the database has been created with the supporting code that
the DIW understands. Access is a complicated DB tool which might be
stretching the cababilities of the frontpage DIW especially when some of
the advanced Access functions are used.
best wishes
Paul M

Thanasis \(sch\) · Jan 15, 2005

thanks again paul,

the problem has been solved. The Database manager works fine even wih the
date field.

I am trying to understand this code...	3	Jul 26, 2008
any ideas why I can update one table in my database but not the other?	2	Sep 22, 2006
Select statement aanpassen in asp gedeelte middels variable	1	Mar 30, 2007
@ my wits end	1	Sep 12, 2006
All values item in a dropdown box for numeric field query	0	Aug 24, 2006
SQL DB Results Wizard Error	1	Apr 9, 2005
SELECT DISTINCT Query not working	4	Oct 24, 2004
FP Database help needed...	6	Oct 18, 2005

Data Base Interface Wizard update problem

Thanasis \(sch\)

Kathleen Anderson [MVP - FrontPage]

Thanasis \(sch\)

Paul M

Thanasis \(sch\)

Thanasis \(sch\)

Thanasis \(sch\)

Paul M

Paul M

Thanasis \(sch\)

Paul M

Thanasis \(sch\)

Paul M

Thanasis \(sch\)

Ask a Question

Similar Threads