Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...
.NET Forum / ASP.NET / General / June 2007

Tip: Looking for answers? Try searching our database.

Gridview not updating the database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Barbara Alderton - 15 Jun 2007 05:08 GMT
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.
Signature

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>

Reply to this Message
Siva M - 15 Jun 2007 06:30 GMT
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."

> 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.
Reply to this Message
Walter Wang [MSFT] - 15 Jun 2007 09:10 GMT
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 (wawang@online.microsoft.com, 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.
Reply to this Message
Barbara Alderton - 15 Jun 2007 13:39 GMT
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.

Signature

Thank-you,
Barbara Alderton

> Hi Barbara,
>
[quoted text clipped - 25 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
Reply to this Message
Walter Wang [MSFT] - 18 Jun 2007 06:25 GMT
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 (wawang@online.microsoft.com, 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.
Reply to this Message

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.