update query is not working in ASP.NET web application

Question

using System.Data.SqlClient;
using System.Data.Sql;

public partial class _Default : System.Web.UI.Page 
{


 SqlConnection con = new SqlConnection(@"Data Source=GAGAN-PC\SQLEXPRESS;Initial Catalog=update_test;Integrated Security=True");
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void delete_button_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("delete from update_delete where id like'"+TextBox1.Text+"'",con);
        cmd.ExecuteNonQuery();
        Response.Write("Control reached.");
        con.Close();
        Response.Write("Data successfully deleted.");
    }
    protected void update_button_Click(object sender, EventArgs e)
    {
        con.Open();
        cmd = new SqlCommand("update update_delete set password ='"+TextBox3.Text+"' where id like'"+TextBox2+"'", con);
        cmd.ExecuteNonQuery();
        Response.Write("Control reached.");
        con.Close();
        Response.Write("Data successfully Updated.");
    }
}

I am trying to implement update query but there is a little problem in it. I have used SQL Server as database and update_delete is a table in which there are 3 columns id,sname,password and I am trying to update password with respect to id.

Problem is when I click on update button control reaches cmd.ExecuteNonQuery(); no error is displayed. but updating is not taking place. what should I do. Please Please Please help me. Thanks in advance. :) :)

So many queries, so few parameters - so much SQL Injection dangers! — marc_s
– marc_s, Commented Nov 24, 2013 at 10:55
Incorrect, marc_s. You can use LIKE OPERATOR with numeric datatypes such as INT. It is not clear why someone would, but it does function as expected. — laylarenee
– laylarenee, Commented Nov 24, 2013 at 12:49
You should always use Parameterized Queries when collecting user input. This is one of the basics of web development. It helps prevent SQL injection attacks. There are multiple, very serious security issues in this example. — laylarenee
– laylarenee, Commented Nov 24, 2013 at 13:05

marc_s · Accepted Answer · 2013-11-24 11:35:57Z

2

I'm just guessing here - if Id is a numeric datatype, then you cannot use LIKE with it.

Also: please use using()... blocks to ensure proper disposal and use parametrized queries to avoid SQL Injection attacks.

Write your UPDATE command like this:

protected void update_button_Click(object sender, EventArgs e)
{
    // get the values to use
    string idValue = Convert.ToInt32(TextBox3.Text.Trim());
    string password = TextBox2.Text.Trim();

    // define the query text with *parameters* !
    string updateQuery = "update update_delete set password = @password where id = @ID";

    // put things like SqlConnection and SqlCommand into "using()...." blocks
    using (SqlCommand updCmd = new SqlCommand(updateQuery, con))
    {
        // define parameters and their values
        updCmd.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = password;
        updCmd.Parameters.Add("@ID", SqlDbType.Int).Value = idValue;

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        Response.Write("Data successfully Updated.");
    }
}

answered Nov 24, 2013 at 11:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

7 Comments

laylarenee Over a year ago

Another important note is that passwords should never be stored in plain text.

ta.speot.is Over a year ago

// define parameters and their values Is there any point to specifying the type as well? I know with some parameters you have to (e.g. TVPs) but otherwise AddWithValue has always worked fine for me and it's much less code.

marc_s Over a year ago

@ta.speot.is: it's just a personal preference - I prefer to tell ADO.NET what my types are, rather than let it guess those from values I provide. What should it guess if you pass in a NULL ??

ta.speot.is Over a year ago

@marc_s Most .NET types have a well defined SqlDbType equivalent that seem pretty sensible. And if you pass in NULL you're passing in DBNull which as best I can tell would fall under Object and map to Variant: A special data type that can contain numeric, string, binary, or date data as well as the SQL Server values Empty and Null, which is assumed if no other type is declared.

marc_s Over a year ago

@ta.speot.is: all you say is true - but still: if I pass in 42 - what is this? TINYINT ? SMALLINT ? INT ? I just prefer to be explicit about typing to control what gets used

|

Pantelis Natsiavas · Accepted Answer · 2013-11-24 10:48:30Z

1

I suppose you get an Exception. I would kindly suggest to catch your exception and tell us the message... You can catch the exception using the debugger or a try-catch clause.

If you don't get an exception and "Control reached" message is displayed, you would have to use the formed SQL string to use it directly in SQL Server and see if there is a mistake in the SQL statement. I suppose that you somehow form an invalid SQL statement (eg using a non-existing ID).

Hope I helped!

answered Nov 24, 2013 at 10:48

Pantelis Natsiavas

5,3875 gold badges24 silver badges40 bronze badges

Collectives™ on Stack Overflow

update query is not working in ASP.NET web application

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related