CODEWITHSUNDEEP
c#sql-servermodel-view-controllerasp.net-core-mvc
Rohan Rao
Rohan Rao

Reputation: 2603

SqlException: Incorrect syntax near @VariableName in asp.net mvc core

I am trying to insert a data (actually a video) to database using Stored Procedure in MS SQL in ASP.NET MVC core

I have this code:

    [HttpPost]
    public async Task<IActionResult> Index(string title, IFormFile file, string filesize ,string filePath )
    {
        var Title= new SqlParameter("@Title", title);
        var UploadType = new SqlParameter("@UploadType", file.ToString());
        var FileSize = new SqlParameter("@FileSize", filesize);
        var FilePath = new SqlParameter("@FilePath", filePath);


        var insertVideo = _context.Lectures.FromSql("usp_AddNewVideoFile (@Title,@UploadType, @FileSize, @FilePath) ",parameters:new[] {Title,FileSize,FilePath,UploadType }).ToList();
        await _context.SaveChangesAsync();

        return RedirectToAction("Source",insertVideo);
    }

Stored Procedure:

CREATE procedure [dbo].[usp_AddNewVideoFile]

@Title nvarchar(50),  
@UploadType nvarchar(50),
@FileSize int = null,  
@FilePath nvarchar(MAX)

as  
begin  

insert into Lectures(Name,FileSize,FilePath,UploadType)   
values (@Title,@FileSize,@FilePath,@UploadType)   

end

Getting an error saying:

SqlException: Incorrect syntax near '@Title'.

What could be the reason?

Upvotes: 1

Views: 640

Answers (3)

Rena
Rena

Reputation: 36705

Change like below:

[HttpPost]
public async Task<IActionResult> Test(string title, IFormFile file, int filesize, string filePath)
{
    var Title = new SqlParameter("@Title", title);
    var UploadType = new SqlParameter("@UploadType", file.ToString());
    var FileSize = new SqlParameter("@FileSize", filesize);
    var FilePath = new SqlParameter("@FilePath", filePath);

    //change this...    
    var insertVideo = _context.Database.ExecuteSqlCommandAsync("usp_AddNewVideoFile @Title,@UploadType,@FileSize, @FilePath", parameters: new[] { Title, UploadType, FileSize, FilePath });

    await _context.SaveChangesAsync();

    return RedirectToAction("Source", insertVideo);
}

Upvotes: 2

Krzysztof Madej
Krzysztof Madej

Reputation: 40729

Check this please:

    [HttpPost]
    public async Task<IActionResult> Index(string title, IFormFile file, string filesize ,string filePath )
    {
        var Title= new SqlParameter("@Title", title);
        var UploadType = new SqlParameter("@UploadType", file.ToString());
        var FileSize = new SqlParameter("@FileSize", filesize);
        var FilePath = new SqlParameter("@FilePath", filePath);


        var insertVideo = _context.Lectures.FromSqlRaw("EXECUTE usp_AddNewVideoFile @Title, @FilePath, @FilePath, @UploadType", parameters:new[] {Title,FileSize,FilePath,UploadType }).ToList();
        await _context.SaveChangesAsync();

        return RedirectToAction("Source",insertVideo);
    }

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063328

The problem is here:

usp_AddNewVideoFile (@Title,@UploadType, @FileSize, @FilePath)

That's not quite how you call an SP; the ideal way would be to specify the command type as CommandType.StoredProcedure, and just specify "usp_AddNewVideoFile" as the CommandText. If that isn't possible due to FromSql, then:

EXEC usp_AddNewVideoFile @Title,@UploadType, @FileSize, @FilePath;

Note, however, that if this is the EF FromSql, then using it for an insert command probably isn't ideal; you're really looking for an "execute non query" API.

Upvotes: 0

Related Questions