Error : SqlException was unhandled by user code

Question

Controller :

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Data.SqlClient;

using System.Web.Configuration;

using Insert_UserDetails.Models;

using System.Data;

namespace Insert_UserDetails.Controllers

{

public class UserController : Controller

{

// GET data from stored procedure

public ActionResult InsertUserDetails()

{

var objuserdetail = new UserDetails();

using(SqlConnection con = new SqlConnection())

{

con.ConnectionString = WebConfigurationManager.ConnectionStrings["mycon"].ToString();

var cmd = new SqlCommand("usercrudoperation", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@status", "GET");

con.Open();

var data_adapter = new SqlDataAdapter(cmd);

var data_set = new DataSet();

data_adapter.Fill(data_set); // got error

var userlist = new List();

for(int i = 0; i< data_set.Tables[0].Rows.Count; i++)

{

var objdetails = new UserDetails();

objdetails.userid = int.Parse(data_set.Tables[0].Rows[i]["userid"].ToString());

objdetails.username = data_set.Tables[0].Rows[i]["username"].ToString();

objdetails.education = data_set.Tables[0].Rows[i]["education"].ToString();

objdetails.location = data_set.Tables[0].Rows[i]["location"].ToString();

userlist.Add(objdetails);

}

objuserdetail.userinfo = userlist;

}

return View(objuserdetail);

}

//Insert data into stored procedure

[HttpPost]

public ActionResult InsertUserDetails(UserDetails user)

{

var objuserdetail = new UserDetails();

using(SqlConnection con = new SqlConnection())

{

con.ConnectionString = WebConfigurationManager.ConnectionStrings["mycon"].ToString();

var cmd = new SqlCommand("usercrudoperation", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@username", user.username);

cmd.Parameters.AddWithValue("@education", user.education);

cmd.Parameters.AddWithValue("@location", user.location);

cmd.Parameters.AddWithValue("@Status", "Insert");

con.Open();

ViewData["result"] = cmd.ExecuteNonQuery();

}

return View();

}

}

}

 

Stored procedure :

 

Create Procedure usercrudoperation

(

@username varchar(50),

@education varchar(50),

@location varchar(50),

@status varchar(10)

)

As

BEGIN

-- Insert User Details

if @status ='INSERT'

BEGIN

INSERT INTO userdetail(username,education,location)

VALUES(@username,@education,@location)

END

-- Get User Details

if @status ='GET'

BEGIN

SELECT * FROM userdetail

END

END

 

 

Table:

 

create table userdetail

(

userid int primary key identity not null,

username nvarchar(50),

education nvarchar(50),

location nvarchar(50)

)

 

Model:

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

namespace Insert_UserDetails.Models

{

public class UserDetails

{

// define all the fields of userdetail table

public int userid { get; set; }

public string username { get; set; }

public string education { get; set; }

public string location { get; set; }

public List userinfo { get; set; }

}

}

Basically , what i want to do is just insert data into table using stored procedure and retrieve data from the database. 

I tried a lot to solve this error and go through the code twice , thrice but i haven't got any solution.

Also, read many solution from stackoverflow nothing work.Need help...!!!!