Sqlexception was unhandled by user code

Question

1.00/5 (1 vote)

See more:

Basically, What i have done i add userdata in the database table and retrieve data from the database using MVC. I got error in Action method of get data from the database. Kindly correct me , where i am actually lacking...!!!

What I have tried:

Controller:

C#

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

Model :

C#

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<userdetails> userinfo { get; set; }
    }
}

View:

HTML

<pre>@model Insert_UserDetails.Models.UserDetails

@{
    ViewBag.Title = "InsertUserDetails";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>InsertUserDetails</h2>
    <div>
        @using (Html.BeginForm("InsertUserDetails", "User", FormMethod.Post))
        {
            <table><tbody><tr><td>User Name :</td><td>@Html.TextBoxFor(u => u.username)</td></tr><tr><td>Education :</td><td>@Html.TextBoxFor(u => u.education)</td></tr><tr><td>Location :</td><td>@Html.TextBoxFor(u => u.location)</td></tr><tr><td> </td><td></td></tr></tbody></table>
        }
        <h4>User Details</h4>
        @if (Model != null)
        {
            if (Model.userinfo.Count > 0)
            {
                    @foreach (var item in Model.userinfo)
                    {
                        
                    }
                <table><tbody><tr><th>UserId</th><th>UserName</th><th>Education</th><th>Location</th></tr><tr><td>@Html.DisplayFor(modelitem => item.userid) </td><td>@Html.DisplayFor(modelitem => item.username)</td><td>@Html.DisplayFor(modelitem => item.education)</td><td>@Html.DisplayFor(modelitem => item.location)</td></tr></tbody></table>
            }
            else
            {
                No Details Found.
            }
        }
        
$(function () {
var msg = '@ViewData["result"]';
if (msg == '1')
{
alert("User Details Inserted Successfully");
window.location.href = "@Url.Action("InsertUserDetails", "User")";
}
});

Stored Procedure:

SQL

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

Posted 24-Feb-17 22:49pm

Member 13004859

Updated 24-Feb-17 23:19pm

Graeme_Grant

v2

Add a Solution

Comments

Graeme_Grant 25-Feb-17 5:05am

We won't be blindly debugging your code for you. What error message are you encountering? Please update your Question by clicking on Improve question

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2017-02-24T23:19:00

Look at the two different ways you use the SP:

Quote:
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

And

SQL

var cmd =  new SqlCommand("usercrudoperation", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@username", user.userid);
                cmd.Parameters.AddWithValue("@education", user.education);
                cmd.Parameters.AddWithValue("@location", user.location);
                cmd.Parameters.AddWithValue("@Status", "Insert");
                con.Open();
                ViewData["result"] = cmd.ExecuteNonQuery();

In t6he first case, you supply only one parameter and get an error.
In the second you supply four parameters, and (presumably) don't.

A quick look at the SP definition:

SQL

Create Procedure usercrudoperation
(
@username varchar(50),
@education varchar(50),
@location varchar(50),
@status varchar(10)
)
As

Shows that it expects four parameters, and that none of them can be null. Parameters you do not provide are passed as null values, so you get an error.
Either provide all the parameters, or change your SP to allow nulls values for the three you do not pass.