Save to database help!!

S

skc

Using FP2000.

I have two forms -:

a. form.asp is a form
b. submit.asp is a page that contains a INSERT INTO custom
SQL that picks up fields from form.asp.

I keep getting:

------
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.
-------

What I am trying to do is post data into a database as
well as trying to change a value using an UPDATE...but I
can't .

Can I simply use a "Save to database" in form.asp and at
the same time have a confirm.asp?ID=<%=Request.QueryString
("value")%> so that I can run the UPDATE custom SQL as my
confirmation page?

This does not work for me...can someone suggest a possible
way?

AGAIN TO RE-ITERATE - I am trying to save a form to a
database with the same ID number, but also updating all
records in the table with the same ID number to a
value "complete" - a field in my database called "status".

Please help!!

skc
 
J

Jim Buyens

There's undoubtedly some way of hacking FrontPage into
doing what you want, but it's going to be 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 two 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

sql = "UPDATE ... "
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 statements would look
something like this.

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

sql = "UPDATE MYTABLE " & _
"SET STATUS = 'COMPLETE' " & _
"WHERE ID = " & Request("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 statements 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)
|/---------------------------------------------------
*----------------------------------------------------
-----Original Message-----
Using FP2000.

I have two forms -:

a. form.asp is a form
b. submit.asp is a page that contains a INSERT INTO custom
SQL that picks up fields from form.asp.

I keep getting:

------
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.
-------

What I am trying to do is post data into a database as
well as trying to change a value using an UPDATE...but I
can't .

Can I simply use a "Save to database" in form.asp and at
the same time have a confirm.asp?ID=<%=Request.QueryString
("value")%> so that I can run the UPDATE custom SQL as my
confirmation page?

This does not work for me...can someone suggest a possible
way?

AGAIN TO RE-ITERATE - I am trying to save a form to a
database with the same ID number, but also updating all
records in the table with the same ID number to a
value "complete" - a field in my database called "status".

Please help!!

skc
.
Click to expand...
 
S

skc

Jim.

You are a true professional.

I will read through this later today and do some playing.

Wil keep you posted.

skc
-----Original Message-----
There's undoubtedly some way of hacking FrontPage into
doing what you want, but it's going to be 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 two 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

sql = "UPDATE ... "
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 statements would look
something like this.

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

sql = "UPDATE MYTABLE " & _
"SET STATUS = 'COMPLETE' " & _
"WHERE ID = " & Request("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 statements 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)
|/---------------------------------------------------
*----------------------------------------------------
-----Original Message-----
Using FP2000.

I have two forms -:

a. form.asp is a form
b. submit.asp is a page that contains a INSERT INTO custom
SQL that picks up fields from form.asp.

I keep getting:

------
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.
-------

What I am trying to do is post data into a database as
well as trying to change a value using an UPDATE...but I
can't .

Can I simply use a "Save to database" in form.asp and at
the same time have a confirm.asp?ID=<% =Request.QueryString
("value")%> so that I can run the UPDATE custom SQL as my
confirmation page?

This does not work for me...can someone suggest a possible
way?

AGAIN TO RE-ITERATE - I am trying to save a form to a
database with the same ID number, but also updating all
records in the table with the same ID number to a
value "complete" - a field in my database called "status".

Please help!!

skc
.
Click to expand...
.
Click to expand...
 

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

Similar Threads

Word and FP2000 1
Database Driven Field info. 1
Database Error 1
Database Results Wizard - records with todays date 3
FP and connection to a SQL database 13
Database connection without DRW 1
Access Need help in making asset history in my current database. 1
UPDATE SQL 5

Top