Update records wrong in GridView

Need help, is where clause required?

You need to add a DataKeyNames property to your gridview and a WHERE clause to your SQL Update Command.

See 1st sample on this page:
http://msdn.microsoft.com/en-us/library/fkzs2t3h.aspx

Since you're using BoundFields and a CommandField you likely dont even need the <updateparameters> but they're not hurting anything.

This example is a bit closer to what you're doing:
http://www.java2s.com/Code/ASP/ADO.net-Database/AddinganUpdateCommandtoaSqlDataSourcecontrol.htm

Your link is very helpful. Thanks!
In the example, how do you know the value of @original_CustomerID even you add where clause?

  <asp:SqlDataSource ID="SqlDataSource1" Runat="server"
            SelectCommand="SELECT * FROM [Customers]"
            ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
            DataSourceMode="DataSet"
            UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName,
                [ContactName] = @ContactName, [ContactTitle] = @ContactTitle,
                [Address] = @Address, [City] = @City, [Region] = @Region, 
                [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,
                [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID">
        </asp:SqlDataSource> 

Select allOpen in new window

Usually when building a table you assign it a Primary Key or an index. This is almost always a numeric field (bigint for example) and is set to autoincrement. This field allows the data to be more quickly accessed and gives you some control over making sure you can access a "unique" row by its value. There's more but that's the basics.

So looking at your database you don't have this, but you may be wanting to update the row based on "OrgCode".

Basically you are only updating (or supposed to be updating) one row(the row you chose to edit and update). By assigning  DataKeyNames="OrgCode" to your gridview then adding the following WHERE clause to your SQL it should only update that one row based on the value of "OrgCode".

"UPDATE MedOrganization SET OrgCode = @OrgCode, OrgName = @OrgName WHERE OrgCode=@OrgCode"

This is a case where that is likely to cause you problems at some point since if two rows have the same value for "OrgCode" they will both get updated(what is happening to you now with all the blanks is a variation of this).

Your best solution may be to add a field to your table called OrgID. Make it bigint or int. In its properties set "is identity" to "yes". It will be automatically setup to autoincrement by 1.

You don't have to display this in your gridview, but you'd use it in your WHERE clause and as the DataKeyName in your gridview (per example). This will allow you to only update the one unique row that the user selected for editing.

Here's more on creating a database and table using SQL Server Management Studio:
http://mrbool.com/p/Creating-databases-using-SQL-Server-Management-Studio-Express/4672

db-sample.jpg

I updated by your suggestion. But it is still wrong.

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
            <br />
            Organizations Management<br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="OrgCode" 
                DataSourceID="SqlDataSource1" ><EmptyDataTemplate>No data found</EmptyDataTemplate>
                <Columns>
                    <asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
                    <asp:BoundField DataField="OrgCode" HeaderText="OrgCode" 
                        SortExpression="OrgCode" />
                    <asp:BoundField DataField="OrgName" HeaderText="OrgName" 
                        SortExpression="OrgName" />
                    <asp:BoundField DataField="OrgContactName" HeaderText="OrgContactName" 
                        SortExpression="OrgContactName" />
                    <asp:BoundField DataField="OrgContactPhone" HeaderText="OrgContactPhone" 
                        SortExpression="OrgContactPhone" />
                    <asp:BoundField DataField="OrgContactEmail" HeaderText="OrgContactEmail" 
                        SortExpression="OrgContactEmail" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:MembershipDB %>" 
                InsertCommand="INSERT INTO MedOrganization(OrgCode, OrgName) VALUES ('@OrgCode', @OrgName)" 
                SelectCommand="SELECT * FROM [MedOrganization]" 
                UpdateCommand="UPDATE MedOrganization SET OrgCode = @OrgCode, OrgName = @OrgName">
                <InsertParameters>
                    <asp:Parameter Name="OrgCode" />
                    <asp:Parameter Name="OrgName" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="OrgCode" />
                    <asp:Parameter Name="OrgName" />
                </UpdateParameters>
            </asp:SqlDataSource>
            <br />
            </asp:Content>

Select allOpen in new window

Do I need to remove update command?

I added where clause to the update command. I could click update button after I typed a diferent value in the field but no change at all.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataSourceID="SqlDataSource1" DataKeyNames="OrgCode" 
                ><EmptyDataTemplate>No data found</EmptyDataTemplate>
                <Columns>
                    <asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
                    <asp:BoundField DataField="OrgCode" HeaderText="OrgCode" 
                        SortExpression="OrgCode" />
                    <asp:BoundField DataField="OrgName" HeaderText="OrgName" 
                        SortExpression="OrgName" />
                    <asp:BoundField DataField="OrgContactName" HeaderText="OrgContactName" 
                        SortExpression="OrgContactName" />
                    <asp:BoundField DataField="OrgContactPhone" HeaderText="OrgContactPhone" 
                        SortExpression="OrgContactPhone" />
                    <asp:BoundField DataField="OrgContactEmail" HeaderText="OrgContactEmail" 
                        SortExpression="OrgContactEmail" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:MembershipDB %>" 
                InsertCommand="INSERT INTO MedOrganization(OrgCode, OrgName) VALUES ('@OrgCode', @OrgName)" 
                SelectCommand="SELECT * FROM [MedOrganization]" 
                
                UpdateCommand="UPDATE MedOrganization SET OrgCode = @OrgCode, OrgName = @OrgName WHERE (OrgCode = @OrgCode)">
                <InsertParameters>
                    <asp:Parameter Name="OrgName" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="OrgCode" />
                    <asp:Parameter Name="OrgName" />
                </UpdateParameters>
            </asp:SqlDataSource>

Select allOpen in new window

And how to insert new records? There is no insert button.

Now update looks work for other fields except the "OrgCode".  That means even I typed "CANES1234" to replace "CANES" then click "Update", it doesn't change. But we can replace
"OrgName" with a new value.

Why?

Alright. I had to think about it awhile and we have some incongruities to resolve. I'm basing everything I say here off the last code sample posted.

1st your SQL UPDATE statement should look something more like this: <see snippet 1>

You have probably already made that correction and not shown it to me because you report that it is now updating all but OrgCode.

I never really ran into this bug before because your approach is flawed and not something I would do. So now you can update everything but "OrgCode". The reason for this is that the sqlparameter @OrgCode is trying to be used in two different ways:

1. Its being used to find the row so to do this it needs its original value or it wouldn't find anything. This is being determined by the DataKeyNames in the GridView.
2. It is being used to get the updated information (what you changed) and try to pass that on to be updated.

It can't be both. Now even if I were to trick around and find some solution for this its just going to lead you into trouble that you can't foresee yet.

I tried to explain that updating off a column that is not fixed is a bad idea. I don't think I was clear enough. Let's take your original example of data:

First Row OrgCode is CANES
I update sedond row OrgCode to CANES and change the rest of the data as well
When I press Update it will update both the 1st and 2nd rows of data.
This is a huge problem, unfathomable messes can be made of your data like this.

I thought you could sneak by and it would become apparent to you if you didn't head my previous warning, but now its no longer a warning its a "must do". You will not get this to work right without an "identity" field unless you change your approach to how you manage this gridview entirely.

Its a simple matter of inserting a column in your sql table and giving it the proper settings <see screenshot>.

Next you update your code to look something like this (note you dont need to update parameters).
<see snippet 2>

Note I've changed the GridView.DataKeyNames to "OrgId" and I've changed the UPDATE command to reflect our current setup. All works fine.

As far as "INSERTING"... one thing at a time!

snippet 1

UpdateCommand="UPDATE MedOrganization SET 
  OrgCode = @OrgCode,
  OrgName = @OrgName,
  OrgContactName = @OrgContactName,
  OrgContactPhone = @OrgContactPhone,
  OrgContactEmail = @OrgContactEmail 
  WHERE OrgCode = @OrgCode">

Select allOpen in new window

snippet 2

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="SqlDataSource1" DataKeyNames="OrgId"
        ><EmptyDataTemplate>No data found</EmptyDataTemplate>
        <Columns>
            <asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
            <asp:BoundField DataField="OrgCode" HeaderText="OrgCode" 
                SortExpression="OrgCode" />
            <asp:BoundField DataField="OrgName" HeaderText="OrgName" 
                SortExpression="OrgName" />
            <asp:BoundField DataField="OrgContactName" HeaderText="OrgContactName" 
                SortExpression="OrgContactName" />
            <asp:BoundField DataField="OrgContactPhone" HeaderText="OrgContactPhone" 
                SortExpression="OrgContactPhone" />
            <asp:BoundField DataField="OrgContactEmail" HeaderText="OrgContactEmail" 
                SortExpression="OrgContactEmail" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MembershipDB %>" 
        InsertCommand="INSERT INTO MedOrganization(OrgCode, OrgName) VALUES ('@OrgCode', @OrgName)" 
        SelectCommand="SELECT * FROM [MedOrganization]" 
        
        UpdateCommand="UPDATE MedOrganization SET 
	OrgCode = @OrgCode,
	OrgName = @OrgName,
	OrgContactName = @OrgContactName,
	OrgContactPhone = @OrgContactPhone,
	OrgContactEmail = @OrgContactEmail WHERE OrgId = @OrgId">
        <InsertParameters>
            <asp:Parameter Name="OrgName" />
        </InsertParameters>
        <UpdateParameters>
        </UpdateParameters>
    </asp:SqlDataSource>

Select allOpen in new window

ModifiedTable.jpg

Can we use

UpdateCommand="UPDATE MedOrganization SET 
  OrgCode = @OrgCode,
  OrgName = @OrgName,
  OrgContactName = @OrgContactName,
  OrgContactPhone = @OrgContactPhone,
  OrgContactEmail = @OrgContactEmail 
  WHERE OrgCode = @Original_OrgCode">

Select allOpen in new window

And

<UpdateParameters>
                    <asp:Parameter Name="OrgCode" />
                    <asp:Parameter Name="OrgName" />
                    <asp.Parameter Name="Original_OrgCode" />
</UpdateParameters>

Select allOpen in new window

Thank for advice.

So you not able to add id? Or you going to do it that way and you're going strong?

I have to ask my supervisor, I don't have the permmision to change the database. Maybe it is a bad designed database.

Well let me ask you a question before you do that.

When I look at the very first picture you posted (capture3.png) I'm seeing some numbers to the left of OrgCode. I just noticed that. That looks very much like an index field I just cant see the column name there. Is there another column there?

If that is a unique numeric field (and I'll bet it is) then you already have what you need. You just need to find the name of that column and substitute it where I used OrgID in the code above.

Hope that helps.

If your table does not have some unique key column and they want to do updates on it then "yes" it is a bad design. You may not have the info in your backpocket to make the argument to your supervisor if he doesn't agree but that is just basic info.

There are people on here who know a lot more about databases than I do. If you want a second opinion or info to help you converse with your boss then put up another question pointing to this issue and see what kind of information you can get from SQL Db Experts about providing a unique key on a table in a situation like this.

Good luck.

I'll keep checking back for a few days if you have more questions.

Does it help?

Does what help? I'd like to help you have a positive resolution but you've got to help me help you here.

If you already have a column in the table that you can use yes it helps you to simply finish this update issue without talking to your supervisor. You showed a picture but I can't see what the numeric column is there and you're not using it in your code.

If you're referring to "does having a unique column help" (oversimplification) well yes it helps. It helps with accuracy, it helps with speed, it helps with relating to other data. Its database 101.

I'm trying to help even though you've assigned points but if you are done its ok.

Thanks, the numeric column comes from the result on the panel of Microsoft SQL Server Managent Studio. It is not in the table.

Merry Christmas.

Well that's a shame...

Merry Christmas right back at ya.