CODEWITHSUNDEEP
c#asp.netsqlsqldatasource
Quentin91360
Quentin91360

Reputation: 122

Can't Update with sqlcommand and Parameters

I have some trouble to update my sql server 2005 database when i use parameters.Here you can see the code that normally has to work.I precise that i already make others treatments such as insert into and it worked perfectly.

myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);
myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";

//Execute la commande
myCommand.ExecuteNonQuery();

EDIT:When i use hard code such as:

myCommand.CommandText = "Update Action set titre='title' where pk=@Pk";

it works...

Upvotes: 0

Views: 3016

Answers (6)

FUSION CHA0S
FUSION CHA0S

Reputation: 93

I have noticed that copying the entire code into a new project helps. I have ran into many times my code would work and then the next day would not, or would only work for someone else and not me. Usually this is due to the designer side of the project when adding and removing code from your project. Just because you delete specific code does not mean the program can update the entire class/project.

Upvotes: 1

Developer
Developer

Reputation: 8636

I don't know where you went wrong this is the working code for me

string strCon = @"Data Source=SYSTEM19\SQLEXPRESS;Initial Catalog=TransactionDB;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection cn = new SqlConnection(strCon);
            SqlCommand cmd = new SqlCommand("select * from tblTransaction1", cn);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                txtName.Text = ds.Tables[0].Rows[i]["FirstName"].ToString();
                txtName1.Text = ds.Tables[0].Rows[i]["LastName"].ToString();
            }
        }
    }

Button click code

protected void btnInsert_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(strCon);
    obj1.FirstName = txtName.Text;
    obj1.LastName = txtName1.Text;
    if (obj1.upDate(cn))
    {

    }
}

Sample class code file

private bool m_flag = false;
        private string strFirstName;
        private string strLastName;

        public string FirstName
        {
            get { return strFirstName; }
            set { strFirstName = value; }
        }

        public string LastName
        {
            get { return strLastName; }
            set { strLastName = value; }
        }

public bool upDate(SqlConnection con)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }

        try
        {
            cmd.Parameters.AddWithValue("@Fname", FirstName);
            cmd.Parameters.AddWithValue("@Lname", LastName);
            cmd.CommandText = "Update tblTransaction1 set LastName=@Lname where FirstName=@Fname";
            if (cmd.ExecuteNonQuery() > 0)
            {
                m_flag = true;
            }
        }
        catch
        {

        }
        return m_flag;
    }

Sample Images

Form With Data from SQL

Database data before update

Changing Lastname

after update

Upvotes: 1

Mark
Mark

Reputation: 1537

Try adding the parameters after declaring the command.

myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";
myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);


//Execute la commande
myCommand.ExecuteNonQuery();

I found something similar (not identical) here: http://forums.asp.net/t/1249831.aspx/1

Upvotes: 0

daniloquio
daniloquio

Reputation: 3902

You could try this: instead of adding the parameters like that

myCommand.Parameters.AddWithValue("@Titre", this.titre);

you should add them with data type.

myCommand.Parameters.Add(new SqlParameter("@Titre", SqlDbType.VarChar, 50));
myCommand.Parameters["@Titre"].Value = this.titre;

That way, the final SQL will be Update Action set titre='titre' instead of Update Action set titre=title. Look that in the second statement titre is not inside quotes ''.

Upvotes: 0

Zachary
Zachary

Reputation: 6532

I've seen weird results when you forget to include the "CommandType" parameter. Since you using inline SQL, it should be set to "CommandType.Text".

myCommand.Parameters.AddWithValue("@Pk", this.pk);
myCommand.Parameters.AddWithValue("@Titre", this.titre);
myCommand.CommandText = "Update Action set titre=@Titre where pk=@Pk";

// Added CommandType //
myCommand.CommandType = CommandType.Text;

//Execute la commande
myCommand.ExecuteNonQuery();

Upvotes: 1

LaGrandMere
LaGrandMere

Reputation: 10359

If you do :

Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);

What does it say ?

Try also to prefix your Action table, with the schema name, for example :

myCommand.CommandText = "Update MySchema.Action set titre=@Titre where pk=@Pk";

Because sometimes it can depend on the schema and the user's rights to update this schema.

Upvotes: 0

Related Questions