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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trevor - 24 Mar 2006 13:35 GMT

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?

Signature

Trevor

Reply to this Message

Mike Brind - 24 Mar 2006 15:26 GMT

> 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
[quoted text clipped - 11 lines]
> --
> Trevor

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

--
Mike Brind

Reply to this Message

Bob Barrows [MVP] - 24 Mar 2006 15:26 GMT

> I have developed an application using frontpage and MS Access, all
> functions work correctly on my development system. Have published
[quoted text clipped - 10 lines]
>
> 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.

Signature

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.

Reply to this Message

Trevor - 24 Mar 2006 16:17 GMT

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

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Add a Client </title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body bgcolor="#FFFFFF" background="images/Background.bmp">

<table width="100%" align=left>
<tr>
<td width="100%">
    <p align="center"><font size="+3"><b>Add A Client</b></font>
    </td>
</tr>
</table>

<p>
<br clear="all">
<hr>
<p>

<form METHOD="POST" action="--WEBBOT-SELF--">

<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">


<p><b>Client Name</b><br>
<input type="TEXT" name="Field2" size="50" value="" maxlength="50"><br>
</p>

<p><input type="submit" value=" OK "><input type="reset" value=" Reset
"></p>

</form>

</body>

</html>

Signature

Trevor

> 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
[quoted text clipped - 9 lines]
>
> Does anyone have suggestions how to determine what could be wrong?

Reply to this Message

Bob Barrows [MVP] - 24 Mar 2006 17:16 GMT

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.

<snip>

Signature

Reply to this Message

Anthony Jones - 28 Mar 2006 11:58 GMT

> fp_rs.AddNew
> FP_DumpError strErrorUrl, "Cannot add new record set to the database"
[quoted text clipped - 13 lines]
>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.

Reply to this Message

Bob Barrows [MVP] - 28 Mar 2006 12:32 GMT

> I'm not an Access expert but should:-
>
[quoted text clipped - 7 lines]
> 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 ...

Signature

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"

Reply to this Message

Mike - 28 Mar 2006 03:54 GMT

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

Reply to this Message

Trevor - 28 Mar 2006 10:53 GMT

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

Signature

Trevor

> 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
[quoted text clipped - 15 lines]
> >
> > Does anyone have suggestions how to determine what could be wrong?

Reply to this Message

Trevor - 28 Mar 2006 13:55 GMT

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

Signature

Trevor

Reply to this Message

Webmaster Forum / ASP / Database Access / March 2006

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