UPDATE database problems

A

Andy Watts

Hi,
I'm having problems updating a database - i'm bringing the data up fine in a
form but am getting in a right pickle trying to update the information.
I've tried so many combinations I'm not sure which I haven't tried. I've
also looked up many sources on the web and even bought a book (Inside Out)
and still can't find a definitive answer. I'm now also confused whether I
should have a ' round text or digits in the SQL statement, oh dear :-s.

TIA
Andy Watts

=======================
Firstly, here is the form code:

<!--webbot bot="DatabaseRegionStart"
s-columnnames="ID,Product,Description,SUPPLIER,STOCK,price"
s-columntypes="3,202,202,202,5,5" s-dataconnection="Database1"
b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="ID"
s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="7" b-makeform="FALSE" s-recordsource="oldstock"
s-displaycolumns="ID,Product,Description,SUPPLIER,STOCK,price"
s-criteria="{ID} EQ {ID} +" s-order s-sql="SELECT * FROM oldstock WHERE (ID
= ::ID::)" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="ID=ID" s-norecordsfound="No records returned."
i-maxrecords="256" i-groupsize="0" botid="0"
u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" 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;"
b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b-DataGridFormat="FALSE"
b-DGridAlternate="TRUE" sa-CritTypes="3" b-WasTableFormat="FALSE"
startspan --><!--#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="SELECT * FROM oldstock WHERE (ID = ::ID::)"
fp_sDefault="ID=ID"
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_sColTypes="&ID=3&Product=202&Description=202&SUPPLIER=202&STOCK=5&price=5
&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="54633" --><form
METHOD="POST" action="updatesql.asp">
<!--webbot bot="PurpleText" preview="Set this form's properties so it
submits user input to the appropriate page." --><table BORDER="0">
<tr>
<td><b>ID:</b></td>
<td>
<input TYPE="TEXT" NAME="ID" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"ID")%>"></td>
</tr>
<tr>
<td><b>Product:</b></td>
<td>
<input TYPE="TEXT" NAME="Product" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Product")%>"></td>
</tr>
<tr>
<td><b>Description:</b></td>
<td>
<input TYPE="TEXT" NAME="Description" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Description")%>"></td>
</tr>
<tr>
<td><b>SUPPLIER:</b></td>
<td>
<input TYPE="TEXT" NAME="SUPPLIER" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"SUPPLIER")%>"></td>
</tr>
<tr>
<td><b>STOCK:</b></td>
<td>
<input TYPE="TEXT" NAME="STOCK" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"STOCK")%>"></td>
</tr>
<tr>
<td><b>price:</b></td>
<td>
<input TYPE="TEXT" NAME="price" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"price")%>"></td>
</tr>
<tr>
<td COLSPAN="2"><br>
<input TYPE="Submit" NAME="fp_submit"><input TYPE="Reset"
NAME="fp_reset"></td>
</tr>
</table>
<input type="hidden" name="ID" value="<%=FP_FieldHTML(fp_rs,"ID")%>">
</form>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="BODY" 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;"
startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" -->

============================================
Secondly, here is the updatesql.asp (updating the database) page:

<!--webbot bot="DatabaseRegionStart" s-columnnames s-columntypes
s-dataconnection="Database1" b-tableformat="TRUE" b-menuformat="FALSE"
s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource s-displaycolumns
s-criteria s-order s-sql="UPDATE oldstock &lt;br&gt;SET
Product='::product::', Description='::Description::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=::price::&lt;br&gt;WHERE
ID=::ID::" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="Product=&amp;Description=&amp;SUPPLIER=&amp;STOCK=&amp;pric
e=&amp;ID=" s-norecordsfound="Record updated" i-maxrecords="256"
i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc"
u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc"
tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&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;"
startspan --><!--#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 oldstock SET Product='::product::',
Description='::Description::', SUPPLIER='::SUPPLIER::', STOCK=::STOCK::,
price=::price:: WHERE ID=::ID::"
fp_sDefault="Product=&Description=&SUPPLIER=&STOCK=&price=&ID="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%"">Record
updated</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan
i-checksum="36996" --><!--webbot bot="DatabaseRegionEnd"
b-tableformat="TRUE" b-menuformat="FALSE"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY"
preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&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;"
startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="56926" --></tbody>
 
B

Bob Lehmann

If you do a Response.Write on the SQL & post that instead of that horrendous
FP gobbledy-gook, stop using technical terms like "right pickle", post error
messages and what it is you expect to happen vs. what is actually happening;
Someone may actually be able to help you out of your pickle.

Bob Lehmann
 
J

Jim Buyens

Howdy.

What's the exact failure? For example, what do you do and
what message do you receive?

As to apostrophes, for an Access database, you only put
them around text literals, as in
WHERE sname = 'smith'
Also, you put #'s around dates and nothing around numbers:
WHERE bdate < #1-Jan-1960#
WHERE boqty > 0

In SQL Server and Oracle, however, you generally put
apostrophes around all types of literals.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
-----Original Message-----
Hi,
I'm having problems updating a database - i'm bringing the data up fine in a
form but am getting in a right pickle trying to update the information.
I've tried so many combinations I'm not sure which I haven't tried. I've
also looked up many sources on the web and even bought a book (Inside Out)
and still can't find a definitive answer. I'm now also confused whether I
should have a ' round text or digits in the SQL statement, oh dear :-s.

TIA
Andy Watts

=======================
Firstly, here is the form code:

<!--webbot bot="DatabaseRegionStart"
s- columnnames="ID,Product,Description,SUPPLIER,STOCK,price"
s-columntypes="3,202,202,202,5,5" s- dataconnection="Database1"
b-tableformat="FALSE" b-menuformat="FALSE" s- menuchoice="ID"
s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b- listseparator="TRUE"
i-listformat="7" b-makeform="FALSE" s- recordsource="oldstock"
displaycolumns="ID,Product,Description,SUPPLIER,STOCK,price
"
s-criteria="{ID} EQ {ID} +" s-order s-sql="SELECT * FROM oldstock WHERE (ID
= ::ID::)" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="ID=ID" s-norecordsfound="No records returned."
i-maxrecords="256" i-groupsize="0" botid="0"
u-dblib="../_fpclass/fpdblib.inc" u- dbrgn1="../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY" 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;"
b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b- DataGridFormat="FALSE"
b-DGridAlternate="TRUE" sa-CritTypes="3" b- WasTableFormat="FALSE"
startspan --><!--#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="SELECT * FROM oldstock WHERE (ID = ::ID::)"
fp_sDefault="ID=ID"
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_sColTypes="&ID=3&Product=202&Description=202&SUPPLIER=2 02&STOCK=5&price=5
&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i- checksum="54633" --><form
METHOD="POST" action="updatesql.asp">
<!--webbot bot="PurpleText" preview="Set this form's properties so it
submits user input to the appropriate page." --><table BORDER="0">
<tr>
<td><b>ID:</b></td>
<td>
<input TYPE="TEXT" NAME="ID" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"ID")%>"></td>
</tr>
<tr>
<td><b>Product:</b></td>
<td>
<input TYPE="TEXT" NAME="Product" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Product")%>"></td>
</tr>
<tr>
<td><b>Description:</b></td>
<td>
<input TYPE="TEXT" NAME="Description" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Description")%>"></td>
</tr>
<tr>
<td><b>SUPPLIER:</b></td>
<td>
<input TYPE="TEXT" NAME="SUPPLIER" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"SUPPLIER")%>"></td>
</tr>
<tr>
<td><b>STOCK:</b></td>
<td>
<input TYPE="TEXT" NAME="STOCK" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"STOCK")%>"></td>
</tr>
<tr>
<td><b>price:</b></td>
<td>
<input TYPE="TEXT" NAME="price" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"price")%>"></td>
</tr>
<tr>
<td COLSPAN="2"><br>
<input TYPE="Submit" NAME="fp_submit"><input TYPE="Reset"
NAME="fp_reset"></td>
</tr>
</table>
<input type="hidden" name="ID" value="<%=FP_FieldHTML (fp_rs,"ID")%>">
</form>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="BODY" 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;"
startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"- ->
<!--webbot bot="DatabaseRegionEnd" endspan i- checksum="56926" -->

============================================
Secondly, here is the updatesql.asp (updating the database) page:

<!--webbot bot="DatabaseRegionStart" s-columnnames s- columntypes
s-dataconnection="Database1" b-tableformat="TRUE" b- menuformat="FALSE"
s-menuchoice s-menuvalue b-tableborder="TRUE" b- tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b- listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource s- displaycolumns
s-criteria s-order s-sql="UPDATE oldstock &lt;br&gt;SET
Product='::product::', Description='::Description::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=::price::&lt;br&gt;WHERE
ID=::ID::" b-procedure="FALSE" clientside suggestedext="asp"
defaultfields="Product=&amp;Description=&amp;SUPPLIER=&amp;
STOCK=&amp;pric
e=&amp;ID=" s-norecordsfound="Record updated" i- maxrecords="256"
i-groupsize="0" botid="0" u- dblib="../_fpclass/fpdblib.inc"
u-dbrgn1="../_fpclass/fpdbrgn1.inc" u- dbrgn2="../_fpclass/fpdbrgn2.inc"
tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&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;"
startspan --><!--#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 oldstock SET Product='::product::',
Description='::Description::', SUPPLIER='::SUPPLIER::', STOCK=::STOCK::,
price=::price:: WHERE ID=::ID::"
fp_sDefault="Product=&Description=&SUPPLIER=&STOCK=&price= &ID="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT"" width=""100%"">Record
updated</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan
i-checksum="36996" --><!--webbot bot="DatabaseRegionEnd"
b-tableformat="TRUE" b-menuformat="FALSE"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY"
preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&quot;&gt;&lt;font
color=&quot;#000000&quot;&gt;This is the
 
A

Andy Watts

Sorry, lesson learnt...

Error message is (which I didn't think particularly helpful and hence didn't
post):

Database Results Wizard Error
The operation failed. If this continues, please contact your server
administrator.

The SQL query I used in the database wizard was:

UPDATE oldstock
SET Product='::product::', Description='::Description::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=::price::
WHERE ID=::ID::

price and stock are both number fields.

Many thanks
Andy
 
K

Kathleen Anderson [MVP - FP]

If you are using FrontPage 2003, The FP2003 code has changed slightly. To
see the true error, open the hidden folder /_fpclass/ and edit the
fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True
 
A

Andy Watts

This is the returning error ...

Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
 
A

Andy Watts

Thank-you for your patience. My current settings are:

s-columnnames="ID,Product,Detail,SUPPLIER,STOCK,price"
s-columntypes="3,202,202,202,2,2"
SET Product='::product::', Detail='::Detail::', SUPPLIER='::SUPPLIER::',
STOCK=::STOCK::, price=::price:: WHERE ID=::ID::"

Current error:
Database Results Wizard Error
Description: Parameter ?_6 has no default value.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

Surely it should be simpler than this...

Thanks
Andy

Kathleen Anderson said:
"Database Results Wizard" error message when you try to change a record in
your database in FrontPage 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817029

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


Andy Watts said:
This is the returning error ...

Database Results Wizard Error
Your page contains a query with user input parameters that could not
be resolved.
This could happen if your DatabaseRegionStart webbot has an empty or
missing s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
 
K

Kathleen Anderson [MVP - FP]

You may want to read this thread in the OutFront forum:
http://www.frontpagewebmaster.com/m-175524/tm.htm

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


Andy Watts said:
Thank-you for your patience. My current settings are:

s-columnnames="ID,Product,Detail,SUPPLIER,STOCK,price"
s-columntypes="3,202,202,202,2,2"
SET Product='::product::', Detail='::Detail::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=::price:: WHERE
ID=::ID::"

Current error:
Database Results Wizard Error
Description: Parameter ?_6 has no default value.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

Surely it should be simpler than this...

Thanks
Andy

Kathleen Anderson said:
"Database Results Wizard" error message when you try to change a
record in your database in FrontPage 2003
http://support.microsoft.com/default.aspx?scid=kb;[LN];817029

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


Andy Watts said:
This is the returning error ...

Database Results Wizard Error
Your page contains a query with user input parameters that could not
be resolved.
This could happen if your DatabaseRegionStart webbot has an empty or
missing s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.


message If you are using FrontPage 2003, The FP2003 code has changed
slightly. To see the true error, open the hidden folder /_fpclass/
and edit the fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True



--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/


I'm still getting the same error message.
Andy

Description is a reserved word in Access -
http://www.aspfaq.com/show.asp?id=2080

Try changing it to something else.

Bob Lehmann

Sorry, lesson learnt...

Error message is (which I didn't think particularly helpful and
hence didn't post):

Database Results Wizard Error
The operation failed. If this continues, please contact your
server administrator.

The SQL query I used in the database wizard was:

UPDATE oldstock
SET Product='::product::', Description='::Description::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=::price::
WHERE ID=::ID::

price and stock are both number fields.

Many thanks
Andy

Howdy.

What's the exact failure? For example, what do you do and
what message do you receive?

As to apostrophes, for an Access database, you only put
them around text literals, as in
WHERE sname = 'smith'
Also, you put #'s around dates and nothing around numbers:
WHERE bdate < #1-Jan-1960#
WHERE boqty > 0

In SQL Server and Oracle, however, you generally put
apostrophes around all types of literals.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
\---------------------------------------------------
Microsoft Office FrontPage 2003 Inside Out
---------------------------------------------------
Web Database Development Step by Step .NET Edition
Microsoft FrontPage Version 2002 Inside Out
Faster Smarter Beginning Programming
(All from Microsoft Press)
/---------------------------------------------------
*----------------------------------------------------

-----Original Message-----
Hi,
I'm having problems updating a database - i'm bringing
the data up fine in a
form but am getting in a right pickle trying to update
the information.
I've tried so many combinations I'm not sure which I
haven't tried. I've
also looked up many sources on the web and even bought a
book (Inside Out)
and still can't find a definitive answer. I'm now also
confused whether I
should have a ' round text or digits in the SQL
statement, oh dear :-s.

TIA
Andy Watts

=======================
Firstly, here is the form code:

<!--webbot bot="DatabaseRegionStart"
s-
columnnames="ID,Product,Description,SUPPLIER,STOCK,price"
s-columntypes="3,202,202,202,5,5" s-
dataconnection="Database1"
b-tableformat="FALSE" b-menuformat="FALSE" s-
menuchoice="ID"
s-menuvalue="ID" b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-
listseparator="TRUE"
i-listformat="7" b-makeform="FALSE" s-
recordsource="oldstock"
s-
displaycolumns="ID,Product,Description,SUPPLIER,STOCK,price
"
s-criteria="{ID} EQ {ID} +" s-order s-sql="SELECT * FROM
oldstock WHERE (ID
= ::ID::)" b-procedure="FALSE" clientside
suggestedext="asp"
s-defaultfields="ID=ID" s-norecordsfound="No records
returned."
i-maxrecords="256" i-groupsize="0" botid="0"
u-dblib="../_fpclass/fpdblib.inc" u-
dbrgn1="../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="BODY"
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;"
b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b-
DataGridFormat="FALSE" b-DGridAlternate="TRUE"
sa-CritTypes="3" b- WasTableFormat="FALSE" startspan
--><!--#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="SELECT * FROM oldstock WHERE (ID = ::ID::)"
fp_sDefault="ID=ID"
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="ID"
fp_sMenuValue="ID"
fp_sColTypes="&ID=3&Product=202&Description=202&SUPPLIER=2
02&STOCK=5&price=5 &"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-
checksum="54633" --><form
METHOD="POST" action="updatesql.asp">
<!--webbot bot="PurpleText" preview="Set this form's
properties so it
submits user input to the appropriate page." --><table
BORDER="0"> <tr>
<td><b>ID:</b></td>
<td>
<input TYPE="TEXT" NAME="ID" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"ID")%>"></td>
</tr>
<tr>
<td><b>Product:</b></td>
<td>
<input TYPE="TEXT" NAME="Product" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Product")%>"></td>
</tr>
<tr>
<td><b>Description:</b></td>
<td>
<input TYPE="TEXT" NAME="Description" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"Description")%>"></td>
</tr>
<tr>
<td><b>SUPPLIER:</b></td>
<td>
<input TYPE="TEXT" NAME="SUPPLIER" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"SUPPLIER")%>"></td>
</tr>
<tr>
<td><b>STOCK:</b></td>
<td>
<input TYPE="TEXT" NAME="STOCK" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"STOCK")%>"></td>
</tr>
<tr>
<td><b>price:</b></td>
<td>
<input TYPE="TEXT" NAME="price" SIZE="40"
VALUE="<%=FP_FieldHTML(fp_rs,"price")%>"></td>
</tr>
<tr>
<td COLSPAN="2"><br>
<input TYPE="Submit" NAME="fp_submit"><input
TYPE="Reset"
NAME="fp_reset"></td>
</tr>
</table>
<input type="hidden" name="ID" value="<%=FP_FieldHTML
(fp_rs,"ID")%>"> </form>
<hr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE"
b-menuformat="FALSE" u-dbrgn2="../_fpclass/fpdbrgn2.inc"
i-groupsize="0" clientside tag="BODY" 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;"
startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-
->
<!--webbot bot="DatabaseRegionEnd" endspan i-
checksum="56926" -->

============================================
Secondly, here is the updatesql.asp (updating the
database) page:

<!--webbot bot="DatabaseRegionStart" s-columnnames s-
columntypes
s-dataconnection="Database1" b-tableformat="TRUE" b-
menuformat="FALSE" s-menuchoice s-menuvalue
b-tableborder="TRUE" b- tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-
listseparator="TRUE" i-listformat="0" b-makeform="FALSE"
s-recordsource s- displaycolumns s-criteria s-order
s-sql="UPDATE oldstock &lt;br&gt;SET Product='::product::',
Description='::Description::', SUPPLIER='::SUPPLIER::',
STOCK=::STOCK::, price=::price::&lt;br&gt;WHERE ID=::ID::"
b-procedure="FALSE" clientside
suggestedext="asp"
s-
defaultfields="Product=&amp;Description=&amp;SUPPLIER=&amp;
STOCK=&amp;pric
e=&amp;ID=" s-norecordsfound="Record updated" i-
maxrecords="256"
i-groupsize="0" botid="0" u-
dblib="../_fpclass/fpdblib.inc"
u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-
dbrgn2="../_fpclass/fpdbrgn2.inc"
tag="TBODY" preview="&lt;tr&gt;&lt;td colspan=64
bgcolor=&quot;#FFFF00&quot; width=&quot;100%&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;"
startspan --><!--#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 oldstock SET Product='::product::',
Description='::Description::', SUPPLIER='::SUPPLIER::',
STOCK=::STOCK::, price=::price:: WHERE ID=::ID::"
fp_sDefault="Product=&Description=&SUPPLIER=&STOCK=&price=
&ID="
fp_sNoRecords="<tr><td colspan=16 align=""LEFT""
width=""100%"">Record updated</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&"
fp_iDisplayCols=16
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan
i-checksum="36996" --><!--webbot bot="DatabaseRegionEnd"
b-tableformat="TRUE" b-menuformat="FALSE"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="TBODY"
preview="&lt;tr&gt;&lt;td colspan=64
bgcolor=&quot;#FFFF00&quot;
width=&quot;100%&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;"
startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"-
->
<!--webbot bot="DatabaseRegionEnd" endspan i-
checksum="56926" --></tbody>


.
 

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

Top