Update records wrong in GridView

Hello,

My original database table likes attached  Original table.Now I want to add update function in the GridView, however when I clicl "UpDate" button. It becomes the image
 Updated oneMy code:
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
            <br />
            Organizations Management<br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                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>

Open in new window

Thank you very much.
zhshqzycAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zhshqzycAuthor Commented:
Need help, is where clause required?
0
ddayx10Commented:
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
0
zhshqzycAuthor Commented:
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> 

Open in new window

0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ddayx10Commented:
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
0
zhshqzycAuthor Commented:
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>

Open in new window

Do I need to remove update command?
0
zhshqzycAuthor Commented:
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>

Open in new window

And how to insert new records? There is no insert button.
0
zhshqzycAuthor Commented:
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?
0
ddayx10Commented:
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">

Open 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>

Open in new window

ModifiedTable.jpg
0
zhshqzycAuthor Commented:
Can we use
UpdateCommand="UPDATE MedOrganization SET 
  OrgCode = @OrgCode,
  OrgName = @OrgName,
  OrgContactName = @OrgContactName,
  OrgContactPhone = @OrgContactPhone,
  OrgContactEmail = @OrgContactEmail 
  WHERE OrgCode = @Original_OrgCode">

Open in new window

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

Open in new window

0
ddayx10Commented:
Nope sorry. Not with the simplified variation of the datasource model you are running. With some signifigant updates / changes you may be able to do something like that BUT its still a huge problem.

1. Your code would start down the path of being more complicated. You are already in learning mode you dont need this. I would have to introduce you to concepts you've likely never seen and it would be unnecessary. Plus now I'm writing your project for you instead of just answering questions :)

2. It is a flawed approach. If some user edited an OrgCode to be the same as any other OrgCode in the table then you would end up updating two or more records in your table at a time.

Is it some sort of big problem to add a new column to your table?

I promise you that any developer would make this suggestion as it is one of the most basic and necessary concepts of working with database tables. There are many benefits and zero negatives.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zhshqzycAuthor Commented:
Thank for advice.
0
ddayx10Commented:
So you not able to add id? Or you going to do it that way and you're going strong?
0
zhshqzycAuthor Commented:
I have to ask my supervisor, I don't have the permmision to change the database. Maybe it is a bad designed database.
0
ddayx10Commented:
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.
0
ddayx10Commented:
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.
0
zhshqzycAuthor Commented:
Does it help?
0
ddayx10Commented:
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.

0
zhshqzycAuthor Commented:
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.
0
ddayx10Commented:
Well that's a shame...

Merry Christmas right back at ya.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.