Gridview not updating the database

G

Guest

I setup a simple gridview as a utility just to do some updates, nothing fancy
just wanted easy UI to make updates. When I select ‘Edit’, I get the fields
I want to edit. I edit them and click ‘Update’, the page returns to its
original state (prior to clicking Edit) and no updates occur in the DB. What
am I missing? I included the html code below.
--
Thank-you,
Barbara Alderton

<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="DBNameSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [DBName] FROM
[CNV_CODE_MAPPING]"></asp:SqlDataSource>
<asp:SqlDataSource ID="TableSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [TableName] FROM
[CNV_CODE_MAPPING] WHERE ([DBName] = @DBName)">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="ForeignKeySource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [ForeignKey] FROM
[CNV_CODE_MAPPING] WHERE (([DBName] = @DBName) AND ([TableName] =
@TableName))">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddTables" Name="TableName"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
<asp:SqlDataSource ID="DataGridSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT [Mapping_UID], [DBName], [TableName],
[ForeignKey], [UIDOld], [ValueOld], [DescOld], [CreateDate], [DescNew],
[ValueNew], [UIDNew], [LastChangeDate] FROM [CNV_CODE_MAPPING] WHERE
(([DBName] = @DBName) AND ([TableName] = @TableName) AND ([ForeignKey] =
@ForeignKey))" OldValuesParameterFormatString="original_{0}"
DeleteCommand="DELETE FROM [CNV_CODE_MAPPING] WHERE [Mapping_UID] =
@original_Mapping_UID" InsertCommand="INSERT INTO [CNV_CODE_MAPPING]
([DBName], [TableName], [ForeignKey], [UIDOld], [ValueOld], [DescOld],
[CreateDate], [DescNew], [ValueNew], [UIDNew], [LastChangeDate]) VALUES
(@DBName, @TableName, @ForeignKey, @UIDOld, @ValueOld, @DescOld, @CreateDate,
@DescNew, @ValueNew, @UIDNew, @LastChangeDate)" UpdateCommand="UPDATE
[CNV_CODE_MAPPING] SET [DBName] = @DBName, [TableName] = @TableName,
[ForeignKey] = @ForeignKey, [UIDOld] = @UIDOld, [ValueOld] = @ValueOld,
[DescOld] = @DescOld, [CreateDate] = @CreateDate, [DescNew] = @DescNew,
[ValueNew] = @ValueNew, [UIDNew] = @UIDNew, [LastChangeDate] =
@LastChangeDate WHERE [Mapping_UID] = @original_Mapping_UID">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddTables" Name="TableName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddForeignKeys"
Name="ForeignKey" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:parameter Name="original_Mapping_UID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="DBName" Type="String" />
<asp:parameter Name="TableName" Type="String" />
<asp:parameter Name="ForeignKey" Type="String" />
<asp:parameter Name="UIDOld" Type="Int32" />
<asp:parameter Name="ValueOld" Type="String" />
<asp:parameter Name="DescOld" Type="String" />
<asp:parameter Name="CreateDate" Type="DateTime" />
<asp:parameter Name="DescNew" Type="String" />
<asp:parameter Name="ValueNew" Type="String" />
<asp:parameter Name="UIDNew" Type="Int32" />
<asp:parameter Name="LastChangeDate" Type="DateTime" />
<asp:parameter Name="original_Mapping_UID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:parameter Name="DBName" Type="String" />
<asp:parameter Name="TableName" Type="String" />
<asp:parameter Name="ForeignKey" Type="String" />
<asp:parameter Name="UIDOld" Type="Int32" />
<asp:parameter Name="ValueOld" Type="String" />
<asp:parameter Name="DescOld" Type="String" />
<asp:parameter Name="CreateDate" Type="DateTime" />
<asp:parameter Name="DescNew" Type="String" />
<asp:parameter Name="ValueNew" Type="String" />
<asp:parameter Name="UIDNew" Type="Int32" />
<asp:parameter Name="LastChangeDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>

<table style="width: 800px">
<tr>
<td style="width: 800px">
<table style="width: 790px">
<tr>
<td style="width: 150px">
</td>
<td style="text-align: center; width: 490px">
<span style="font-family: Arial"><strong>Code Mapping
Tool</strong></span></td>
<td style="width: 150px">
</td>
</tr>
<tr>
<td style="height: 21px">
</td>
<td style="height: 21px">
</td>
<td style="height: 21px">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="width: 800px">
<table style="width: 400px">
<tr>
<td style="width: 167px">
<asp:Label ID="Label1" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="DataBase Name:"
Width="120px"></asp:Label></td>
<td style="width: 200px">
<asp:DropDownList ID="ddDataBases"
runat="server" AutoPostBack="True" DataSourceID="DBNameSource"
DataTextField="DBName"
DataValueField="DBName">
</asp:DropDownList></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 167px; height: 20px;">
<asp:Label ID="lblTblTag" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="Table Name:"></asp:Label></td>
<td style="width: 200px; height: 20px;">
<asp:DropDownList ID="ddTables"
runat="server" AutoPostBack="True" DataSourceID="TableSource"
DataTextField="TableName"
DataValueField="TableName">
</asp:DropDownList></td>
<td style="width: 100px; height: 20px;">
</td>
</tr>
<tr>
<td style="width: 167px">
<asp:Label ID="Label2" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="Foreign Key:"></asp:Label></td>
<td style="width: 200px">
<asp:DropDownList ID="ddForeignKeys"
runat="server" AutoPostBack="True" DataSourceID="ForeignKeySource"
DataTextField="ForeignKey"
DataValueField="ForeignKey">
</asp:DropDownList></td>
<td style="width: 100px">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="width: 800px">
<table style="width: 790px">
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid"
BorderWidth="3px" CellPadding="4"
CellSpacing="2" DataSourceID="DataGridSource"
ForeColor="Black">
<FooterStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField
DataField="Mapping_UID" HeaderText="Mapping_UID" InsertVisible="False"
ReadOnly="True"
SortExpression="Mapping_UID" />
<asp:BoundField
DataField="TableName" HeaderText="Table Name" ReadOnly="True"
SortExpression="TableName" >
<FooterStyle Width="50px" />
</asp:BoundField>
<asp:BoundField
DataField="ForeignKey" HeaderText="Foreign Key" ReadOnly="True"
SortExpression="ForeignKey" />
<asp:BoundField DataField="UIDOld"
HeaderText="Old UID" ReadOnly="True" SortExpression="UIDOld" />
<asp:BoundField DataField="ValueOld"
HeaderText="Old Value" ReadOnly="True" SortExpression="ValueOld" />
<asp:BoundField DataField="DescOld"
HeaderText="Old Desc" ReadOnly="True" SortExpression="DescOld" />
<asp:BoundField
DataField="CreateDate" DataFormatString="{0:d}" HeaderText="Create Date"
HtmlEncode="False"
ReadOnly="True" SortExpression="CreateDate" />
<asp:BoundField DataField="UIDNew"
HeaderText="UIDNew" SortExpression="UIDNew" />
<asp:BoundField DataField="ValueNew"
HeaderText="New Value" SortExpression="ValueNew" />
<asp:BoundField DataField="DescNew"
HeaderText="New Desc" SortExpression="DescNew" />
<asp:BoundField
DataField="LastChangeDate" HeaderText="Last Change Date"
SortExpression="LastChangeDate" />
<asp:CommandField
ShowEditButton="True" />
</Columns>
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099"
Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC"
ForeColor="Black" HorizontalAlign="Left" />
<HeaderStyle BackColor="Black"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
ForeColor="White" />
</asp:GridView>
</td>
</tr>
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
</td>
</tr>
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
 
G

Guest

It looks like you are not setting DataKeyNames property for the GridView.
This property represents the primary key (or comma separated if composite PK)
for the table bound to the grid and GridView needs this info for automatic
update or delete.

Also from MSDN: "You must set the DataKeyNames property for the automatic
updating and deleting features of the GridView control to work. The values of
these key fields are passed to the data source control in order to match the
row to update or delete."

Barbara Alderton said:
I setup a simple gridview as a utility just to do some updates, nothing fancy
just wanted easy UI to make updates. When I select ‘Edit’, I get the fields
I want to edit. I edit them and click ‘Update’, the page returns to its
original state (prior to clicking Edit) and no updates occur in the DB. What
am I missing? I included the html code below.
--
Thank-you,
Barbara Alderton

<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="DBNameSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [DBName] FROM
[CNV_CODE_MAPPING]"></asp:SqlDataSource>
<asp:SqlDataSource ID="TableSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [TableName] FROM
[CNV_CODE_MAPPING] WHERE ([DBName] = @DBName)">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="ForeignKeySource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT DISTINCT [ForeignKey] FROM
[CNV_CODE_MAPPING] WHERE (([DBName] = @DBName) AND ([TableName] =
@TableName))">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddTables" Name="TableName"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</div>
<asp:SqlDataSource ID="DataGridSource" runat="server"
ConnectionString="<%$ ConnectionStrings:CONV_ADMINConnectionString %>"
SelectCommand="SELECT [Mapping_UID], [DBName], [TableName],
[ForeignKey], [UIDOld], [ValueOld], [DescOld], [CreateDate], [DescNew],
[ValueNew], [UIDNew], [LastChangeDate] FROM [CNV_CODE_MAPPING] WHERE
(([DBName] = @DBName) AND ([TableName] = @TableName) AND ([ForeignKey] =
@ForeignKey))" OldValuesParameterFormatString="original_{0}"
DeleteCommand="DELETE FROM [CNV_CODE_MAPPING] WHERE [Mapping_UID] =
@original_Mapping_UID" InsertCommand="INSERT INTO [CNV_CODE_MAPPING]
([DBName], [TableName], [ForeignKey], [UIDOld], [ValueOld], [DescOld],
[CreateDate], [DescNew], [ValueNew], [UIDNew], [LastChangeDate]) VALUES
(@DBName, @TableName, @ForeignKey, @UIDOld, @ValueOld, @DescOld, @CreateDate,
@DescNew, @ValueNew, @UIDNew, @LastChangeDate)" UpdateCommand="UPDATE
[CNV_CODE_MAPPING] SET [DBName] = @DBName, [TableName] = @TableName,
[ForeignKey] = @ForeignKey, [UIDOld] = @UIDOld, [ValueOld] = @ValueOld,
[DescOld] = @DescOld, [CreateDate] = @CreateDate, [DescNew] = @DescNew,
[ValueNew] = @ValueNew, [UIDNew] = @UIDNew, [LastChangeDate] =
@LastChangeDate WHERE [Mapping_UID] = @original_Mapping_UID">
<SelectParameters>
<asp:ControlParameter ControlID="ddDataBases" Name="DBName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddTables" Name="TableName"
PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="ddForeignKeys"
Name="ForeignKey" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:parameter Name="original_Mapping_UID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="DBName" Type="String" />
<asp:parameter Name="TableName" Type="String" />
<asp:parameter Name="ForeignKey" Type="String" />
<asp:parameter Name="UIDOld" Type="Int32" />
<asp:parameter Name="ValueOld" Type="String" />
<asp:parameter Name="DescOld" Type="String" />
<asp:parameter Name="CreateDate" Type="DateTime" />
<asp:parameter Name="DescNew" Type="String" />
<asp:parameter Name="ValueNew" Type="String" />
<asp:parameter Name="UIDNew" Type="Int32" />
<asp:parameter Name="LastChangeDate" Type="DateTime" />
<asp:parameter Name="original_Mapping_UID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:parameter Name="DBName" Type="String" />
<asp:parameter Name="TableName" Type="String" />
<asp:parameter Name="ForeignKey" Type="String" />
<asp:parameter Name="UIDOld" Type="Int32" />
<asp:parameter Name="ValueOld" Type="String" />
<asp:parameter Name="DescOld" Type="String" />
<asp:parameter Name="CreateDate" Type="DateTime" />
<asp:parameter Name="DescNew" Type="String" />
<asp:parameter Name="ValueNew" Type="String" />
<asp:parameter Name="UIDNew" Type="Int32" />
<asp:parameter Name="LastChangeDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>

<table style="width: 800px">
<tr>
<td style="width: 800px">
<table style="width: 790px">
<tr>
<td style="width: 150px">
</td>
<td style="text-align: center; width: 490px">
<span style="font-family: Arial"><strong>Code Mapping
Tool</strong></span></td>
<td style="width: 150px">
</td>
</tr>
<tr>
<td style="height: 21px">
</td>
<td style="height: 21px">
</td>
<td style="height: 21px">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="width: 800px">
<table style="width: 400px">
<tr>
<td style="width: 167px">
<asp:Label ID="Label1" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="DataBase Name:"
Width="120px"></asp:Label></td>
<td style="width: 200px">
<asp:DropDownList ID="ddDataBases"
runat="server" AutoPostBack="True" DataSourceID="DBNameSource"
DataTextField="DBName"
DataValueField="DBName">
</asp:DropDownList></td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 167px; height: 20px;">
<asp:Label ID="lblTblTag" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="Table Name:"></asp:Label></td>
<td style="width: 200px; height: 20px;">
<asp:DropDownList ID="ddTables"
runat="server" AutoPostBack="True" DataSourceID="TableSource"
DataTextField="TableName"
DataValueField="TableName">
</asp:DropDownList></td>
<td style="width: 100px; height: 20px;">
</td>
</tr>
<tr>
<td style="width: 167px">
<asp:Label ID="Label2" runat="server"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
Text="Foreign Key:"></asp:Label></td>
<td style="width: 200px">
<asp:DropDownList ID="ddForeignKeys"
runat="server" AutoPostBack="True" DataSourceID="ForeignKeySource"
DataTextField="ForeignKey"
DataValueField="ForeignKey">
</asp:DropDownList></td>
<td style="width: 100px">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style="width: 800px">
<table style="width: 790px">
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False"
BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid"
BorderWidth="3px" CellPadding="4"
CellSpacing="2" DataSourceID="DataGridSource"
ForeColor="Black">
<FooterStyle BackColor="#CCCCCC" />
<Columns>
<asp:BoundField
DataField="Mapping_UID" HeaderText="Mapping_UID" InsertVisible="False"
ReadOnly="True"
SortExpression="Mapping_UID" />
<asp:BoundField
DataField="TableName" HeaderText="Table Name" ReadOnly="True"
SortExpression="TableName" >
<FooterStyle Width="50px" />
</asp:BoundField>
<asp:BoundField
DataField="ForeignKey" HeaderText="Foreign Key" ReadOnly="True"
SortExpression="ForeignKey" />
<asp:BoundField DataField="UIDOld"
HeaderText="Old UID" ReadOnly="True" SortExpression="UIDOld" />
<asp:BoundField DataField="ValueOld"
HeaderText="Old Value" ReadOnly="True" SortExpression="ValueOld" />
<asp:BoundField DataField="DescOld"
HeaderText="Old Desc" ReadOnly="True" SortExpression="DescOld" />
<asp:BoundField
DataField="CreateDate" DataFormatString="{0:d}" HeaderText="Create Date"
HtmlEncode="False"
ReadOnly="True" SortExpression="CreateDate" />
<asp:BoundField DataField="UIDNew"
HeaderText="UIDNew" SortExpression="UIDNew" />
<asp:BoundField DataField="ValueNew"
HeaderText="New Value" SortExpression="ValueNew" />
<asp:BoundField DataField="DescNew"
HeaderText="New Desc" SortExpression="DescNew" />
<asp:BoundField
DataField="LastChangeDate" HeaderText="Last Change Date"
SortExpression="LastChangeDate" />
<asp:CommandField
ShowEditButton="True" />
</Columns>
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099"
Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC"
ForeColor="Black" HorizontalAlign="Left" />
<HeaderStyle BackColor="Black"
Font-Bold="True" Font-Names="Arial" Font-Size="11pt"
ForeColor="White" />
</asp:GridView>
</td>
</tr>
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
</td>
</tr>
<tr>
<td style="width: 50px">
</td>
<td style="width: 740px">
</td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
Click to expand...
 
W

Walter Wang [MSFT]

Hi Barbara,

As Siva pointed out, you need to set GridView.DataKeyNames to the PK of the
database table, in your case, which is the "Mapping_UID":

<asp:GridView DataKeyNames="Mapping_UID" ...


Please try this and let us know the result. Thanks.


Reference:

#GridView.DataKeyNames Property (System.Web.UI.WebControls)
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.
datakeynames.aspx
You must set the DataKeyNames property for the automatic updating and
deleting features of the GridView control to work. The values of these key
fields are passed to the data source control in order to match the row to
update or delete.



Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
G

Guest

I set the datakeynames value and I then got another error regarding my update
statement. I modified to just update the editable fields and all worked
fine. This is my first exposure to the new gridview in asp.net 2.0,
previously worked with the datagrid in 1.1. I don't usually depend on auto
generated items in .net, there is usually something I need to add or change.
This was just supposed to be a quick little page I setup for myself to make
another task easier.

Thanks for the quick responses.
 
W

Walter Wang [MSFT]

Hi Barbara,

If you're new to the ASP.NET 2.0's new data related controls, you may find
following resource useful:

#Working with Data and ASP.NET 2.0: ASP.NET Web: The Official Microsoft
ASP.NET 2.0 Site
http://www.asp.net/learn/dataaccess/default.aspx?tabid=63


Regards,
Walter Wang ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 

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
ASP.NET Gridview - Edit (Update) 4
DetailsView not updating 2
GridView not updating changes 5
GridView-FormView (Master/Detail) Control not working... 1
GridView Not Updating 0
updating a gridview 0
How do I get rid of the select button in a gridview? 1

Members online

Total: 304 (members: 2, guests: 302)
Robots: 271

Forum statistics

Threads
473,968
Messages
2,570,149
Members
46,695
Latest member
StanleyDri

Latest Threads

Top