Solved

Why won’t updates save in my asp.net 3.5 GridView control?

Posted on 2012-04-13
8
259 Views
Last Modified: 2012-04-13
Hello,

I am using Visual Web Developer 2008. My data source is MS SQL Server 2008 Express.

I have an SQLDataSource where I have selected all of the columns from one table, including the primary key.  In the data source setup, I went into the Advanced tab and selected the option to generate Updates, Deletions, etc. The GridView is tied to this data source and the data appears, but when I do an update it does not save.

I have selected the “Enable Editing” check box from the GridView’s task list.  

Oddly, I have another data source and Gridview on the same aspx page, and this Gridview saves updates just fine. I have compared all the settings that I can find and have compared the code and can’t see a difference between them that would cause this. But then again, I'm a total beginner.

One thing I don’t understand is that on the GridView which is saving updates, all of the cells (in design view) have a value of Databound, but I have values of “abc” for text fields, for example, in the GridView that does not save updates. I’m not sure what this means but it is one obvious difference that I have noticed. I don't know what I did to cause the other GridView to have the Databound values in the fields.

I greatly appreciate your assistance.

Riverwalk

<%@ Page Language="VB" Debug="true" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            font-family: Arial;
            font-size: large;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div class="style1"><b>IMS Customer Management</b></div>
    <p></p>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:CustomersConnectionString %>" 
        DeleteCommand="DELETE FROM [Contacts] WHERE [Contact_ID] = @original_Contact_ID AND (([Title] = @original_Title) OR ([Title] IS NULL AND @original_Title IS NULL)) AND (([First_Name] = @original_First_Name) OR ([First_Name] IS NULL AND @original_First_Name IS NULL)) AND (([Last_Name] = @original_Last_Name) OR ([Last_Name] IS NULL AND @original_Last_Name IS NULL)) AND (([Work_Phone] = @original_Work_Phone) OR ([Work_Phone] IS NULL AND @original_Work_Phone IS NULL)) AND (([Cell_Phone] = @original_Cell_Phone) OR ([Cell_Phone] IS NULL AND @original_Cell_Phone IS NULL)) AND (([Email] = @original_Email) OR ([Email] IS NULL AND @original_Email IS NULL)) AND (([Department] = @original_Department) OR ([Department] IS NULL AND @original_Department IS NULL))" 
        InsertCommand="INSERT INTO [Contacts] ([Title], [First_Name], [Last_Name], [Work_Phone], [Cell_Phone], [Email], [Department]) VALUES (@Title, @First_Name, @Last_Name, @Work_Phone, @Cell_Phone, @Email, @Department)" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT [Contact_ID], [Title], [First_Name], [Last_Name], [Work_Phone], [Cell_Phone], [Email], [Department] FROM [Contacts]" 
        
        UpdateCommand="UPDATE [Contacts] SET [Title] = @Title, [First_Name] = @First_Name, [Last_Name] = @Last_Name, [Work_Phone] = @Work_Phone, [Cell_Phone] = @Cell_Phone, [Email] = @Email, [Department] = @Department WHERE [Contact_ID] = @original_Contact_ID AND (([Title] = @original_Title) OR ([Title] IS NULL AND @original_Title IS NULL)) AND (([First_Name] = @original_First_Name) OR ([First_Name] IS NULL AND @original_First_Name IS NULL)) AND (([Last_Name] = @original_Last_Name) OR ([Last_Name] IS NULL AND @original_Last_Name IS NULL)) AND (([Work_Phone] = @original_Work_Phone) OR ([Work_Phone] IS NULL AND @original_Work_Phone IS NULL)) AND (([Cell_Phone] = @original_Cell_Phone) OR ([Cell_Phone] IS NULL AND @original_Cell_Phone IS NULL)) AND (([Email] = @original_Email) OR ([Email] IS NULL AND @original_Email IS NULL)) AND (([Department] = @original_Department) OR ([Department] IS NULL AND @original_Department IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_Contact_ID" Type="Int32" />
            <asp:Parameter Name="original_Title" Type="String" />
            <asp:Parameter Name="original_First_Name" Type="String" />
            <asp:Parameter Name="original_Last_Name" Type="String" />
            <asp:Parameter Name="original_Work_Phone" Type="String" />
            <asp:Parameter Name="original_Cell_Phone" Type="String" />
            <asp:Parameter Name="original_Email" Type="String" />
            <asp:Parameter Name="original_Department" Type="String" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="Title" Type="String" />
            <asp:Parameter Name="First_Name" Type="String" />
            <asp:Parameter Name="Last_Name" Type="String" />
            <asp:Parameter Name="Work_Phone" Type="String" />
            <asp:Parameter Name="Cell_Phone" Type="String" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="Department" Type="String" />
            <asp:Parameter Name="original_Contact_ID" Type="Int32" />
            <asp:Parameter Name="original_Title" Type="String" />
            <asp:Parameter Name="original_First_Name" Type="String" />
            <asp:Parameter Name="original_Last_Name" Type="String" />
            <asp:Parameter Name="original_Work_Phone" Type="String" />
            <asp:Parameter Name="original_Cell_Phone" Type="String" />
            <asp:Parameter Name="original_Email" Type="String" />
            <asp:Parameter Name="original_Department" Type="String" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="Title" Type="String" />
            <asp:Parameter Name="First_Name" Type="String" />
            <asp:Parameter Name="Last_Name" Type="String" />
            <asp:Parameter Name="Work_Phone" Type="String" />
            <asp:Parameter Name="Cell_Phone" Type="String" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="Department" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AllowSorting="True" AutoGenerateColumns="False" CellPadding="8" 
        DataKeyNames="Contact_ID" DataSourceID="SqlDataSource1" ForeColor="#333333" 
        GridLines="None" ShowFooter="True" style="text-align: left">
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="Contact_ID" HeaderText="ID" 
                InsertVisible="False" ReadOnly="True" SortExpression="Contact_ID" >
            <HeaderStyle HorizontalAlign="Left" Width="0px" />
            </asp:BoundField>
            <asp:BoundField DataField="First_Name" HeaderText="First" 
                SortExpression="First_Name" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Last_Name" HeaderText="Last" 
                SortExpression="Last_Name" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Work_Phone" HeaderText="Work_Phone" 
                SortExpression="Work_Phone" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Cell_Phone" HeaderText="Cell_Phone" 
                SortExpression="Cell_Phone" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Email" HeaderText="Email" 
                SortExpression="Email" >
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
            <asp:BoundField DataField="Department" HeaderText="Department" 
                SortExpression="Department" />
        </Columns>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:CustomersConnectionString %>" 
        DeleteCommand="DELETE FROM [Customers] WHERE [Customer_ID] = @original_Customer_ID AND (([Customer_Name] = @original_Customer_Name) OR ([Customer_Name] IS NULL AND @original_Customer_Name IS NULL)) AND (([Date_First_Installed] = @original_Date_First_Installed) OR ([Date_First_Installed] IS NULL AND @original_Date_First_Installed IS NULL)) AND (([Unknown_Part_Of_First_Installed_Date] = @original_Unknown_Part_Of_First_Installed_Date) OR ([Unknown_Part_Of_First_Installed_Date] IS NULL AND @original_Unknown_Part_Of_First_Installed_Date IS NULL)) AND (([ExhibitA_Evidence_Version] = @original_ExhibitA_Evidence_Version) OR ([ExhibitA_Evidence_Version] IS NULL AND @original_ExhibitA_Evidence_Version IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([County] = @original_County) OR ([County] IS NULL AND @original_County IS NULL)) AND (([State] = @original_State) OR ([State] IS NULL AND @original_State IS NULL)) AND (([Zip] = @original_Zip) OR ([Zip] IS NULL AND @original_Zip IS NULL))" 
        InsertCommand="INSERT INTO [Customers] ([Customer_Name], [Date_First_Installed], [Unknown_Part_Of_First_Installed_Date], [ExhibitA_Evidence_Version], [Address], [City], [County], [State], [Zip]) VALUES (@Customer_Name, @Date_First_Installed, @Unknown_Part_Of_First_Installed_Date, @ExhibitA_Evidence_Version, @Address, @City, @County, @State, @Zip)" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT [Customer_ID], [Customer_Name], [Date_First_Installed], [Unknown_Part_Of_First_Installed_Date], [ExhibitA_Evidence_Version], [Address], [City], [County], [State], [Zip] FROM [Customers]" 
        
        
        UpdateCommand="UPDATE [Customers] SET [Customer_Name] = @Customer_Name, [Date_First_Installed] = @Date_First_Installed, [Unknown_Part_Of_First_Installed_Date] = @Unknown_Part_Of_First_Installed_Date, [ExhibitA_Evidence_Version] = @ExhibitA_Evidence_Version, [Address] = @Address, [City] = @City, [County] = @County, [State] = @State, [Zip] = @Zip WHERE [Customer_ID] = @original_Customer_ID AND (([Customer_Name] = @original_Customer_Name) OR ([Customer_Name] IS NULL AND @original_Customer_Name IS NULL)) AND (([Date_First_Installed] = @original_Date_First_Installed) OR ([Date_First_Installed] IS NULL AND @original_Date_First_Installed IS NULL)) AND (([Unknown_Part_Of_First_Installed_Date] = @original_Unknown_Part_Of_First_Installed_Date) OR ([Unknown_Part_Of_First_Installed_Date] IS NULL AND @original_Unknown_Part_Of_First_Installed_Date IS NULL)) AND (([ExhibitA_Evidence_Version] = @original_ExhibitA_Evidence_Version) OR ([ExhibitA_Evidence_Version] IS NULL AND @original_ExhibitA_Evidence_Version IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([County] = @original_County) OR ([County] IS NULL AND @original_County IS NULL)) AND (([State] = @original_State) OR ([State] IS NULL AND @original_State IS NULL)) AND (([Zip] = @original_Zip) OR ([Zip] IS NULL AND @original_Zip IS NULL))">
        <DeleteParameters>
            <asp:Parameter Name="original_Customer_ID" Type="Int32" />
            <asp:Parameter Name="original_Customer_Name" Type="String" />
            <asp:Parameter DbType="Date" Name="original_Date_First_Installed" />
            <asp:Parameter Name="original_Unknown_Part_Of_First_Installed_Date" Type="String" />
            <asp:Parameter Name="original_ExhibitA_Evidence_Version" Type="String" />
            <asp:Parameter Name="original_Address" Type="String" />
            <asp:Parameter Name="original_City" Type="String" />
            <asp:Parameter Name="original_County" Type="String" />
            <asp:Parameter Name="original_State" Type="String" />
            <asp:Parameter Name="original_Zip" Type="String" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="Customer_Name" Type="String" />
            <asp:Parameter DbType="Date" Name="Date_First_Installed" />
            <asp:Parameter Name="Unknown_Part_Of_First_Installed_Date" Type="String" />
            <asp:Parameter Name="ExhibitA_Evidence_Version" Type="String" />
            <asp:Parameter Name="Address" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="County" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
            <asp:Parameter Name="original_Customer_ID" Type="Int32" />
            <asp:Parameter Name="original_Customer_Name" Type="String" />
            <asp:Parameter Name="original_Date_First_Installed" DbType="Date" />
            <asp:Parameter Name="original_Unknown_Part_Of_First_Installed_Date" Type="String" />
            <asp:Parameter Name="original_ExhibitA_Evidence_Version" Type="String" />
            <asp:Parameter Name="original_Address" Type="String" />
            <asp:Parameter Name="original_City" Type="String" />
            <asp:Parameter Name="original_County" Type="String" />
            <asp:Parameter Name="original_State" Type="String" />
            <asp:Parameter Name="original_Zip" Type="String" />
        </UpdateParameters>
        <InsertParameters>
            <asp:Parameter Name="Customer_Name" Type="String" />
            <asp:Parameter DbType="Date" Name="Date_First_Installed" />
            <asp:Parameter Name="Unknown_Part_Of_First_Installed_Date" Type="String" />
            <asp:Parameter Name="ExhibitA_Evidence_Version" Type="String" />
            <asp:Parameter Name="Address" Type="String" />
            <asp:Parameter Name="City" Type="String" />
            <asp:Parameter Name="County" Type="String" />
            <asp:Parameter Name="State" Type="String" />
            <asp:Parameter Name="Zip" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
    <p>
        &nbsp;</p>
    <p>
        <asp:GridView ID="GridView2" runat="server" AllowPaging="True" 
            AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Customer_ID" 
            DataSourceID="SqlDataSource2">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" 
                    ShowSelectButton="True" />
                <asp:BoundField DataField="Customer_ID" HeaderText="Customer_ID" 
                    InsertVisible="False" ReadOnly="True" SortExpression="Customer_ID" />
                <asp:BoundField DataField="Customer_Name" HeaderText="Customer_Name" 
                    SortExpression="Customer_Name" />
                <asp:BoundField DataField="Date_First_Installed" 
                    HeaderText="Date_First_Installed" SortExpression="Date_First_Installed" />
                <asp:BoundField DataField="Unknown_Part_Of_First_Installed_Date" 
                    HeaderText="Unknown_Part_Of_First_Installed_Date" 
                    SortExpression="Unknown_Part_Of_First_Installed_Date" />
                <asp:BoundField DataField="ExhibitA_Evidence_Version" HeaderText="ExhibitA_Evidence_Version" 
                    SortExpression="ExhibitA_Evidence_Version" />
                <asp:BoundField DataField="Address" HeaderText="Address" 
                    SortExpression="Address" />
                <asp:BoundField DataField="City" HeaderText="City" 
                    SortExpression="City" />
                <asp:BoundField DataField="County" HeaderText="County" 
                    SortExpression="County" />
                <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
                <asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
            </Columns>
        </asp:GridView>
    </p>
    </form>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
    <p>
        &nbsp;</p>
</body>
</html>

Open in new window

0
Comment
Question by:RiverWalk
  • 4
  • 3
8 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37844838
The most common cause for this is your DataBinding the Grid in the page_Load without first checking if it's a postback.


eg:

If (!Page.IsPostback)
{
  grid.DataBind();
   // do something else
}


The grid is posting back, which is firing the dataload, which is rebinding the grid to the original data, ignoring your update.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37844892
I don't think this author is doing anything in code behind on Page_Load.

I would try plugging in some hard values and see if they stick.

Comment out your original Update statement and paste in a new one just below it and fill in some hard values instead of parameters up to the first WHERE clause:

UpdateCommand="UPDATE [Contacts] SET [Title] = 'Title', [First_Name] = 'Joe', [Last_Name] = 'Smith', [Work_Phone] = '800-555-1212', [Cell_Phone] = '800-555-1212', [Email] = 'email@email.com', [Department] = 'Billing' WHERE [Contact_ID] = 12345"

Be sure to include a legitimate Contact_ID.
0
 

Author Comment

by:RiverWalk
ID: 37845120
Ged325 & TommyBoy, thank you both for your replies. And you are correct TommyBoy, I'm not trying to do anything in a code behind page. Not intentionally anyway.

I started to you try out your suggestion TommyBoy, and got frustrated with how many columns I was looking at in order to do the test, so I thought I'd adjust my data source to only include two columns, the Customer_ID and Customer_Name columns. Just to simply what I'm dealing with for testing. And to my surprise, after making this change, I am able to save updates to the Customer_Name... Now, I'm going to add columns back one by one and see at what point the update capability breaks. I'm certain that it is going to break at some point, so I will post back here and let you know what happens. So,  please come back, I still need you on this one. I know I'm not out of the woods yet.

Thank you!
Riverwalk
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Accepted Solution

by:
Tom Beck earned 500 total points
ID: 37845125
That's good investigative work. My guess would be that the problem lies somewhere in that extensive WHERE clause. I can't imagine why you would need to be so specific. I would think just a simple WHERE Customer_ID=@Customer_ID would work especially if Customer_ID is a primary key. If so, then there could not be a mistake in updating the wrong row. The rest of that WHERE clause could be removed.
0
 

Author Comment

by:RiverWalk
ID: 37845166
Ahh... I thought that WHERE clause was extremely weird myself, but assumed that if Visual Web Developer put it in there then it must be needed. Thanks for that piece of insight.

I just now was able to add Date_Of_First_Install, and updating worked, but updating broke when I added "Unknow_Part_Of_First_Install_Date"...

So, I whittled down the WHERE clause, like you suggested, and it worked! I'm going to keep adding the columns and ensure that the WHERE clause stays small and see what happens.

I am going to award you the points, but just have one question left. Your points do not hinge on this though, it's just a closing question. Why in the world is Visual Web Developer building that insane and confusing WHERE clause? Any ideas?

Thank you,
Riverwalk
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37845183
Just to cover all bases I would say. Visual Web Developer probably has no way of determining which fields in your database are null-able, which are primary keys, which are identity, etc, so it does the only logical thing; check every column to make certain it is updating the intended row. "What good is a setup wizard if it does not work in every possible circumstance" is Microsoft's way of thinking.
0
 

Author Comment

by:RiverWalk
ID: 37845185
Thank you so much TommyBoy, for that informative explanation and also for solving this very painful riddle for me. I now have all of the columns in place and updating works. You are good!

Thank you as well ged325.

Riverwalk
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37845189
You are welcome. Glad to help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question