Solved

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

Posted on 2012-04-13
8
257 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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