CODEWITHSUNDEEP
c#sqlasp.netgridview
Rimo72
Rimo72

Reputation: 91

Can't update datasource from gridview update

Trying to update my DB from the edit/update functionality of a GridView. What ever I try, I can't seem to be working.

How can I update my SQLDatasource using the information entered in the GridView edit textbox?

Here is what I have:

.cs:

                    DS.UpdateCommand = "UPDATE tbSystems SET Systems = @Systems WHERE id = @id";
                    DS.Update();

.aspx:

                <asp:GridView ID="gv1" 
                    runat="server" 
                    CellPadding="2"
                    DataKeyNames="id"
                    AutoGenerateDeleteButton="True"
                    AutoGenerateEditButton="True" 
                    OnRowDeleting="gv1_RowDeleting"
                    OnRowDeleted="gv1_RowDeleted"
                    OnRowUpdating="gv1_RowUpdating" OnRowEditing="gv1_RowEditing" OnRowUpdated="gv1_RowUpdated">
                </asp:GridView>
                <asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:conn %>">
                     <UpdateParameters>
                         <asp:Parameter Name="Systems" Type="String" />
                     </UpdateParameters>
                 </asp:SqlDataSource>

I get this error:

Must declare the scalar variable "@id".

  1. Shouldn't the id variable be declared already since I have it declared in the DataKeyNames of the GridView or should I create an update parameter in the SQLDataSource?

  2. How do I get the new value in the textbox of the GridView? This line of code always give the old value regardless in which event (edit event, updating event or updated event) I put it in:

    Response.Write(((TextBox)gv1.Rows[e.NewEditIndex].Cells[2].Controls[0]).Text);

  3. How do I manage the @variables?

Your help is greatly appreciated.

Upvotes: 2

Views: 2831

Answers (3)

Richard Deeming
Richard Deeming

Reputation: 31198

You don't need any code to connect a GridView to a SqlDataSource control. Just set the relevant properties on the controls and it will just work:

<asp:GridView ID="gv1" runat="server" 
    DataSourceID="DS"
    DataKeyNames="id"
    AutoGenerateDeleteButton="True"
    AutoGenerateEditButton="True" 
/>

<asp:SqlDataSource ID="DS" runat="server" 
    ConnectionString="<%$ ConnectionStrings:conn %>"
    SelectCommand="SELECT * FROM tbSystems"
    UpdateCommand="UPDATE tbSystems SET Systems = @Systems WHERE id = @id"
    DeleteCommand="DELETE tbSystems WHERE id = @id"
>
     <UpdateParameters>
        <asp:Parameter Name="id" Type="Int32" />
        <asp:Parameter Name="Systems" Type="String" />
     </UpdateParameters>
     <DeleteParameters>
        <asp:Parameter Name="id" Type="Int32" />
     </DeleteParameters>
 </asp:SqlDataSource>

The important properties are:

With those properties in place, you can get rid of the event handlers in the code-behind. The data source control will take care of everything for you.

ASP.NET Data-Bound Web Server Controls Overview
Data Source Controls Overview

Upvotes: 2

Rimo72
Rimo72

Reputation: 91

Thanks to the Naveen and lots of tweeking, I finally got to make it work. Here is how I did it. It may not be the best practices but it works.

Put the SQLDataSource in a Session. I don't knkow if this is the right thing to do, however it is the only way I found to catch the value of the gridview edit textbox.

if (!Page.IsPostBack)
{
                DS.SelectCommand = "SELECT * FROM tbSystems";
                Session["myDS"] = DS;
                BindData();
}

Created a BindData function: (gv1 being my GridView)

private void BindData()
        {
            gv1.DataSource = Session["myDS"];
            gv1.DataBind();
        }

In the RowEditing function, I've changed the gv1 edit index to the event new edit index.

protected void gv1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gv1.EditIndex = e.NewEditIndex;
            //Bind data to the GridView control.
            BindData();

And this is the update code.

protected void gv1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {

                    DS.UpdateCommand = "UPDATE tbSystems SET Systems = @Systems WHERE id = @id";
                    var id = gv1.DataKeys[e.RowIndex]["id"];
                    var systems = ((TextBox)gv1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
                    DS.UpdateParameters.Add("id",id.ToString());
                    DS.UpdateParameters.Add("Systems",systems);
                    DS.Update();
                    gv1.EditIndex = -1;
                    BindData();
        }

Hopefully this will help some of you.

Thanks,

Upvotes: 1

codeandcloud
codeandcloud

Reputation: 55200

Shouldn't the id variable be declared already since I have it declared in the DataKeyNames of the GridView or should I create an update parameter in the SQLDataSource?

No. You must fetch the value like this

var id = GridView1.DataKeys[e.RowIndex]["id"];

How do I get the new value in the textbox of the GridView? This line of code always give the old value regardless in which event (edit event, updating event or updated event) I put it in

You should be using the RowUpdating event. MSDN sample code here.

How do I manage the @variables?

var systems = ((TextBox)gv1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
DS.UpdateParameters.Add("@id", id);
DS.UpdateParameters.Add("@Systems", systems);

Upvotes: 0

Related Questions