Gridview update not working for AccessDataSource
Morris Neuman
I am trying to update a row in a GridView that uses a table with a join to
another table and using hidden column info for a parameter. I have set the
DataKeyNames for the GridView.
The page is working when I bind the GridView (GridView1) to my SqlDataDource
(SqlDataSource1), however it does not work when the GridView is bound to my
AccessDataSource (AccessDataSource1).
The page accesses 2 tables as follows:
1) Attendant which has AttendantID Text PK, TimeOfDay Int PK, Extension to
Dial Text.
2) AttTimeOfDayXref which has TimeOfDatNumber Int PK, TimeOfDayDescription
Text.
There is an Inner Join between the tables on the TimeOfDay and
TimeOfDayNumber fields.
My GridView is on the Content Page of a Master page. The GridView shows
fields from both tables
The source code below is set-up where DataSourceID="AccessDataSource1". You
can change to point to SqlDataSource to see it working.
Any help would be very much appreciated.
-------------------------------Source
Code------------------------------------------
<%@ Page Language="C#" MasterPageFile="~/MasterPage1.master"
Title="Admin-Manage Distribution List" %>
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
}
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
<br />
<strong><span style="color: #000080; font-family: Verdana">
Manage Attendant Records<br />
</span></strong> <asp:Menu ID="Menu2" runat="server" BackColor="#FFC080"
BorderColor="Navy"
BorderStyle="Inset" BorderWidth="1px" Font-Bold="False"
Font-Strikeout="False"
Font-Underline="False" ForeColor="Navy" Orientation="Horizontal">
<StaticMenuStyle HorizontalPadding="5px" />
<StaticMenuItemStyle BackColor="#FFC080" Font-Names="verdana"
Font-Size="8pt"
HorizontalPadding="15px" />
<StaticHoverStyle Font-Bold="True" />
<Items>
<asp:MenuItem NavigateUrl="~/Default.aspx" Text="Message Center"
Value="Message Center" ToolTip="Review Mailboxes for logged in web account">
</asp:MenuItem>
<asp:MenuItem
NavigateUrl="~/systemadminOnly/ManageCALLMaster.aspx" Text="Manage CALLMaster"
Value="Manage CALLMaster" ToolTip="Manage CALLMaster
databases"></asp:MenuItem>
<asp:MenuItem
NavigateUrl="~/systemadminOnly/ManageWebAccounts.aspx" Text="Manage Web
Accounts"
Value="Manage Web Accounts" ToolTip="Manage Web
Accounts"></asp:MenuItem>
<asp:MenuItem
NavigateUrl="http://localhost/aspnetadmin/default.aspx?applicationPhysicalPath=C:\Inetpub\wwwroot\CMWebManager\&applicationUrl=/TestAspnetConfig"
Text="Manage Roles/Web Account via ASP.Net" Value="Manage
via ASP.Net" ToolTip="Must be locally connected to web server"></asp:MenuItem>
</Items>
</asp:Menu>
<span style="font-family: Verdana; font-size: 10pt; color: navy;">
<br />
<br />
All Attendant Records </span>
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" BorderColor="#FFC080"
BorderStyle="Solid" BorderWidth="1px"
DataKeyNames="AttendantID,TimeOfDay,TimeOfDayNumber,TimeOfDayDescription"
DataSourceID="AccessDataSource1"
Font-Size="8pt" CellPadding="5" CellSpacing="1" Font-Names="Verdana"
ForeColor="Navy">
<Columns>
<asp:CommandField ShowEditButton="True">
<ControlStyle Font-Bold="False" ForeColor="DarkSlateGray" />
<HeaderStyle BackColor="Lavender" />
<ItemStyle BackColor="Lavender" BorderColor="SlateGray"
BorderStyle="Inset" BorderWidth="2px" />
</asp:CommandField>
<asp:TemplateField>
<ControlStyle Font-Bold="False" ForeColor="DarkSlateGray" />
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server"
OnClientClick="return confirm('Are you sure you want
to delete this Attendant Record?');"
CommandName="Delete">Delete</asp:LinkButton>
</ItemTemplate>
<ItemStyle BackColor="Lavender" BorderColor="SlateGray"
BorderStyle="Inset" BorderWidth="2px" />
<HeaderStyle BackColor="Lavender" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Attendant ID"
SortExpression="AttendantID">
<EditItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%#
Bind("AttendantID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%#
Bind("AttendantID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Time Of Day"
SortExpression="TimeOfDay"
Visible="False">
<EditItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%#
Bind("TimeOfDay") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%#
Bind("TimeOfDay") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="TimeOfDayNumber"
HeaderText="TimeOfDayNumber"
Visible="False" />
<asp:TemplateField HeaderText="Time Of Day Desc"
SortExpression="TimeOfDayDescription">
<EditItemTemplate>
<asp:Label ID="TimeOfDayDescription" runat="server"
Text='<%# Bind("TimeOfDayDescription")
%>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="TimeOfDayDescription" runat="server"
Text='<%# Bind("TimeOfDayDescription")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ExtensionToDial" HeaderText="Number
To Dial"
SortExpression="ExtensionToDial" />
</Columns>
<RowStyle BackColor="BlanchedAlmond" BorderStyle="None" />
<EmptyDataTemplate>
No mailboxes set-up for Member
<asp:LoginName ID="LoginName2" runat="server" />
</EmptyDataTemplate>
<HeaderStyle BackColor="#FFC080" Font-Bold="False"
BorderColor="#FFC080" />
<AlternatingRowStyle BackColor="SeaShell" />
<PagerSettings Mode="NumericFirstLast" />
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="<%$ ConnectionStrings:CALLMasterMDB %>"
SelectCommand='SELECT Attendant.AttendantID, Attendant.TimeOfDay,
AttTimeOfDayXref.TimeOfDayDescription, Attendant.ExtensionToDial,
AttTimeOfDayXref.TimeOfDayNumber FROM (Attendant INNER JOIN AttTimeOfDayXref
ON Attendant.TimeOfDay = AttTimeOfDayXref.TimeOfDayNumber) ORDER BY
Attendant.AttendantID'
DeleteCommand="DELETE FROM Attendant WHERE (AttendantID = ?) AND
(TimeOfDay = ?)"
UpdateCommand="UPDATE Attendant SET Attendant.ExtensionToDial = ?
WHERE (((Attendant.AttendantID)=?) AND ((Attendant.TimeOfDay)=(Select
[TimeOfDayNumber] From AttTimeOfDayXref INNER JOIN Attendant ON
AttTimeOfDayXref.TimeOfDayNumber = Attendant.TimeOfDay Where
[TimeOfDayDescription]=?)));
">
<DeleteParameters>
<asp:Parameter Name="AttendantID" Type="String" />
<asp:Parameter Name="TimeOfDay" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ExtensionToDial" />
<asp:ControlParameter ControlID="GridView1" Name="AttendantID"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="GridView1"
Name="TimeOfDayDescription"
PropertyName="SelectedValue" Type="String" />
</UpdateParameters>
</asp:AccessDataSource>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:CallMasterSQLConnectionString %>"
SelectCommand="SELECT Attendant.AttendantID, Attendant.TimeOfDay,
AttTimeOfDayXref.TimeOfDayNumber, AttTimeOfDayXref.TimeOfDayDescription,
Attendant.ExtensionToDial FROM Attendant INNER JOIN AttTimeOfDayXref ON
Attendant.TimeOfDay = AttTimeOfDayXref.TimeOfDayNumber ORDER BY
Attendant.AttendantID"
DeleteCommand="DELETE FROM Attendant WHERE (AttendantID =
@AttendantID) AND (TimeOfDay = (SELECT TimeOfDayNumber FROM AttTimeOfDayXref
WHERE (TimeOfDayDescription = @TimeOfDayDescription)))"
UpdateCommand="UPDATE Attendant SET ExtensionToDial =
@ExtensionToDial FROM Attendant INNER JOIN AttTimeOfDayXref AS
AttTimeOfDayXref_1 ON Attendant.TimeOfDay =
AttTimeOfDayXref_1.TimeOfDayNumber WHERE (Attendant.AttendantID =
@AttendantID) AND (Attendant.TimeOfDay = (SELECT TimeOfDayNumber FROM
AttTimeOfDayXref WHERE (TimeOfDayDescription = @TimeOfDayDescription)))">
<DeleteParameters>
<asp:Parameter Name="AttendantID" />
<asp:Parameter Name="TimeOfDayDescription" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ExtensionToDial" />
<asp:ControlParameter ControlID="GridView1" Name="AttendantID"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="GridView1"
Name="TimeOfDayDescription"
PropertyName="SelectedValue" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:AccessDataSource ID="AccessDataSource3" runat="server"
DataFile="C:\Program Files\CallMaster\Data\Callmaster.mdb"
SelectCommand="SELECT [TimeOfDayNumber], [TimeOfDayDescription] FROM
[AttTimeOfDayXref] ORDER BY [TimeOfDayNumber]">
</asp:AccessDataSource>
<asp:SqlDataSource ID="SqlDataSource3" runat="server"
ConnectionString="<%$ ConnectionStrings:CallMasterSQLConnectionString %>"
SelectCommand="SELECT [TimeOfDayNumber], [TimeOfDayDescription] FROM
[AttTimeOfDayXref] ORDER BY [TimeOfDayNumber]">
</asp:SqlDataSource>
<br />
<br />
<br />
<br />
<asp:HyperLink ID="HyperLink7" runat="server" Font-Names="Verdana"
Font-Size="8pt"
NavigateUrl="~/Default.aspx" ToolTip="Review Account Mailboxes,
Messages, Notifications"
Width="149px">Message Center</asp:HyperLink>
<asp:HyperLink ID="HyperLink6" runat="server"
Font-Names="Verdana" Font-Size="8pt"
NavigateUrl="~/systemadminOnly/ManageCALLMaster.aspx"
ToolTip="Maintain CALLMaster Database" Visible='<%#
User.IsInRole("systemadmin") %>'
Width="170px">Manage CALLMaster</asp:HyperLink>
<asp:HyperLink ID="HyperLink1" runat="server" Font-Names="Verdana"
Font-Size="8pt"
NavigateUrl="~/systemadminOnly/ManageWebAccounts.aspx"
Width="196px">Manage Web Accounts</asp:HyperLink>
<asp:HyperLink ID="HyperLink8" runat="server" Font-Names="Verdana"
Font-Size="8pt"
NavigateUrl="http://localhost/aspnetadmin/default.aspx?applicationPhysicalPath=C:\Inetpub\wwwroot\CMWebManager\&applicationUrl=/TestAspnetConfig"
ToolTip="From Local Machine ONLY" Width="252px">Manage Roles/Web
Account via ASP.Net</asp:HyperLink><br />
<br />
<br />
</asp:Content>
--
Thanks
Morris
Allen Chen [MSFT]
From your description the AccessDataSource doesn't work while the
SqlDataSource does, right?
Please pay attention to this line:
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="<%$ ConnectionStrings:CALLMasterMDB %>"
From above code I guess you specify the connection string to the DataFile
property of the AccessDataSource, correct? Actually this property is used
to specify the location of the data base file instead of the connection
string:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.accessdata
source.datafile.aspx
If my guess is correct please change the DataFile to point to the location
of the data base file. If it still doesn't work please send me a demo
project that can reproduce this problem. My email is
v-al...@microsoft.com. Looking forward to your reply.
Regards,
Allen Chen
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msd...@microsoft.com.
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Gridview update not working for AccessDataSource
| thread-index: AckoDdPiLcjJxLe4TU2qBoQOUjIhmQ==
| From: =?Utf-8?B?TW9ycmlzIE5ldW1hbg==?= <Mor...@online.nospam>
| Subject: Gridview update not working for AccessDataSource
| Date: Mon, 6 Oct 2008 16:47:01 -0700
| Lines: 266
| Message-ID: <AEE7431E-6BA1-4CB5...@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet.webcontrols:4044
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.webcontrols
Allen Chen [MSFT]
I've received an email from your colleague and created a same database to
test. The code helps me understand your scenario more clearly. Here's a
working sample:
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" BorderColor="#FFC080"
BorderStyle="Solid" BorderWidth="1px"
DataKeyNames="AttendantID,TimeOfDay"
<asp:BoundField DataField="TimeOfDayNumber"
HeaderText="TimeOfDayNumber"
Visible="False" />
<asp:TemplateField HeaderText="Time Of Day Desc"
SortExpression="TimeOfDayDescription">
<EditItemTemplate>
<asp:Label ID="TimeOfDayDescription" runat="server"
Text='<%# Eval("TimeOfDayDescription")
%>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="TimeOfDayDescription" runat="server"
Text='<%# Eval("TimeOfDayDescription")
%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ExtensionToDial" HeaderText="Number
To Dial"
SortExpression="ExtensionToDial" />
</Columns>
<RowStyle BackColor="BlanchedAlmond" BorderStyle="None" />
<EmptyDataTemplate>
No mailboxes set-up for Member
<asp:LoginName ID="LoginName2" runat="server" />
</EmptyDataTemplate>
<HeaderStyle BackColor="#FFC080" Font-Bold="False"
BorderColor="#FFC080" />
<AlternatingRowStyle BackColor="SeaShell" />
<PagerSettings Mode="NumericFirstLast" />
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/Database4.mdb"
SelectCommand='SELECT Attendant.AttendantID, Attendant.TimeOfDay,
AttTimeOfDayXref.TimeOfDayDescription, Attendant.ExtensionToDial,
AttTimeOfDayXref.TimeOfDayNumber FROM (Attendant INNER JOIN
AttTimeOfDayXref ON Attendant.TimeOfDay = AttTimeOfDayXref.TimeOfDayNumber)
ORDER BY Attendant.AttendantID'
DeleteCommand="DELETE FROM Attendant WHERE (AttendantID = ?) AND
(TimeOfDay = ?)"
UpdateCommand="UPDATE Attendant SET Attendant.ExtensionToDial = ?
WHERE (((Attendant.AttendantID)=?) AND ((Attendant.TimeOfDay)=?));"
>
<DeleteParameters>
<asp:Parameter Name="AttendantID" Type="String" />
<asp:Parameter Name="TimeOfDay" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ExtensionToDial" />
<asp:ControlParameter ControlID="GridView1" Name="AttendantID"
PropertyName="SelectedValue"/>
</UpdateParameters>
</asp:AccessDataSource>
You may note some changes in my code comparing to yours. First one is the
DataKeyNames. I think we only need two.The second is, I used
Eval("TimeOfDayDescription") instead of Bind.It's a good practice to use
Bind when it's really need.The third one is to change the UpdateCommand.
The last one is removing some redundant parameters in the UpdateParameters.
BTW,please have a look at this tutorial about Styles, Themes, and Skins. It
will help to write neat aspx code and save your time:
http://quickstarts.asp.net/QuickStartv20/aspnet/doc/themes/default.aspx
Please have a try and let me know if it works.
Regards,
Allen Chen
Microsoft Online Community Support
--------------------
| Thread-Topic: Gridview update not working for AccessDataSource
| thread-index: AckoDdPiLcjJxLe4TU2qBoQOUjIhmQ==
| From: =?Utf-8?B?TW9ycmlzIE5ldW1hbg==?= <Mor...@online.nospam>
| Subject: Gridview update not working for AccessDataSource
| Date: Mon, 6 Oct 2008 16:47:01 -0700
| Lines: 266
| Message-ID: <AEE7431E-6BA1-4CB5...@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet.webcontrols:4044
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet.webcontrols
|
Morris Neuman
Just for the future so I can learn, was it the change from Bind to Eval that
solved it and if so why?
Also if I have a field declared in the DataKeyNames then I do not need to
declare as a parameter in the db query?
Once again I appreciate your help.
--
Thanks
Morris
"Allen Chen [MSFT]" wrote:
Allen Chen [MSFT]
Glad to know you've solved this issue. As to your questions:
Q: Was it the change from Bind to Eval that solved it and if so why?
A: No. I think I need to clarify when to use Bind. Generally speaking Bind
is used when we need to provide a parameter in updating or inserting. In
your case, the TimeOfDayDescription field is not needed for the
UpdateCommand. So it's a good practice to use Eval. Otherwise a redundant
parameter will be provided, which can cause some problems if we write code
carelessly.
Q: If I have a field declared in the DataKeyNames then I do not need to
declare as a parameter in the db query?
A: Yes. The field declared in the DataKeyNames will be assigned to the
parameters automatically. Another note is, the fields in the DataKeyNames
are readonly. They cannot be updated.
Please feel free to ask if you need further assistance.
Regards,
Allen Chen
Microsoft Online Community Support
--------------------
| Thread-Topic: Gridview update not working for AccessDataSource
| thread-index: AckpUGRScS5V/aaxRWKu9Q1O0mgR+Q==
| X-WBNR-Posting-Host: 65.55.12.11
| From: =?Utf-8?B?TW9ycmlzIE5ldW1hbg==?= <Mor...@online.nospam>
| References: <AEE7431E-6BA1-4CB5...@microsoft.com>
<mfVi0$OKJHA...@TK2MSFTNGHUB02.phx.gbl>
| Subject: RE: Gridview update not working for AccessDataSource
| Date: Wed, 8 Oct 2008 07:16:01 -0700
| Lines: 316
| Message-ID: <02BDFAB7-4ADB-4618...@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet.webcontrols:4055
Allen Chen [MSFT]
Do you have further questions?
Regards,
Allen Chen
Microsoft Online Support
--------------------
| Thread-Topic: Gridview update not working for AccessDataSource
| thread-index: AckpUGRScS5V/aaxRWKu9Q1O0mgR+Q==
| X-WBNR-Posting-Host: 65.55.12.11
| From: =?Utf-8?B?TW9ycmlzIE5ldW1hbg==?= <Mor...@online.nospam>
| References: <AEE7431E-6BA1-4CB5...@microsoft.com>
<mfVi0$OKJHA...@TK2MSFTNGHUB02.phx.gbl>
| Subject: RE: Gridview update not working for AccessDataSource
| Date: Wed, 8 Oct 2008 07:16:01 -0700
| Lines: 316
| Message-ID: <02BDFAB7-4ADB-4618...@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.3119
| Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet.webcontrols:4055