CODEWITHSUNDEEP
c#t-sqlsqlcommand
E.Rawrdríguez.Ophanim
E.Rawrdríguez.Ophanim

Reputation: 276

SqlException occurred in System.Data.dll but was not handled in user code

I'm trying to run this method from a AJAX in ASP.Net with Razor. I don't have any visible error but every time I try to run it sends me this. I have a method like this running perfectly. But I guess I'm missing something.

enter image description here

This is my C# method

[WebMethod]
public JsonResult  GetDeparments()
{
    string cs = "Data Source=DMX87025;Initial Catalog=DB_PCC;Integrated Security=True";
    string sql = "SELECT * FROM[DB_PCC].[dbo].[Departments]";
    List<Departments> departaments = new List<Departments>();

    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand(sql , con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader(); //unhandled expection here
        while (rdr.Read())
        {
            Departments dep = new Departments();
            dep.Id = Convert.ToInt32(rdr["Id"]);
            dep.Code = rdr["Code"].ToString();
            dep.Description = rdr["Description"].ToString();
            departaments.Add(dep);
        }
    }

    JavaScriptSerializer js = new JavaScriptSerializer();

    return Json(new { success = true, message = (js.Serialize(departaments)) },
        JsonRequestBehavior.AllowGet); 
}

Upvotes: 1

Views: 931

Answers (1)

Steve
Steve

Reputation: 216313

You use the CommandType.StoredProcedure when you want to execute a stored procedure, when your command is a plain text sql command you shouldn't set this property because the default is appropriate for a command text

By the way, while it is not a fatal error, it is always better to use the using statement around disposable objects like the SqlCommand and the SqlDataReader 

using (SqlConnection con = new SqlConnection(cs))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
    // cmd.CommandType = CommandType.StoredProcedure;
    ... 
    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
        ....
    }

}

Upvotes: 4

Related Questions