Access Database record added via asp - but it is not inserted

I have developed an application using frontpage and MS Access, all functions
work correctly on my development system. Have published the web site to a
production server and all seemed to work.

However when functions add a record the a database table, asp reports that
the record was added, but in fact it doesn't get into the database. Read and
update functions work correctly. The new site is on a windows 2003 server
using frontpage with server extensions 2002 and MS Office 2003 including
Access.

I have tried to do an odbc trace, but couldn't get that to work either.

Does anyone have suggestions how to determine what could be wrong?
--
Trevor

Re: Access Database record added via asp - but it is not inserted

Trevor wrote:
> I have developed an application using frontpage and MS Access, all functions
> work correctly on my development system. Have published the web site to a
> production server and all seemed to work.
>
> However when functions add a record the a database table, asp reports that
> the record was added, but in fact it doesn't get into the database. Read and
> update functions work correctly. The new site is on a windows 2003 server
> using frontpage with server extensions 2002 and MS Office 2003 including
> Access.
>
> I have tried to do an odbc trace, but couldn't get that to work either.
>
> Does anyone have suggestions how to determine what could be wrong?
> --
> Trevor

Impossible to even start to guess without seeing the code you are using
to insert a record.

--
Mike Brind

Re: Access Database record added via asp - but it is not inserted

Trevor wrote:
> I have developed an application using frontpage and MS Access, all
> functions work correctly on my development system. Have published
> the web site to a production server and all seemed to work.
>
> However when functions add a record the a database table, asp reports
> that the record was added, but in fact it doesn't get into the
> database. Read and update functions work correctly. The new site is
> on a windows 2003 server using frontpage with server extensions 2002
> and MS Office 2003 including Access.
>
> I have tried to do an odbc trace, but couldn't get that to work
> either.
>
> Does anyone have suggestions how to determine what could be wrong?

Not without seeing a repro script. We need to be able to reproduce your
problem in order to be able to help.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

RE: Access Database record added via asp - but it is not inserted

Copy of asp source:

<%

On Error Resume Next
Session("FP_OldCodePage") = Session.CodePage
Session("FP_OldLCID") = Session.LCID
Session.CodePage = 1252
Session.LCID = 2057
Err.Clear

strErrorUrl = ""

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
If Request.Form("VTI-GROUP") = "0" Then
Err.Clear

Set fp_conn = Server.CreateObject("ADODB.Connection")
FP_DumpError strErrorUrl, "Cannot create connection"

Set fp_rs = Server.CreateObject("ADODB.Recordset")
FP_DumpError strErrorUrl, "Cannot create record set"

fp_conn.Open Application("CopyScheduling_ConnectionString")
FP_DumpError strErrorUrl, "Cannot open database"

fp_rs.Open """Client Table""", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
FP_DumpError strErrorUrl, "Cannot open record set"

fp_rs.AddNew
FP_DumpError strErrorUrl, "Cannot add new record set to the database"
Dim arFormFields0(2)
Dim arFormDBFields0(2)
Dim arFormValues0(2)

arFormFields0(0) = "Field2"
arFormDBFields0(0) = "Client Name"
arFormValues0(0) = Request("Field2")
arFormFields0(1) = "Field3"
arFormDBFields0(1) = "Current"
arFormValues0(1) = "Yes"

FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0


fp_rs.Update
FP_DumpError strErrorUrl, "Cannot update the database"

fp_rs.Close
fp_conn.Close

FP_FormConfirmation "text/html; charset=windows-1252",_
"Form Confirmation",_
"Thank you for submitting the following information:",_
"Add_Client_page.asp",_
"Return to the form."

End If
End If

Session.CodePage = Session("FP_OldCodePage")
Session.LCID = Session("FP_OldLCID")

%>
<% Response.Buffer = True %>

Re: Access Database record added via asp - but it is not inserted

Sorry, but I cannot deal with all this frontpage stuff. There's calls to
functions and methods in there and I just don't know what they do. Maybe if
you try a frontpage newsgroup ...
One bad thing I see is using a recordset to edit data, but
1. It's not fatal and certainly won't lead to the symptom you described in
your OP
2. I can't show you the right way to do it unless you're willing to drop the
frontpage crutch.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

RE: Access Database record added via asp - but it is not inserted

Make sure your permission are correct on the server directory the database is
stored in. You have better permissions than your users and they can not
write to the database.


Mike




"Trevor" wrote:

> I have developed an application using frontpage and MS Access, all functions
> work correctly on my development system. Have published the web site to a
> production server and all seemed to work.
>
> However when functions add a record the a database table, asp reports that
> the record was added, but in fact it doesn't get into the database. Read and
> update functions work correctly. The new site is on a windows 2003 server
> using frontpage with server extensions 2002 and MS Office 2003 including
> Access.
>
> I have tried to do an odbc trace, but couldn't get that to work either.
>
> Does anyone have suggestions how to determine what could be wrong?
> --
> Trevor

RE: Access Database record added via asp - but it is not inserted

I don't understand that, updates to the database do get applied it's only
adds that dont.
--
Trevor


"Mike" wrote:

> Make sure your permission are correct on the server directory the database is
> stored in. You have better permissions than your users and they can not
> write to the database.
>
>
> Mike
>
>
>
>
> "Trevor" wrote:
>
> > I have developed an application using frontpage and MS Access, all functions
> > work correctly on my development system. Have published the web site to a
> > production server and all seemed to work.
> >
> > However when functions add a record the a database table, asp reports that
> > the record was added, but in fact it doesn't get into the database. Read and
> > update functions work correctly. The new site is on a windows 2003 server
> > using frontpage with server extensions 2002 and MS Office 2003 including
> > Access.
> >
> > I have tried to do an odbc trace, but couldn't get that to work either.
> >
> > Does anyone have suggestions how to determine what could be wrong?
> > --
> > Trevor

Re: Access Database record added via asp - but it is not inserted

> fp_rs.AddNew
> FP_DumpError strErrorUrl, "Cannot add new record set to the database"
> Dim arFormFields0(2)
> Dim arFormDBFields0(2)
> Dim arFormValues0(2)
>
> arFormFields0(0) = "Field2"
> arFormDBFields0(0) = "Client Name"
> arFormValues0(0) = Request("Field2")
> arFormFields0(1) = "Field3"
> arFormDBFields0(1) = "Current"
> arFormValues0(1) = "Yes"
>
> FP_SaveFormFields fp_rs, arFormFields0, arFormDBFields0
>
>fp_rs.Update
>FP_DumpError strErrorUrl, "Cannot update the database"



It would be better if we could see the code for the FP_SaveFormFields
function as well.

However a couple of problems might be indicated here.

The arFormValues0 array doesn't appear to be used anywhere I suspect that
this too ought to be passed to FP_SaveFormFields as the other arrays are.

I'm not an Access expert but should:-

arFormValues0(1) = "Yes"

in fact be:-

arFormValues0(1) = True

This is the downside to using Frontpage and builders of it's ilk. When they
go wrong it can be like wadding throught treacle to find out what's up.

Antony.

Re: Access Database record added via asp - but it is not inserted

Anthony Jones wrote:
>
> I'm not an Access expert but should:-
>
> arFormValues0(1) = "Yes"
>
> in fact be:-
>
> arFormValues0(1) = True
>
> This is the downside to using Frontpage and builders of it's ilk.
> When they go wrong it can be like wadding throught treacle to find
> out what's up.
>
Jet stores Yes/No data as numbers: 0 for false, -1 for true.
You are correct that the cited line is incorrect, but it should throw a
"mismatch" error. Maybe he has an On Error Resume Next somewhere in there
that is masking the error ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

RE: Access Database record added via asp - but it is not inserted

Resolved.

The problem was due to different double quotes vs square brackets.
Removing the on error resume highlighted the problem.
Resolution is to change

fp_rs.Open """Client Table"""", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable

to

fp_rs.Open "[Client Table]", fp_conn, 1, 3, 2 ' adOpenKeySet,
adLockOptimistic, adCmdTable
--
Trevor


"Trevor" wrote:

> I have developed an application using frontpage and MS Access, all functions
> work correctly on my development system. Have published the web site to a
> production server and all seemed to work.
>
> However when functions add a record the a database table, asp reports that
> the record was added, but in fact it doesn't get into the database. Read and
> update functions work correctly. The new site is on a windows 2003 server
> using frontpage with server extensions 2002 and MS Office 2003 including
> Access.
>
> I have tried to do an odbc trace, but couldn't get that to work either.
>
> Does anyone have suggestions how to determine what could be wrong?
> --
> Trevor