Data Base Interface Wizard update problem

[Thanasis \(sch\)]

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="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td
bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;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.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include
file="../../../_fpclass/fpdblib.inc"-->
<% 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
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="12627" -->

 

[Kathleen Anderson [MVP - FrontPage]]

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\)]

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]

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\)]

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\)]

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">
<!--webbot bot="PurpleText" PREVIEW="-Important- If you modify this
Database Results region using the Database Results Wizard, then your
Database Editor will no longer work. If you accidentally open the Database
Results Wizard, simply click Cancel to exit without regenerating the
Database Results region." -->

<script Language="JavaScript">
<!--
top.list.location.href = top.list.location.href;
// -->
</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="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td
bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;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.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include
file="../../../_fpclass/fpdblib.inc"-->
<% 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
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="53678" -->

<p><!--webbot bot="PurpleText" PREVIEW="This is the UPDATE query." --></p>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc"
i-groupsize="0" clientside Tag="BODY" startspan preview="&lt;table border=0
width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td
bgcolor=&quot;#FFFF00&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is
the end of a Database Results
region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include
file="../../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="9297" -->

</body>

</html>

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

 

[Thanasis \(sch\)]

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

regards
thanasis

 

[Paul M]

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]

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

 

[Thanasis \(sch\)]

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]

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\)]

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]

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\)]

thanks again paul,

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