Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cannot Update Access Database

Status
Not open for further replies.

wilberforce2

IS-IT--Management
Jan 8, 2007
36
GB
Hello All

I have a simple asp.net 2.0 project using Visual Web Studio 2005. I have set up a Details view and enable the Edit and Delete buttons. I have also made sure the Generate INSERT, UPDATE and DELETE Statements is checked. But when I run the page I can see the data however when I try to update I enter new information into the text boxes click update and nothing happens. It just reverts back with no error. Any ideas how I can troubleshoot? I have set the text fields in Access to allow zero length.

Here is the code:
Code:
<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]

<script runat="server">

</script>

<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" ConflictDetection="CompareAllValues"
            DataFile="~/App_Data/Data.mdb" DeleteCommand="DELETE FROM [tblData] WHERE [fldDataID] = ? AND [fldCompanyName] = ? AND [fldAddress1] = ? AND [fldAddress2] = ? AND [fldAddress3] = ? AND [fldTownCity] = ? AND [fldCounty] = ? AND [fldPostcode] = ? AND [fldTelephone] = ? AND [fldMobile] = ? AND [fldFax] = ? AND [fldEmail] = ? AND [fldWebsite] = ? AND [fldTherapy1] = ? AND [fldTherapy2] = ? AND [fldTherapy3] = ? AND [fldTherapy4] = ? AND [fldDescription] = ? AND [fldLat] = ? AND [fldLong] = ?"
            InsertCommand="INSERT INTO [tblData] ([fldDataID], [fldCompanyName], [fldAddress1], [fldAddress2], [fldAddress3], [fldTownCity], [fldCounty], [fldPostcode], [fldTelephone], [fldMobile], [fldFax], [fldEmail], [fldWebsite], [fldTherapy1], [fldTherapy2], [fldTherapy3], [fldTherapy4], [fldDescription], [fldLat], [fldLong]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [tblData]"
            UpdateCommand="UPDATE [tblData] SET [fldCompanyName] = ?, [fldAddress1] = ?, [fldAddress2] = ?, [fldAddress3] = ?, [fldTownCity] = ?, [fldCounty] = ?, [fldPostcode] = ?, [fldTelephone] = ?, [fldMobile] = ?, [fldFax] = ?, [fldEmail] = ?, [fldWebsite] = ?, [fldTherapy1] = ?, [fldTherapy2] = ?, [fldTherapy3] = ?, [fldTherapy4] = ?, [fldDescription] = ?, [fldLat] = ?, [fldLong] = ? WHERE [fldDataID] = ? AND [fldCompanyName] = ? AND [fldAddress1] = ? AND [fldAddress2] = ? AND [fldAddress3] = ? AND [fldTownCity] = ? AND [fldCounty] = ? AND [fldPostcode] = ? AND [fldTelephone] = ? AND [fldMobile] = ? AND [fldFax] = ? AND [fldEmail] = ? AND [fldWebsite] = ? AND [fldTherapy1] = ? AND [fldTherapy2] = ? AND [fldTherapy3] = ? AND [fldTherapy4] = ? AND [fldDescription] = ? AND [fldLat] = ? AND [fldLong] = ?">
            <DeleteParameters>
                <asp:Parameter Name="original_fldDataID" Type="Int32" />
                <asp:Parameter Name="original_fldCompanyName" Type="String" />
                <asp:Parameter Name="original_fldAddress1" Type="String" />
                <asp:Parameter Name="original_fldAddress2" Type="String" />
                <asp:Parameter Name="original_fldAddress3" Type="String" />
                <asp:Parameter Name="original_fldTownCity" Type="String" />
                <asp:Parameter Name="original_fldCounty" Type="String" />
                <asp:Parameter Name="original_fldPostcode" Type="String" />
                <asp:Parameter Name="original_fldTelephone" Type="String" />
                <asp:Parameter Name="original_fldMobile" Type="String" />
                <asp:Parameter Name="original_fldFax" Type="String" />
                <asp:Parameter Name="original_fldEmail" Type="String" />
                <asp:Parameter Name="original_fldWebsite" Type="String" />
                <asp:Parameter Name="original_fldTherapy1" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy2" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy3" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy4" Type="Int32" />
                <asp:Parameter Name="original_fldDescription" Type="String" />
                <asp:Parameter Name="original_fldLat" Type="Int32" />
                <asp:Parameter Name="original_fldLong" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="fldCompanyName" Type="String" />
                <asp:Parameter Name="fldAddress1" Type="String" />
                <asp:Parameter Name="fldAddress2" Type="String" />
                <asp:Parameter Name="fldAddress3" Type="String" />
                <asp:Parameter Name="fldTownCity" Type="String" />
                <asp:Parameter Name="fldCounty" Type="String" />
                <asp:Parameter Name="fldPostcode" Type="String" />
                <asp:Parameter Name="fldTelephone" Type="String" />
                <asp:Parameter Name="fldMobile" Type="String" />
                <asp:Parameter Name="fldFax" Type="String" />
                <asp:Parameter Name="fldEmail" Type="String" />
                <asp:Parameter Name="fldWebsite" Type="String" />
                <asp:Parameter Name="fldTherapy1" Type="Int32" />
                <asp:Parameter Name="fldTherapy2" Type="Int32" />
                <asp:Parameter Name="fldTherapy3" Type="Int32" />
                <asp:Parameter Name="fldTherapy4" Type="Int32" />
                <asp:Parameter Name="fldDescription" Type="String" />
                <asp:Parameter Name="fldLat" Type="Int32" />
                <asp:Parameter Name="fldLong" Type="Int32" />
                <asp:Parameter Name="original_fldDataID" Type="Int32" />
                <asp:Parameter Name="original_fldCompanyName" Type="String" />
                <asp:Parameter Name="original_fldAddress1" Type="String" />
                <asp:Parameter Name="original_fldAddress2" Type="String" />
                <asp:Parameter Name="original_fldAddress3" Type="String" />
                <asp:Parameter Name="original_fldTownCity" Type="String" />
                <asp:Parameter Name="original_fldCounty" Type="String" />
                <asp:Parameter Name="original_fldPostcode" Type="String" />
                <asp:Parameter Name="original_fldTelephone" Type="String" />
                <asp:Parameter Name="original_fldMobile" Type="String" />
                <asp:Parameter Name="original_fldFax" Type="String" />
                <asp:Parameter Name="original_fldEmail" Type="String" />
                <asp:Parameter Name="original_fldWebsite" Type="String" />
                <asp:Parameter Name="original_fldTherapy1" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy2" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy3" Type="Int32" />
                <asp:Parameter Name="original_fldTherapy4" Type="Int32" />
                <asp:Parameter Name="original_fldDescription" Type="String" />
                <asp:Parameter Name="original_fldLat" Type="Int32" />
                <asp:Parameter Name="original_fldLong" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="fldDataID" Type="Int32" />
                <asp:Parameter Name="fldCompanyName" Type="String" />
                <asp:Parameter Name="fldAddress1" Type="String" />
                <asp:Parameter Name="fldAddress2" Type="String" />
                <asp:Parameter Name="fldAddress3" Type="String" />
                <asp:Parameter Name="fldTownCity" Type="String" />
                <asp:Parameter Name="fldCounty" Type="String" />
                <asp:Parameter Name="fldPostcode" Type="String" />
                <asp:Parameter Name="fldTelephone" Type="String" />
                <asp:Parameter Name="fldMobile" Type="String" />
                <asp:Parameter Name="fldFax" Type="String" />
                <asp:Parameter Name="fldEmail" Type="String" />
                <asp:Parameter Name="fldWebsite" Type="String" />
                <asp:Parameter Name="fldTherapy1" Type="Int32" />
                <asp:Parameter Name="fldTherapy2" Type="Int32" />
                <asp:Parameter Name="fldTherapy3" Type="Int32" />
                <asp:Parameter Name="fldTherapy4" Type="Int32" />
                <asp:Parameter Name="fldDescription" Type="String" />
                <asp:Parameter Name="fldLat" Type="Int32" />
                <asp:Parameter Name="fldLong" Type="Int32" />
            </InsertParameters>
        </asp:AccessDataSource>
    
    </div>
        <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"
            DataKeyNames="fldDataID" DataSourceID="AccessDataSource1" Height="50px" Width="125px">
            <Fields>
                <asp:BoundField DataField="fldDataID" HeaderText="fldDataID" InsertVisible="False"
                    ReadOnly="True" SortExpression="fldDataID" />
                <asp:BoundField DataField="fldCompanyName" HeaderText="fldCompanyName" SortExpression="fldCompanyName" />
                <asp:BoundField DataField="fldAddress1" HeaderText="fldAddress1" SortExpression="fldAddress1" />
                <asp:BoundField DataField="fldAddress2" HeaderText="fldAddress2" SortExpression="fldAddress2" />
                <asp:BoundField DataField="fldAddress3" HeaderText="fldAddress3" SortExpression="fldAddress3" />
                <asp:BoundField DataField="fldTownCity" HeaderText="fldTownCity" SortExpression="fldTownCity" />
                <asp:BoundField DataField="fldCounty" HeaderText="fldCounty" SortExpression="fldCounty" />
                <asp:BoundField DataField="fldPostcode" HeaderText="fldPostcode" SortExpression="fldPostcode" />
                <asp:BoundField DataField="fldTelephone" HeaderText="fldTelephone" SortExpression="fldTelephone" />
                <asp:BoundField DataField="fldMobile" HeaderText="fldMobile" SortExpression="fldMobile" />
                <asp:BoundField DataField="fldFax" HeaderText="fldFax" SortExpression="fldFax" />
                <asp:BoundField DataField="fldEmail" HeaderText="fldEmail" SortExpression="fldEmail" />
                <asp:BoundField DataField="fldWebsite" HeaderText="fldWebsite" SortExpression="fldWebsite" />
                <asp:BoundField DataField="fldTherapy1" HeaderText="fldTherapy1" SortExpression="fldTherapy1" />
                <asp:BoundField DataField="fldTherapy2" HeaderText="fldTherapy2" SortExpression="fldTherapy2" />
                <asp:BoundField DataField="fldTherapy3" HeaderText="fldTherapy3" SortExpression="fldTherapy3" />
                <asp:BoundField DataField="fldTherapy4" HeaderText="fldTherapy4" SortExpression="fldTherapy4" />
                <asp:BoundField DataField="fldDescription" HeaderText="fldDescription" SortExpression="fldDescription" />
                <asp:BoundField DataField="fldLat" HeaderText="fldLat" SortExpression="fldLat" />
                <asp:BoundField DataField="fldLong" HeaderText="fldLong" SortExpression="fldLong" />
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
            </Fields>
        </asp:DetailsView>
    </form>
</body>
</html>
 
Having same issue... i believe there is a problem with the primary key not getting updated in access from the aspx page. I am still researching the fix/code to resolve, please post if you find one.

ps- thanks for posting this. Thought I was the only one having the problem.
 
Are all your parameters in the same order as they appear in the database (This used to be an issue with access iirc)

Do the delete and insert methods work ok?
If So, does your update query work if u code it in Access SQL view ? (Im sure it will, but sometimes its the obvious mistakes that cause the biggest headaches)

K
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top