Gridview SqlDataSource; Problem with update

Question

I'm trying to do an update of the form

UPDATE TableName 
   SET field1 = NewValue
   WHERE field1 = OldValue

but I'm not quite sure how to do it with GridView.

I have this for my Gridview markup

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    DataSourceID="SqlDataSource1"  AllowPaging="True" DataKeyNames="cftm_emp_name" PageSize="30">
    <Columns>
        <asp:CommandField ButtonType="Image" SelectImageUrl="~/Images/sel.jpg" ShowSelectButton="True" />
        <asp:BoundField DataField="cftm_emp_name" HeaderText="CF Team Member" SortExpression="cftm_emp_name" />
        <asp:CommandField ShowEditButton="True" />
    </Columns>
</asp:GridView>

and the SqlDataSource looks like this

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT [cftm_emp_name] FROM [rpc_extra_cf_team_members] ORDER BY [cftm_emp_name]" OnUpdating="SqlDataSource1_Updating" UpdateCommand="UPDATE rpc_extra_cf_team_members SET cftm_emp_name = ? WHERE cftm_emp_name = ?">
            <UpdateParameters>
                <asp:Parameter Name="cftm_emp_name" Type="String" />
                <asp:ControlParameter ControlID="GridView1" Name="cftm_emp_name" PropertyName="SelectedValue" />
            </UpdateParameters>
        </asp:SqlDataSource>

EDIT:

No errors but it just does not update it. I set a break point in OnUpdating event handler and I noticed that both parameters are set to OldValue.

Let me know if there is any additional info required and I would appreciate any help on this.

Thanks

so, you are trying to update all rows with OldValue for field1 to NewValue ? — Bala R
– Bala R, Commented Jun 24, 2011 at 13:08
@TheOtherGuy - Seems ok to me, if you are getting some errors then update your question. — Bibhu
– Bibhu, Commented Jun 24, 2011 at 13:19
Not all rows per say, but just the rows with value = OldValue for field1. What I have does not work. It just does not update at all. — TheOtherGuy
– TheOtherGuy, Commented Jun 24, 2011 at 13:19
@Bibhu added an edit section with what happens when I try this. Thanks — TheOtherGuy
– TheOtherGuy, Commented Jun 24, 2011 at 13:21

avpaderno · Accepted Answer · 2011-07-15 23:06:47Z

1

The reason is that you are updating the primary key field named cftm_emp_name, which you are using as DataKeyNames="cftm_emp_name" in your case. The field of the table that we use as DataKeyNames of GridView acts as primary key of the table. So, in your table, "cftm_emp_name" is the primary key and you cannot update the primary key. Try to update the other fields of your table by using "cftm_emp_name" field as primary key for the search criteria.

For example, you can use an update query such as:

 UpdateCommand="UPDATE rpc_extra_cf_team_members SET cftm_emp_add = @cftm_emp_add WHERE cftm_emp_name = @cftm_emp_name"

<UpdateParameters>
  <asp:Parameter Name="cftm_emp_add" Type="String" />
  <asp:Parameter Name="cftm_emp_name" Type="String" />   
</UpdateParameters>

edited Jul 15, 2011 at 23:06

avpaderno

30k17 gold badges81 silver badges95 bronze badges

answered Jul 15, 2011 at 15:14

SK INFOPOINT

363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

saurabh goyal · Accepted Answer · 2011-06-24 14:38:41Z

0

set the datakeymember property of sqldatasource as your primary key field in the table and try. hope it helps

answered Jun 24, 2011 at 14:38

saurabh goyal

1,7448 gold badges35 silver badges46 bronze badges

2 Comments

saurabh goyal Over a year ago

why are you using two parameter for the same filed ?

TheOtherGuy Over a year ago

I have SET field1 = NewValue WHERE field1 = OldValue so I'm using two params for same field, one for newvalue and one for oldValue.

Collectives™ on Stack Overflow

Gridview SqlDataSource; Problem with update

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related