update command not working in asp.net c#

Question

 protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection mycon = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=master;Integrated Security=True");
        SqlDataAdapter myadp = new SqlDataAdapter();
        myadp.UpdateCommand = new SqlCommand("Update [orgs] Set [fname]=@fname,[weblnk]=@weblnk,[email]=@email,[cntct]=@cntct,[lctn]=@lctn,[cdscrptn]=@cdscrptn,[bsnstp]=@bsnstp WHERE [cmpny]=" +Label1.Text,mycon);
        myadp.UpdateCommand.Parameters.Add("@fname", SqlDbType.VarChar, 50).Value = TextBox1.Text;
        myadp.UpdateCommand.Parameters.Add("@weblnk", SqlDbType.VarChar,80).Value = TextBox3.Text;
        myadp.UpdateCommand.Parameters.Add("@email", SqlDbType.VarChar,80).Value = TextBox4.Text;
        myadp.UpdateCommand.Parameters.Add("@cntct", SqlDbType.VarChar,20).Value = TextBox5.Text;
        myadp.UpdateCommand.Parameters.Add("@lctn", SqlDbType.VarChar,80).Value = TextBox6.Text;
        myadp.UpdateCommand.Parameters.Add("@cdscrptn", SqlDbType.VarChar,600).Value = TextBox7.Text;
        myadp.UpdateCommand.Parameters.Add("@bsnstp", SqlDbType.VarChar,40).Value = TextBox8.Text;
        myadp.UpdateCommand.Connection = mycon;
        mycon.Open();
        myadp.UpdateCommand.ExecuteNonQuery();
        mycon.Close();
    }

protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection mycon = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=master;Integrated Security=True");
        SqlDataAdapter myadp = new SqlDataAdapter();
        myadp.UpdateCommand = new SqlCommand("Update [orgs] Set [fname]=@fname,[weblnk]=@weblnk,[email]=@email,[cntct]=@cntct,[lctn]=@lctn,[cdscrptn]=@cdscrptn,[bsnstp]=@bsnstp WHERE [cmpny]=@cmpny", mycon);
        myadp.UpdateCommand.Parameters.Add("@fname", SqlDbType.VarChar, 50).Value = TextBox1.Text;
        myadp.UpdateCommand.Parameters.Add("@cmpny", SqlDbType.VarChar, 50).Value = TextBox2.Text;
        myadp.UpdateCommand.Parameters.Add("@weblnk", SqlDbType.VarChar,80).Value = TextBox3.Text;
        myadp.UpdateCommand.Parameters.Add("@email", SqlDbType.VarChar,80).Value = TextBox4.Text;
        myadp.UpdateCommand.Parameters.Add("@cntct", SqlDbType.VarChar,20).Value = TextBox5.Text;
        myadp.UpdateCommand.Parameters.Add("@lctn", SqlDbType.VarChar,80).Value = TextBox6.Text;
        myadp.UpdateCommand.Parameters.Add("@cdscrptn", SqlDbType.VarChar,600).Value = TextBox7.Text;
        myadp.UpdateCommand.Parameters.Add("@bsnstp", SqlDbType.VarChar,40).Value = TextBox8.Text;
        myadp.UpdateCommand.Connection = mycon;
        mycon.Open();
        myadp.UpdateCommand.ExecuteNonQuery();
        mycon.Close();
    }

here I have parametrize cmpny too but its still not working

describle more what error you are facing... we can't help you without explanation — Rémi, Jun 03 '13 at 14:06
You are connecting to the master Database? Are you sure that's what you want? — Ralf, Jun 03 '13 at 14:07
i have tried that already ,it is not working that way either — Gitesh Sawariya, Jun 03 '13 at 14:12
Your code does not do anything with the data you insert; what makes you think it is "not working"? — Dour High Arch, Jun 03 '13 at 15:52

score 4 · Answer 1 · answered Jun 03 '13 at 14:09

4

I assume that cmpny is a text field, so you need to enclose it in apostrophes:

WHERE [cmpny]='" + Label1.Text + "'",mycon);

However, forget this immediately. You should use parameters always.

WHERE [cmpny]=@cmpny", mycon);

and

myadp.UpdateCommand.Parameters.AddWithValue("@cmpny", TextBox1.Text);

answered Jun 03 '13 at 14:09

Tim Schmelter

450,073
74
686
939

this one is not working too – Gitesh Sawariya Jun 03 '13 at 14:18
1

@GiteshSawariya: Do you have more details? Do you get an exception on `mycon.Open();`? Use the debugger. – Tim Schmelter Jun 03 '13 at 14:19
no i am not getting any exceptions. – Gitesh Sawariya Jun 03 '13 at 14:33

score 1 · Answer 2 · edited Jun 03 '13 at 15:51

Get rid of the unnecessary SqlDataAdapter.
Get a real Database on your local server. The master database isn't intended for your data.
Check the return value of ExecuteNonQuery. Maybe your assumed cmpny value doesn't exist in the table?
Attach a minimum of Exception Handling to your code.

using (SqlConnection mycon = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=master;Integrated Security=True"))
{
    mycon.Open();
    using (SqlCommand cmd = new SqlCommand("Update [orgs] Set [fname]=@fname,[weblnk]=@weblnk,[email]=@email,[cntct]=@cntct,[lctn]=@lctn,[cdscrptn]=@cdscrptn,[bsnstp]=@bsnstp WHERE [cmpny]=@cmpny", mycon))
    {
        cmd.Parameters.Add("@fname", SqlDbType.VarChar, 50).Value = TextBox1.Text;
        // all the other params
        cmd.Parameters.Add("@bsnstp", SqlDbType.VarChar, 40).Value = TextBox8.Text;
        cmd.Parameters.Add("@cmpny", /*correct Datatype here*/).Value = Label1.Text;  // from a Label ?? how does it got there? You should take the value from the actual source
        int affectedRecords = cmd.ExecuteNonQuery();
    }
}

i have tried them all. I am not getting any exceptions or errors, but it is not updating — Gitesh Sawariya, Jun 03 '13 at 15:29

score 0 · Answer 3 · answered Jun 03 '13 at 14:08

Label1.Text should be parameterized too because it may contains some quotes. In a general way, you should parametize every user inputed value in SQL in order to avoid SQL injections.

You may have some error because of this so your update command is not working.

update command not working in asp.net c#

3 Answers3