How to fix 'System.Data.SqlClient.SqlException' error in ASP.NET MVC?

Question

I have problem uploading videos in ASP.NET MVC. I m using HttpPostedfile base class to upload videos and I have a model for my videos to add extra field. I'm getting this error and don't know how to fix it.

The error I am getting is;

System.Data.SqlClient.SqlException: Procedure or function 'sAddNewVideoFile' expects parameter '@CourseName', which was not supplied.

Here is my code

Model

namespace eLearning.Models
{
    public class FileModel 
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public Nullable<int> FileSize { get; set; }
        public string FilePath { get; set; }
        public int CourseID { get; set; }
        public string CourseName { get; set; }
    }
}

Controller

public ActionResult UploadVideos()
{
        return View();
}

[HttpPost]
public ActionResult UploadVideos(HttpPostedFileBase fileupload)
{
        if (fileupload != null)
        {
            var file = new FileModel();
            string fileName = Path.GetFileName(fileupload.FileName);
            int fileSize = fileupload.ContentLength;
            int Size = fileSize / 1000;
            fileupload.SaveAs(Server.MapPath("~/VideoFileUpload/" + fileName));
            int CourseId = file.CourseID;
            string CourseName = file.CourseName;
            string CS = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("sAddNewVideoFile", con);
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                cmd.Parameters.AddWithValue("@Name", fileName);
                cmd.Parameters.AddWithValue("@FileSize", Size);
                cmd.Parameters.AddWithValue("@FilePath", "~/VideoFileUpload/" + fileName);
                cmd.Parameters.AddWithValue("@CourseID", CourseId);
                cmd.Parameters.AddWithValue("@CourseName", CourseName);

                cmd.ExecuteNonQuery();
            }
        }

        return View();
}

Stored procedure:

CREATE procedure [dbo].[sAddNewVideoFile]  
    (@Name NVARCHAR(50),  
     @FileSize INT,  
     @FilePath NVARCHAR(100),
     @CourseID INT, 
     @CourseName NVARCHAR(50)
    )  
AS 
BEGIN
    INSERT INTO Files (Name, FileSize, FilePath, CourseID, CourseName)   
    VALUES (@Name, @FileSize, @FilePath, @CourseID, @CourseName)   
END

View

@model eLearning.Models.FileModel

@{
    ViewBag.Title = "UploadVideos";
}


<h2>UploadVideo</h2>

<!DOCTYPE html>
<html>

<body>
    <div class="container py-4">

        <div class="card">
            <div class="card-header bg-danger text-white">
                <h6 class="text-uppercase">video List</h6>
            </div>
            <div class="card-body">
                <div class="row">
                    <button style="margin-left: 27px; margin-bottom:10px;" type="button" class="btn btn-danger rounded-0" data-toggle="modal" data-target="#UploadVideo">
                        <i class="fa fa-plus-circle"></i> Add New
                    </button>
                    <div class="modal fade" id="UploadVideo">
                        <div class="modal-dialog">
                            <div class="modal-content">
                                <div class="modal-header">
                                    <h4 class="modal-title">Upload New video File</h4>
                                    <button type="button" class="close" data-dismiss="modal">×</button>
                                </div>
                                <div class="modal-body">
                                    @using (Html.BeginForm("UploadVideos", "File", FormMethod.Post, new { enctype = "multipart/form-data" }))
                                    {
                                        <div class="form-group">
                                            @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                                            <div class="form-group">
                                                @Html.LabelFor(model => model.CourseID, htmlAttributes: new { @class = "control-label col-md-2" })
                                                <div class="col-md-10">
                                                    @Html.EditorFor(model => model.CourseID, new { htmlAttributes = new { @class = "form-control" } })
                                                </div>
                                                @Html.LabelFor(model => model.CourseName, htmlAttributes: new { @class = "control-label col-md-2" })
                                                <div class="col-md-10">
                                                    @Html.EditorFor(model => model.CourseName, new { htmlAttributes = new { @class = "form-control" } })
                                                </div>
                                            </div>>
                                            <label>Choose File:</label>
                                            <div class="input-group">
                                                <div class="custom-file">
                                                    <input type="file" id="fileupload" name="fileupload" class="custom-file-input" />
                                                    <label class="custom-file-label"></label>
                                                </div>
                                                <div class="input-group-append">
                                                    <input type="submit" id="btnUpload" class="btn btn-secondary" value="Upload" />
                                                </div>
                                            </div>
                                        </div>
                                    }
                                </div>
                                <div class="modal-footer">
                                    <button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

Are you sure that the sp is the DB is the latest? Can it have a null value? — Irvin Dominin
– Irvin Dominin, Commented Apr 14, 2019 at 10:41
Dan Guzman says: "AddWithValue is Evil" - please read the article and stop using it! — marc_s
– marc_s, Commented Apr 14, 2019 at 10:52

Irvin Dominin · Accepted Answer · 2019-04-14 10:50:36Z

2

The error can point out that the CourseName is null, try change it to:

cmd.Parameters.AddWithValue("@CourseName", CourseName ?? "");

if null is not allowed you have to check your model binder or model validation.

answered Apr 14, 2019 at 10:50

Irvin Dominin

31k9 gold badges83 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hassan.ef · Accepted Answer · 2019-04-14 10:45:19Z

0

mabey about CourseName value if CourseName is null change it to string.Empty!

answered Apr 14, 2019 at 10:45

hassan.ef

1,3502 gold badges11 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to fix 'System.Data.SqlClient.SqlException' error in ASP.NET MVC?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related