how to remove System.Data.SqlClient.SqlException error?

Question

When I am performing update operation then the error

"An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code"

occured,how can I remove this?this error is occured on cmd.ExecuteNonQuery (line below the insert query)

protected void Button2_Click(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection() ;
        con.ConnectionString = @"Data Source=ADMIN\LOCALHOST;Initial Catalog=maha;Integrated Security=True;";

                con.Open();

                SqlCommand cmd = new SqlCommand("Insert into [dbo].[student]([ID],[NAME],[DOB],[GENDER]) Values ('" + TB1.Text + "','" + TB2.Text + "','" + TB3.Text + "','" + @rm + "')", con);

                cmd.ExecuteNonQuery();
                con.Close();
}
protected void Button3_Click(object sender, EventArgs e)
{
        SqlConnection con = new SqlConnection();
        con.ConnectionString = @"Data Source=ADMIN\LOCALHOST;Initial Catalog=maha;Integrated Security=True;";
        con.Open();

        SqlCommand cmd = new SqlCommand("Update [dbo].[student] set [ID]='" + TB1.Text + "',[NAME]='" + TB2.Text + "',[DOB]='" + TB3.Text + "',[GENDER]='" + @rm + "' where [ID]=='" + TB1.Text + "'", con);
        cmd.ExecuteNonQuery();
        con.Close();
 }

You code is open for SQL Injection attack. I would recommend you to start using [SqlParameter](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx) — Satpal, Aug 02 '16 at 11:20
The exception may indicate that there is some error in the SQL you are creating. What is the `CommandText` of `cmd` after you created the command? — bassfader, Aug 02 '16 at 11:26
Not an exact duplicate, but please have a look at http://stackoverflow.com/q/35163361/87698. As a side effect, it might also fix your original problem. About your original problem: Your exception also has an *exception message*. Look for it and add it to your question. — Heinzi, Aug 02 '16 at 11:36

jonju · Accepted Answer · 2016-08-03T19:51:11.953

0

Well you have some errors in your code and you can catch that Exception by using try{} catch{}.Besides I would suggest to use using statement.

ERROR

In both of the Insert query you are using an SqlParameter i.e @r and you are not providing value for it. So it will obviously throw and Exception, == in the Update query also is an error, It doesn't exist in SqlServer.

Now try this code:

protected void Button3_Click(object sender, EventArgs e){
    try{
        string constr = @"Data Source=ADMIN\LOCALHOST;Initial Catalog=maha;"+
                     "Integrated Security=True;";
        using(SqlConnection con = new SqlConnection(constr)){        
            if(con.State==ConnectionState.Closed){
                con.Open();
            }
            string query= "Update [dbo].[student] set [ID]=@id,[NAME]=@name, [DOB]=@dob,"+
                      "[GENDER]=@rm Where [ID]=@id";
            using(SqlCommand cmd = new SqlCommand(query, con)){
                cmd.CommandType=CommandType.Text;
                cmd.Parameters.AddWithValue("@id",TB1.Text);
                cmd.Parameters.AddWithValue("@name",TB2.Text);
                cmd.Parameters.AddWithValue("@dob",TB3.Text);
                cmd.Parameters.AddWithValue("@rm",rmvalue); //i don't know it's value
                cmd.ExecuteNonQuery(); 
                //success message here      
            }
        }  
    }catch(Exception ex){
          //print your error message here e.g errLabel.Text=ex.Message;
    }

}

I suggest you use this approach for both Insert and Update

edited Aug 03 '16 at 19:51

answered Aug 02 '16 at 11:31

jonju

2,711
1
13
19

i tried this code,but it does not execute the query.always comes to catch block. how to solve this? – shefali jain Aug 03 '16 at 06:32
And what does the Exception Message say? – jonju Aug 03 '16 at 06:44
Message which i print in catch block, how can i solve this? I want to execute the query without any exception – shefali jain Aug 03 '16 at 19:45
Let me know the exact error you see. When the exception is thrown, a small `cross` icon must there next to `catch(Exception ex)` put you mouse on that icon you will be able to see the message. And my guess for the errror is your `connection string`, if the exception is thrown everytime you execute the code. – jonju Aug 03 '16 at 19:50
There was a small typo in the update query here `set [ID]='@id'`. it must be `set [ID]=@id`. changed now try again. – jonju Aug 03 '16 at 19:52
the exception is come because of this cmd.ExecuteNonQuery(); line,Connection string is not an error? – shefali jain Aug 04 '16 at 05:26

how to remove System.Data.SqlClient.SqlException error?

1 Answers1