How to add update parameter to SQLDataSource in c#

Question

In reference to my question,

How to get boundfield value, or am I totally wrong?

I am trying to add update parameter to a SQLDataSource using c# instead of ASP.NET but I am getting error,

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
    string userName = "";
    string city = "";
    string updateStatement = "Update myTable set userName=@userName, city=@city where userID=@userID";

    foreach (DictionaryEntry entry in e.NewValues)
    {
        if (entry.Key == "userName")
            userName = entry.Value.ToString();
        if (entry.Key == "city")
            city = entry.Value.ToString();
    }

    using (SqlDataSource ds = new SqlDataSource(ConnectionString(), updateStatement))
    {
       ds.UpdateParameters.Add("@userName");  
        ds.UpdateParameters.Add("@city");
       ds.UpdateParameters.Add("@UserId");
    }

    gvDetails.EditIndex = -1;
    BindData();
}

How can I pass values of parameters :S

ds.UpdateParameters.Add("@userName");  
        ds.UpdateParameters.Add("@city");
       ds.UpdateParameters.Add("@UserId");

But I don't know the right syntax, can someone direct me in right direction please

Wrong syntax, I can't add paramters to SQLDataSource like that.. — Mathematics
– Mathematics, Commented Apr 30, 2013 at 14:36

Doug Wilson · Accepted Answer · 2013-04-30 15:04:51Z

5

I think you should use an SqlCommand instead of SqlDataSource:

  using (SqlConnection connection = new SqlConnection(ConnectionString())
  using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) {
    connection.Open();
    cmd.Parameters.Add(new SqlParameter("@Name", userName));
    cmd.Parameters.Add(new SqlParameter("@city", city));
    cmd.Parameters.Add(new SqlParameter("@UserId", userID));
    cmd.ExecuteNonQuery();
  }

answered Apr 30, 2013 at 15:04

Doug Wilson

4,2643 gold badges33 silver badges37 bronze badges

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

3 Comments

Mathematics Over a year ago

+1, Can you also explain, why I should use SQLCommand not SqlDataSource please

Doug Wilson Over a year ago

SqlDataSource is intended for databinding to controls on a WebForm. It's way overkill for what you're doing, particularly when you're trying to do it manually. If you want to go the databinding route then it's the right choice.

Damith Over a year ago

1+ no need of creating SqlDataSource here.

Ty Petrice · Accepted Answer · 2013-04-30 20:14:01Z

1

if you want to use SQLDataSource, then I believe the syntax is.

ds.UpdateParameters.Add("userName", "someDefaultValue");

No '@' and you can supply the default value.

answered Apr 30, 2013 at 20:14

Ty Petrice

1,9591 gold badge14 silver badges7 bronze badges

Comments

Soner Gönül · Accepted Answer · 2013-04-30 14:46:25Z

0

You can't add parameters to SqlDataSource. You can add SqlCommand instead. Try something similar like this;

string updateStatement = "Update myTable set userName=@userName, city=@city where userID=@userID";

using (SqlDataSource ds = new SqlDataSource(ConnectionString(), updateStatement))
{       
        SqlCommand cmd = new SqlCommand(updateStatement);
        cmd.Parameters.Add("@userName");  
        cmd.Parameters.Add("@city");
        cmd.Parameters.Add("@UserId");
        cmd.Paramters["@City"].Value = city;
        cmd.Paramters["@userName"].Value = userName;
        cmd.Paramters["@UserId"].Value = userID;

        GridView1.DataSource = ds;
        GridView1.DataBind();
}

answered Apr 30, 2013 at 14:46

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

4 Comments

Mathematics Over a year ago

thanks, but you said I can't add parameters to SQLDataSource ? is this something else then, msdn.microsoft.com/en-us/library/… , I might be wrong ?

Soner Gönül Over a year ago

With UpdateParameters property, you can specify which parameters will use in your update command of your SqlDataSource.

Mathematics Over a year ago

sorry but I am not really sure, how above code will going to work as it's not executing anything. but I give it a go anyway, thanks

Damith Over a year ago

SqlDataSource accept select statement, there is a property to set update statement. but here on row updating no point of creating SqlDataSource just for updating database. Hellfire's answer provide better solution.

Collectives™ on Stack Overflow

How to add update parameter to SQLDataSource in c#

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related