newbie problem: updating an access db

N

Ned Balzer

Hi all,

I have encountered a really weird problem -- probably it is obvious to
someone but not to me. I hoep someone can give me a clue.

I am implementing some code straight out of the book "ASP.NET 2.0 for
Dummies". I just copied the code onto an IIS server with the 2.0 .NET
framework and tried to run it. I had to make two changes:
1) changed the path to the supplied access .mdb file in the web.config
file
2) changed the permissions on the .mdb file so that "Users" have write
access.

Here is the weird thing: the application will let me insert a record
into the access table. it will let me delete the record. But it will
not update the record. I can't think of any permissions reason why
insert and delete would work and update wouldn't -- update is just a
special case of insert and delete, right?

I was assuming that the author's code had been tested. I'm not sure if
that's a valid assumption. Initially the code for insert worked but
the code for update and delete did not work. I made a couple of
changes to the update and delete code, and the delete problem was
fixed, but not the update problem. Here is the code for the
DetailsView control and the DataSourceControl:

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px"
Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum"
DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated"
OnItemDeleted="DetailsView1_ItemDeleted"
OnItemCommand="DetailsView1_ItemCommand">
<Fields>
<asp:BoundField DataField="AdNum" HeaderText="AdNum"
SortExpression="AdNum" />
<asp:BoundField DataField="Title" HeaderText="Title"
SortExpression="Title" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="Price" HeaderText="Price"
SortExpression="Price" />
<asp:BoundField DataField="Phone" HeaderText="Phone"
SortExpression="Phone" />
<asp:BoundField DataField="Email" HeaderText="Email"
SortExpression="Email" />
<asp:BoundField DataField="State" HeaderText="State"
SortExpression="State" />
<asp:BoundField DataField="UserPassword"
HeaderText="UserPassword" SortExpression="UserPassword" />
<asp:CommandField ButtonType="Button" ShowDeleteButton="True"
ShowEditButton="True" />
</Fields>
<RowStyle BackColor="#FFFBD6" />
<FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
</asp:DetailsView>
&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$
ConnectionStrings:classydbConnectionString %>"
ProviderName="<%$
ConnectionStrings:classydbConnectionString.ProviderName %>"
SelectCommand="SELECT [AdNum], [Title], [Category],
[Description], [Price], [Phone], , [State], [UserPassword] FROM
[Ads] WHERE ([AdNum] = ?)"
UpdateCommand="UPDATE [Ads] SET [Title] = ?, [Category] = ?,
[Description] = ?, [Price] = ?, [Phone] = ?, [Email] = ?, [State] = ?,
[UserPassword] = ? WHERE ([AdNum] = ?)"
DeleteCommand="DELETE FROM [Ads] WHERE ([AdNum] = ?)"<SelectParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="Title" Type="String" />
<asp:parameter Name="Category" Type="String" />
<asp:parameter Name="Description" Type="String" />
<asp:parameter Name="Price" Type="Decimal" />
<asp:parameter Name="Phone" Type="String" />
<asp:parameter Name="Email" Type="String" />
<asp:parameter Name="State" Type="String" />
<asp:parameter Name="UserPassword" Type="String" />
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

Changes I made:
1) I added the AdNum as a bound field in the detailsView control so I
could see if it has the correct value (it does)
2) I took out a bunch of extraneous conditions in the WHERE clauses of
the delete and update commands, so now they just test whether Adnum= ?.
This seemed to be sufficient to fix the delete problem so i was sure
update would start working too, but no.
3) I put parens around the WHERE condition of the update and delete
commands to make the syntax exactly like the syntax in the select
command. Seems to be irrelevant to the delete problem.

This has been driving me batty all day. I hate access, but until i can
get this code to work, I don't want to start modifying it to use MSSQL,
which is my ultimate target DBMS for this.

Thanks in advance of any advice -- I'm happy to provide more of the
code or the access table structure or whatever is needed.

-- Ned
 
N

Ned Balzer

I want to document that I discovered the source of the problem -- after
modifying the code to use SQL Server it still exhibited the same
behavior.

It turned out to be a parameter of SQLDataSource: ConflictDetection was
set to "CompareAllValues" instead of the default "OverwriteChanges".
This caused (for some reason that I don't fully understand yet but am
still studying the code and alll the options) the update to fail since
some of the values had been changed (well, duh!) -- presumably this
option is set to prevent user A from accidentally overwriting the
changes user B made while both were trying to update the same record.
But something in the code was preventing updates even in a single user
environment.

-- Ned
Ned said:
Hi all,

I have encountered a really weird problem -- probably it is obvious to
someone but not to me. I hoep someone can give me a clue.

I am implementing some code straight out of the book "ASP.NET 2.0 for
Dummies". I just copied the code onto an IIS server with the 2.0 .NET
framework and tried to run it. I had to make two changes:
1) changed the path to the supplied access .mdb file in the web.config
file
2) changed the permissions on the .mdb file so that "Users" have write
access.

Here is the weird thing: the application will let me insert a record
into the access table. it will let me delete the record. But it will
not update the record. I can't think of any permissions reason why
insert and delete would work and update wouldn't -- update is just a
special case of insert and delete, right?

I was assuming that the author's code had been tested. I'm not sure if
that's a valid assumption. Initially the code for insert worked but
the code for update and delete did not work. I made a couple of
changes to the update and delete code, and the delete problem was
fixed, but not the update problem. Here is the code for the
DetailsView control and the DataSourceControl:

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px"
Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum"
DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated"
OnItemDeleted="DetailsView1_ItemDeleted"
OnItemCommand="DetailsView1_ItemCommand">
<Fields>
<asp:BoundField DataField="AdNum" HeaderText="AdNum"
SortExpression="AdNum" />
<asp:BoundField DataField="Title" HeaderText="Title"
SortExpression="Title" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="Price" HeaderText="Price"
SortExpression="Price" />
<asp:BoundField DataField="Phone" HeaderText="Phone"
SortExpression="Phone" />
<asp:BoundField DataField="Email" HeaderText="Email"
SortExpression="Email" />
<asp:BoundField DataField="State" HeaderText="State"
SortExpression="State" />
<asp:BoundField DataField="UserPassword"
HeaderText="UserPassword" SortExpression="UserPassword" />
<asp:CommandField ButtonType="Button" ShowDeleteButton="True"
ShowEditButton="True" />
</Fields>
<RowStyle BackColor="#FFFBD6" />
<FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
</asp:DetailsView>
&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$
ConnectionStrings:classydbConnectionString %>"
ProviderName="<%$
ConnectionStrings:classydbConnectionString.ProviderName %>"
SelectCommand="SELECT [AdNum], [Title], [Category],
[Description], [Price], [Phone], , [State], [UserPassword] FROM
[Ads] WHERE ([AdNum] = ?)"
UpdateCommand="UPDATE [Ads] SET [Title] = ?, [Category] = ?,
[Description] = ?, [Price] = ?, [Phone] = ?, [Email] = ?, [State] = ?,
[UserPassword] = ? WHERE ([AdNum] = ?)"
DeleteCommand="DELETE FROM [Ads] WHERE ([AdNum] = ?)"<SelectParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="Title" Type="String" />
<asp:parameter Name="Category" Type="String" />
<asp:parameter Name="Description" Type="String" />
<asp:parameter Name="Price" Type="Decimal" />
<asp:parameter Name="Phone" Type="String" />
<asp:parameter Name="Email" Type="String" />
<asp:parameter Name="State" Type="String" />
<asp:parameter Name="UserPassword" Type="String" />
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

Changes I made:
1) I added the AdNum as a bound field in the detailsView control so I
could see if it has the correct value (it does)
2) I took out a bunch of extraneous conditions in the WHERE clauses of
the delete and update commands, so now they just test whether Adnum= ?.
This seemed to be sufficient to fix the delete problem so i was sure
update would start working too, but no.
3) I put parens around the WHERE condition of the update and delete
commands to make the syntax exactly like the syntax in the select
command. Seems to be irrelevant to the delete problem.

This has been driving me batty all day. I hate access, but until i can
get this code to work, I don't want to start modifying it to use MSSQL,
which is my ultimate target DBMS for this.

Thanks in advance of any advice -- I'm happy to provide more of the
code or the access table structure or whatever is needed.

-- Ned[/QUOTE]
Click to expand...
 
R

Rod Makin

Thanks Ned - solved the problem for me too:)



Ned Balzer said:
Hi all,

I have encountered a really weird problem -- probably it is obvious to
someone but not to me. I hoep someone can give me a clue.

I am implementing some code straight out of the book "ASP.NET 2.0 for
Dummies". I just copied the code onto an IIS server with the 2.0 .NET
framework and tried to run it. I had to make two changes:
1) changed the path to the supplied access .mdb file in the web.config
file
2) changed the permissions on the .mdb file so that "Users" have write
access.

Here is the weird thing: the application will let me insert a record
into the access table. it will let me delete the record. But it will
not update the record. I can't think of any permissions reason why
insert and delete would work and update wouldn't -- update is just a
special case of insert and delete, right?

I was assuming that the author's code had been tested. I'm not sure if
that's a valid assumption. Initially the code for insert worked but
the code for update and delete did not work. I made a couple of
changes to the update and delete code, and the delete problem was
fixed, but not the update problem. Here is the code for the
DetailsView control and the DataSourceControl:

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px"
Width="100%" AutoGenerateRows="False" DataKeyNames="AdNum"
DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333"
GridLines="None" OnItemUpdated="DetailsView1_ItemUpdated"
OnItemDeleted="DetailsView1_ItemDeleted"
OnItemCommand="DetailsView1_ItemCommand">
<Fields>
<asp:BoundField DataField="AdNum" HeaderText="AdNum"
SortExpression="AdNum" />
<asp:BoundField DataField="Title" HeaderText="Title"
SortExpression="Title" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
<asp:BoundField DataField="Description"
HeaderText="Description" SortExpression="Description" />
<asp:BoundField DataField="Price" HeaderText="Price"
SortExpression="Price" />
<asp:BoundField DataField="Phone" HeaderText="Phone"
SortExpression="Phone" />
<asp:BoundField DataField="Email" HeaderText="Email"
SortExpression="Email" />
<asp:BoundField DataField="State" HeaderText="State"
SortExpression="State" />
<asp:BoundField DataField="UserPassword"
HeaderText="UserPassword" SortExpression="UserPassword" />
<asp:CommandField ButtonType="Button" ShowDeleteButton="True"
ShowEditButton="True" />
</Fields>
<RowStyle BackColor="#FFFBD6" />
<FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
</asp:DetailsView>
&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$
ConnectionStrings:classydbConnectionString %>"
ProviderName="<%$
ConnectionStrings:classydbConnectionString.ProviderName %>"
SelectCommand="SELECT [AdNum], [Title], [Category],
[Description], [Price], [Phone], , [State], [UserPassword] FROM
[Ads] WHERE ([AdNum] = ?)"
UpdateCommand="UPDATE [Ads] SET [Title] = ?, [Category] = ?,
[Description] = ?, [Price] = ?, [Phone] = ?, [Email] = ?, [State] = ?,
[UserPassword] = ? WHERE ([AdNum] = ?)"
DeleteCommand="DELETE FROM [Ads] WHERE ([AdNum] = ?)"<SelectParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="Title" Type="String" />
<asp:parameter Name="Category" Type="String" />
<asp:parameter Name="Description" Type="String" />
<asp:parameter Name="Price" Type="Decimal" />
<asp:parameter Name="Phone" Type="String" />
<asp:parameter Name="Email" Type="String" />
<asp:parameter Name="State" Type="String" />
<asp:parameter Name="UserPassword" Type="String" />
<asp:QueryStringParameter Name="AdNum"
QueryStringField="AdNum" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>

Changes I made:
1) I added the AdNum as a bound field in the detailsView control so I
could see if it has the correct value (it does)
2) I took out a bunch of extraneous conditions in the WHERE clauses of
the delete and update commands, so now they just test whether Adnum= ?.
This seemed to be sufficient to fix the delete problem so i was sure
update would start working too, but no.
3) I put parens around the WHERE condition of the update and delete
commands to make the syntax exactly like the syntax in the select
command. Seems to be irrelevant to the delete problem.

This has been driving me batty all day. I hate access, but until i can
get this code to work, I don't want to start modifying it to use MSSQL,
which is my ultimate target DBMS for this.

Thanks in advance of any advice -- I'm happy to provide more of the
code or the access table structure or whatever is needed.

-- Ned
[/QUOTE]
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

databinding 'system.data.datarowview' does not contain a propertywith the name 1
DetailsView not updating 2
ASP.NET Gridview - Edit (Update) 4
DropDownList not submitting to db 0
SqlDataSource and Optimistic Concurrency Problems 2
GridView not updating in asp.net 2.0 1
Error: The XML page cannot be displayed 0
AJAX + ObjectDataSource Refresh Issue 0

Members online

Total: 75 (members: 1, guests: 74)
Robots: 440

Forum statistics

Threads
473,995
Messages
2,570,230
Members
46,819
Latest member
masterdaster

Latest Threads

Top