Error Updating data in ASP.NET

Question

I am unable to figure out the exact problem of the given code when i try to update.

protected void UpdateService(object sender, GridViewUpdateEventArgs e)
    {
        int ServiceId = Convert.ToInt32(((Label)GridView1.Rows[e.RowIndex].FindControl("lblServiceID")).Text);
        string ServiceName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtService")).Text;
        decimal Rate = Convert.ToDecimal(((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRate")).Text);
        string ParentService = ((DropDownList)GridView1.Rows[e.RowIndex].FindControl("ddlParentService")).SelectedItem.Text;
        string GrandParentService = ((DropDownList)GridView1.Rows[e.RowIndex].FindControl("ddlGrandParentService")).SelectedItem.Text;

        SqlConnection con = new SqlConnection(strConnString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "update Service set ServiceName=@ServiceName,Rate=@Rate,"+
        "ParentService=@ParentService,GrandParentService=@GrandParentService "+
        "where ServiceId = @ServiceId;" +
         "select ServiceId,GrandParentService,ParentService,ServiceName,Rate from Service";
        cmd.Parameters.Add("@ServiceName", SqlDbType.VarChar).Value = ServiceName;
        cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = Rate;
        cmd.Parameters.Add("@ParentService", SqlDbType.VarChar).Value = ParentService;
        cmd.Parameters.Add("@GrandParentService", SqlDbType.VarChar).Value = GrandParentService;


        GridView1.EditIndex = -1;
        GridView1.DataSource = GetData(cmd);
        GridView1.DataBind();
    }



private DataTable GetData(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);                    //Error "Must declare the scalar variable "@ServiceId".
        return dt;
    }

I am getting an error message "Must declare the scalar variable "@ServiceId" at the time of update. "ServiceId" is the primary key for the table.

Please help me. Thanks in advance.

You have a @ServiceId in your SqlCommand but you never add it as a parameter with Parameters.Add() method. — Soner Gönül
– Soner Gönül, Commented Dec 27, 2013 at 20:42
But like Soner said, you still need to pass the ServiceId value, otherwise how will your SQL command know what row to update if you're not passing anything in your WHERE clause? — Vash The Stampede
– Vash The Stampede, Commented Dec 27, 2013 at 20:48

Dave Zych · Accepted Answer · 2013-12-27 20:43:43Z

You're specifying a @ServiceId parameter in your query but never declaring or adding one in your command. You need to specify this when performing an update, or leave off the where clause when inserting a new row.

cmd.CommandText = "update Service set ServiceName=@ServiceName,Rate=@Rate,"+
    "ParentService=@ParentService,GrandParentService=@GrandParentService "+
    "where ServiceId = @ServiceId;" +
     "select ServiceId,GrandParentService,ParentService,ServiceName,Rate from Service";
cmd.Parameters.Add("@ServiceName", SqlDbType.VarChar).Value = ServiceName;
cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = Rate;
cmd.Parameters.Add("@ParentService", SqlDbType.VarChar).Value = ParentService;
cmd.Parameters.Add("@GrandParentService", SqlDbType.VarChar).Value = GrandParentService;
cmd.Parameters.Add("@ServiceId", SqlDbType.Int).Value = 1; //Some Value

David · Accepted Answer · 2013-12-27 20:43:44Z

Your query has a parameter called @ServiceId:

cmd.CommandText = "update Service set ServiceName=@ServiceName,Rate=@Rate,"+
"ParentService=@ParentService,GrandParentService=@GrandParentService "+
"where ServiceId = @ServiceId;" +  // <--- right here
 "select ServiceId,GrandParentService,ParentService,ServiceName,Rate from Service";

But you never add such a parameter:

cmd.Parameters.Add("@ServiceName", SqlDbType.VarChar).Value = ServiceName;
cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = Rate;
cmd.Parameters.Add("@ParentService", SqlDbType.VarChar).Value = ParentService;
cmd.Parameters.Add("@GrandParentService", SqlDbType.VarChar).Value = GrandParentService;

To the SQL code, @ServiceId is a variable that's never declared or assigned a value, hence the error. You need to add a parameter for it:

cmd.Parameters.Add("@ServiceId", SqlDbType.Int).Value = ServiceId;

T McKeown · Accepted Answer · 2013-12-27 20:42:44Z

1

Looks like ServiceId is not being given a value even though @ServiceId is in your WHERE clause..

You are adding a parameter for ServiceName but not ServiceId

answered Dec 27, 2013 at 20:42

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

2 Comments

Jaan Over a year ago

ServiceId is my primary key. How do i update please elaborate.

T McKeown Over a year ago

You are performing an UPDATE so this assumes you know the Service Id

shujaat siddiqui · Accepted Answer · 2013-12-27 20:48:11Z

1

add this

cmd.Parameters.Add("@ServiceId", SqlDbType.Int).Value = ServiceId ;

answered Dec 27, 2013 at 20:48

shujaat siddiqui

1,6873 gold badges22 silver badges45 bronze badges

Collectives™ on Stack Overflow

Error Updating data in ASP.NET

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related