GridView RowUpdating SqlDataSource.Update from CodeBehind

Question

So I am having an issue with doing an update in a Gridview during an OnRowUpdating event.

What I am trying to do is set the UpdateCommand in an SqlDataSource then update using that command. The event is firing okay, but when the event is done it appears that the row never updates.

C#:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    SqlDataSource1.UpdateCommand = "UPDATE A_Table SET Something = @AValue WHERE ID = " + e.RowIndex;

    SqlDataSource1.Update();
}

Edit: Re-wrote my example update command...it is really an update command, not a select command haha.

You shouldn't have to do anything in the `RowUpdating` event if you have the `DataKeyNames` set up for the `GridView` (and you have the `UpdateCommand` defined in the `SQLDataSource1` markup). Calling the `Update()` method here would cause the `UPDATE` to occur twice. — Josh Darnell, Oct 06 '11 at 20:51
@jadarnel27 is right; if you do have the `DataKeyNames` set up, calling `Update()` from the `RowUpdating` handler will cause a StackOverflowExeption! — Kiley Naro, Oct 06 '11 at 21:13

score 2 · Answer 1 · answered Oct 06 '11 at 20:38

Your UpdateCommand is supposed to be an UPDATE command, not a SELECT command.

SqlDataSource1.UpdateCommand = "SELECT Something FROM A_Table WHERE ID = " + e.RowIndex;

Change that line to something closer to this:

SqlDataSource1.UpdateCommand = "UPDATE A_Table SET Something = @Something WHERE ID = " + e.RowIndex;

See this msdn link for more info on how to use the UpdateCommand:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updatecommand.aspx

Soner Gönül · Answer 2 · 2011-10-06T20:46:16.590

2

First of all, UpdateCommand shouln't be like UPDATE A_Table SET ID = .. WHERE ... ?

I think you missing something. Read Insert Update Edit Delete record in GridView. This is a great article for you.

EDIT: Like Kiley say: Try SqlDataSource1.UpdateCommand = "UPDATE A_Table SET Something = @Something WHERE ID = " + e.RowIndex;

edited Oct 06 '11 at 20:46

answered Oct 06 '11 at 20:40

Soner Gönül

97,193
102
206
364

Opps, the example is not actually corrent. I didn't post the script because it kind of contains sensative information, I just wrote a poorly thought out example. The update command is not actually the problem, actually getting it to execute is. – Derrick H Oct 07 '11 at 18:20

score 2 · Accepted Answer · answered Oct 07 '11 at 03:04

The other answers here are VERY correct in pointing out the issue with your UpdateCommand.

As I mentioned in the comment earlier, I'll just point out that this is a bit easier than what you have there. When using a GridView with a SQLDataSource, alot of the work is done for you as long as you set up correctly.

First off, define your UpdateCommand in the markup for the SQLDataSource, like this:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="Your Connection String Here" 
    ProviderName="System.Data.SqlClient" 
    SelectCommand="SELECT ColPK, ColA, ColB, ColC FROM Table_Name" 
    UpdateCommand="UPDATE Table_Name 
                   SET ColA=@ColA,  ColB=@ColB, ColC=@ColC, 
                   WHERE ColPK=@ColPK">
</asp:SqlDataSource>

(ColPK would be the primary key of the table you're updating)

Then, you can set "AutoGenerateEditButton" to true in your GridView markup and, poof! You can update the GridView (without having to do anything in code behind).

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    AutoGenerateEditButton="True" DataKeyNames="ColPK" 
    DataSourceID="SqlDataSource1">
    <Columns>
        <asp:BoundField DataField="ColPK" HeaderText="Column PK" 
            SortExpression="ColA" ReadOnly="True" />
        <asp:BoundField DataField="ColA" HeaderText="Column A" 
            SortExpression="ColA" />
        <asp:BoundField DataField="ColB" HeaderText="Column B" 
            SortExpression="ColB" />
        <asp:BoundField DataField="ColC" HeaderText="Column C" 
            SortExpression="ColC" />
    </Columns>
</asp:GridView>

Now, you can still handle the OnRowUpdating event in your code if you need to do extra processing, or cancel the Update based on some logic, etc. But the basic Update functionality is pretty much for free!

GridView RowUpdating SqlDataSource.Update from CodeBehind

3 Answers3

Linked