Solved

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

Posted on 2012-04-13
8
258 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Gridview selected row 9 49
Problem to refer to value 8 62
imap read mail 1 34
Correct and Effective way to generate pdf files online via JavaScript or ASP.Net? 6 57
Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

861 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