INSERT INTO a database with FP 2003

[Sally]

I've just switched to FP2003 - from FP2002 - and have a problem when I try to "insert into" my database the information from a form when created with FP2003. I capture the info and send it to my next asp page and this is the result:

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

I've been to MS support to find out about this known problem and the suggested workaround for the "timestamp" is not appropriate - I don't have a field called timestamp nor do I add timestamp to the table.

I have tried using the database wizard and sending the information to the database, which works just fine (webbot sends info back to the form and saves to the database).

HOWEVER, I need to be able to use the data in subsequent pages and this method does not allow me to do so - I can send the information to a confirmation page and have done so. BUT I cannot use the information except by the confirmation field. If I could capture that, then I would be happy.

This link illustrates the problem: http://66.201.75.238/dh/newcustomer.asp I've filled in the form

Any suggestions?

I have a couple of sites created with FP 2000 & FP 2003 where I use INSERT INTO without problems. Tried going back but it didn't work.

Thanks,
Sal

 

[Jim Buyens]

Please:

1. Rerun the DRW.
2. On page 2, select Custom Query and click Edit.
3. Select the contents of the SQL Statement text box,
copy them to the clipboard, and post them to this
thread.
4. Click Cancel and Cancel to exit without making changes.

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-----
I've just switched to FP2003 - from FP2002 - and have a

problem when I try to "insert into" my database the
information from a form when created with FP2003. I
capture the info and send it to my next asp page and this
is the result:

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

I've been to MS support to find out about this known

problem and the suggested workaround for the "timestamp"
is not appropriate - I don't have a field called timestamp
nor do I add timestamp to the table.

I have tried using the database wizard and sending the

information to the database, which works just fine (webbot
sends info back to the form and saves to the database).

HOWEVER, I need to be able to use the data in subsequent

pages and this method does not allow me to do so - I can
send the information to a confirmation page and have done
so. BUT I cannot use the information except by the
confirmation field. If I could capture that, then I would
be happy.

This link illustrates the problem:

http://66.201.75.238/dh/newcustomer.asp I've filled in
the form

Any suggestions?

I have a couple of sites created with FP 2000 & FP 2003

where I use INSERT INTO without problems. Tried going
back but it didn't work.

 

[Guest]

I'm struggling with the exact same problem. The same
Database results region INSERT query worked over and over
in FP2002. Now I get the (less than detailed)
error: "Database Results Wizard Error
The operation failed. If this continues, please contact
your server administrator."

The SQL is: INSERT INTO 62431 ( Role, Other, Firstname,
LastName, SiteName, SiteCode, PIName, Address, City,
State, Zip, Country, Phone, Fax, Email, CheckInDate,
CheckOutDate, RoomPref, SmokingPref, Reception, Guest,
AirArriveDate, AirArriveTime, AirArriveAirport,
AirArriveAirline, AirArriveFlight, AirDepartDate,
AirDepartTime, AirDepartAirport, AirDepartAirline,
AirDepartFlight, Comments)
VALUES
('::Role::', '::Other::', '::Firstname::', '::LastName::',
'::SiteName::', '::SiteCode::', ':IName::', '::Address::
', '::City::', '::State::', '::Zip::', '::Country::', ':h
one::', '::Fax::', '::Email::', '::CheckInDate::', '::Check
OutDate::', '::RoomPref::', '::SmokingPref::', '::Reception
::', '::Guest::', '::AirArriveDate::', '::AirArriveTime::',
'::AirArriveAirport::', '::AirArriveAirline::', '::AirArri
veFlight::', '::AirDepartDate::', '::AirDepartTime::', '::A
irDepartAirport::', '::AirDepartAirline::', '::AirDepartFli
ght::', '::Comments::');

These are all text fields in an ACCESS database.

Any help appreciated!
Marc

 

[Sally]

Thanks, Jim. Here's the SQ

INSERT INTO Customers (CustomerID,CompanyName,ContactFirstName,ContactLastName,BillingAddress,City,StateOrProvince,PostalCode,CountryOrRegion,ContactTitle,PhoneNumber,Extension,FaxNumber,EmailAddress,Notes) VALUES :CustomerID::,'::CompanyName::','::ContactFirstName::','::ContactLastName::','::BillingAddress::','::City::','::StateOrProvince::',':ostalCode::','::CountryOrRegion::','::ContactTitle::',':honeNumber::','::Extension::','::FaxNumber::','::EmailAddress::','::Notes::'

Sall

----- Jim Buyens wrote: ----

Please

1. Rerun the DRW
2. On page 2, select Custom Query and click Edit
3. Select the contents of the SQL Statement text box
copy them to the clipboard, and post them to thi
thread
4. Click Cancel and Cancel to exit without making changes

Jim Buyen
Microsoft FrontPage MV
http://www.interlacken.co
Author of
*---------------------------------------------------
|\--------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Ou
||--------------------------------------------------
|| Web Database Development Step by Step .NET Editio
|| Microsoft FrontPage Version 2002 Inside Ou
|| Faster Smarter Beginning Programmin
|| (All from Microsoft Press
|/--------------------------------------------------
*---------------------------------------------------

-----Original Message----
I've just switched to FP2003 - from FP2002 - and have a

problem when I try to "insert into" my database the
information from a form when created with FP2003. I
capture the info and send it to my next asp page and this
is the result

The operation failed. If this continues, please contact your server administrator

problem and the suggested workaround for the "timestamp"
is not appropriate - I don't have a field called timestamp
nor do I add timestamp to the tableinformation to the database, which works just fine (webbot
sends info back to the form and saves to the database).pages and this method does not allow me to do so - I can
send the information to a confirmation page and have done
so. BUT I cannot use the information except by the
confirmation field. If I could capture that, then I would
be happyhttp://66.201.75.238/dh/newcustomer.asp I've filled in
the forwhere I use INSERT INTO without problems. Tried going
back but it didn't work

 

[Jim Buyens]

Thanks, Jim. Here's the SQL

INSERT INTO Customers

(CustomerID,CompanyName,ContactFirstName,ContactLastName,BillingAddress,City,StateOrProvince,PostalCode,CountryOrRegion,ContactTitle,PhoneNumber,Extension,FaxNumber,EmailAddress,Notes)
VALUES :CustomerID::,'::CompanyName::','::ContactFirstName::','::ContactLastName::','::BillingAddress::','::City::','::StateOrProvince::',':ostalCode::','::CountryOrRegion::','::ContactTitle::',':honeNumber::','::Extension::','::FaxNumber::','::EmailAddress::','::Notes::')
I checked all your field names against the list of reserved words at:

http://support.microsoft.com/default.aspx?kbid=321266

I didn't see any conflicts. I though this would be the cause, but
apparently not.

So then, I ran the Database Interface Wizard and created a Datbase
Editor. A Database Editor uses a DRW to run UPDATE and DELETE
commands, and I was hoping to discover how Microsoft did that without
encountering your error. What I found was this comment, in both the
edit.asp and delete.asp pages:

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

I can only presume that the FrontPage team found they needed to hack
the DRW configuration in order to make it work with UPDATE and DELETE
commands. Someone may know how to repeat that hack and make your
INSERT command work, but I don't, and I try not to recommend that sort
of thing. It's messy, and FrontPage will probably overwrite it each
time you make a further change to the same page.

But since you seem to know some SQL, let me show you the ASP code
required to run a SQL statements:

<%
dim cnMyDb
dim sql
dim recsaff
if Request("btnSub") <> "" then
set cnMyDb = Server.CreateObject("ADODB.Connection")
cnMyDb.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
server.mappath("fpdb/my.mdb") & ";"

sql = "INSERT INTO ..."
cnMyDb.Execute sql, recsaff

cnMyDb.close
end if
%>

This assumes your form contains a Submit button named btnSub. The
initial If statement determines whether the ASP page is running becaue
of a link from another page (in which case Request("btnSub") will be
empty) or becaue the visitor has clicked the Submit button (in which
case Request("btnSub") will contain the button label).

In the cnMyDb.open statement, the argument you pass to server.mappath
is the path and file name of your database, relative to the page that
contains the code.

The code to construct the SQL statement would look something like
this.

sql = "INSERT INTO mytable (id, project, excuse) " & _
"VALUES (" & Request("id") & ", " & _
"'" & Request("project") & "', " & _
"'" & Request("excuse") & "') "

The expression Request("id") provides the value of the form field
named "id".

If you want to do a really professional job of this, you would add
logic to verify the syntax of the input form fields, and to detect any
errors that occur running your SQL statements. For starters, after you
run

cnMyDb.Execute sql, recsaff

the recsaff variable will contain the number of records affected.
After an insert, this should be 1. If it's anything else, you have a
problem.

To gracefully handle more serious errors, you would wrap
the cnMyDb.Execute statement in code like this:

on error resume next
err.clear
cnMyDb.Execute sql, recsaff
if err then
response.write "<p>Database error " & err.number & _
" - " & err.description & "</p>"
end if
on error goto 0


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)
|/---------------------------------------------------
*----------------------------------------------------

 

[Sally]

Thanks very much. I'll give it a try today

Just like so many others who have written here, I don't understand why the same statements worked with FP 2002 and then not with FP2003. I tried going restoring to fp2002 and, also, using fp2k on my notebook and that didn't help as long as I had saved the page once with FP2003

perhaps someone from Microsoft knows

~ Sall

 

[Ron]

Sal,

I just found the problem I believe. First, if you modify
the "fp_DEBUG = False" line in your fpdbrgn1.inc file
located in the _fpclass directory to say "fp_DEBUG = True"
and save the file, you will see the actual error message
instead of the "please contact your server administrator"
message. This message says:

"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." This article does not exist, so don't waste your
time. However, the folowing is copied and pasted from a
message I JUST left on another message board after
resolving my issue...

When a custom query is done on the update page that sends
the change to the database, it doesn't seem to include the
field names and attributes in the database region of the
page. On the update page, look for "<!--webbot
bot="DatabaseRegionStart" s-columnnames s-columntypes" in
the code view of the update page and add the names of the
two fields referenced in the query and the appropriate
code for the type of field it is. For example:

s-columnnames="PART_NO,PRICE" s-columntypes="202,202"

I have two fields here, and both are set up as text fields
in the DB (202.) Other codes are 3 for numeric, 135 for
dates, 203 for memo (I think.) So if I decide to use a
numeric field for price, it should be:

s-columnnames="PART_NO,PRICE" s-columntypes="202,3"

I hope it works for you as well!

-----Original Message-----
I've just switched to FP2003 - from FP2002 - and have a

problem when I try to "insert into" my database the
information from a form when created with FP2003. I
capture the info and send it to my next asp page and this
is the result:

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

I've been to MS support to find out about this known

problem and the suggested workaround for the "timestamp"
is not appropriate - I don't have a field called timestamp
nor do I add timestamp to the table.

I have tried using the database wizard and sending the

information to the database, which works just fine (webbot
sends info back to the form and saves to the database).

HOWEVER, I need to be able to use the data in subsequent

pages and this method does not allow me to do so - I can
send the information to a confirmation page and have done
so. BUT I cannot use the information except by the
confirmation field. If I could capture that, then I would
be happy.

This link illustrates the problem:

http://66.201.75.238/dh/newcustomer.asp I've filled in
the form

Any suggestions?

I have a couple of sites created with FP 2000 & FP 2003

where I use INSERT INTO without problems. Tried going
back but it didn't work.