shefali jain shefali jain - 4 months ago 38
ASP.NET (C#) Question

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

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();
}

Answer

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