UPDATE database problems

Andy Watts · Aug 13, 2004

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="<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>"
b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b-DataGridFormat="FALSE"
b-DGridAlternate="TRUE" sa-CritTypes="3" b-WasTableFormat="FALSE"
startspan -->
<% 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
%>

<form
METHOD="POST" action="updatesql.asp">
<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>



============================================
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 <br>SET
Product=':

roduct::', Description=':

escription::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=:

rice::<br>WHERE
ID=::ID::" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="Product=&Description=&SUPPLIER=&STOCK=&pric
e=&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="<tr><td colspan=64 bgcolor="#FFFF00"
width="100%"><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>"
startspan -->
<% 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=':

roduct::',
Description=':

escription::', SUPPLIER='::SUPPLIER::', STOCK=::STOCK::,
price=:

rice:: 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
%>


</tbody>

Bob Lehmann · Aug 13, 2004

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

Jim Buyens · Aug 13, 2004

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="<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>"
b-UseDotNET="FALSE" CurrentExt sa-InputTypes="3" b- DataGridFormat="FALSE"
b-DGridAlternate="TRUE" sa-CritTypes="3" b- WasTableFormat="FALSE"
startspan -->
<form
METHOD="POST" action="updatesql.asp">
<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="<table border=0
width="100%"><tr><td
bgcolor="#FFFF00"><font

color="#000000">This is

the end of a Database Results
region.</font></td></tr></table>"
startspan -->

============================================
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 <br>SET
Product=':roduct::', Description=':escription::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=:rice::<br>WHERE
ID=::ID::" b-procedure="FALSE" clientside suggestedext="asp"
defaultfields="Product=&Description=&SUPPLIER=&
STOCK=&pric
e=&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="<tr><td colspan=64 bgcolor="#FFFF00"
width="100%"><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>"
startspan -->
<!--webbot bot="DatabaseRegionEnd"
b-tableformat="TRUE" b-menuformat="FALSE"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY"
preview="<tr><td colspan=64 bgcolor="#FFFF00"
width="100%"><font

color="#000000">This is the

Bob Lehmann · Aug 14, 2004

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

Try changing it to something else.

Bob Lehmann

Andy Watts · Aug 14, 2004

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=':

roduct::', Description=':

escription::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=:

rice::
WHERE ID=::ID::

price and stock are both number fields.

Many thanks
Andy

Kathleen Anderson [MVP - FP] · Aug 14, 2004

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

Andy Watts · Aug 14, 2004

I'm still getting the same error message.
Andy

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

Try changing it to something else.

Bob Lehmann

Andy Watts · Aug 14, 2004

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.

Kathleen Anderson [MVP - FP] · Aug 15, 2004

"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 · Aug 16, 2004

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=':

roduct::', Detail=':

etail::', SUPPLIER='::SUPPLIER::',
STOCK=::STOCK::, price=:

rice:: 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.

Click to expand...

Kathleen Anderson [MVP - FP] · Aug 17, 2004

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=':roduct::', Detail=':etail::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=:rice:: 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=':roduct::', Description=':escription::',
SUPPLIER='::SUPPLIER::', STOCK=::STOCK::, price=:rice::
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:


<form
METHOD="POST" action="updatesql.asp">
<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>


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


</tbody>

.

Click to expand...

Click to expand...

I am trying to understand this code...	3	Jul 26, 2008
@ my wits end	1	Sep 12, 2006
Please help, FP generated code produces a "Too few parameters. Expected1" error!	7	Jun 6, 2005
any ideas why I can update one table in my database but not the other?	2	Sep 22, 2006
All values item in a dropdown box for numeric field query	0	Aug 24, 2006
Select statement aanpassen in asp gedeelte middels variable	1	Mar 30, 2007
FP Database help needed...	6	Oct 18, 2005
Database Update Buttons	8	May 25, 2005

UPDATE database problems

Andy Watts

Bob Lehmann

Jim Buyens

Bob Lehmann

Andy Watts

Kathleen Anderson [MVP - FP]

Andy Watts

Andy Watts

Kathleen Anderson [MVP - FP]

Andy Watts

Kathleen Anderson [MVP - FP]

Ask a Question

Similar Threads