Solved

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

Posted on 2012-04-13
8
255 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 39

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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now