An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.SqlClient.dll but was not handled in user code

Question

Additional information: Incorrect syntax near @UserPic.

What is causing this, and how can I resolve it?
Controller code:

    [HttpPut("{id}")]
    public ActionResult<User> ChangeUser(int id, User userToUpdate) 
    {
        User user = dataRepository.GetUser(id);
        if (user == null) { return NotFound(); }
        var updatedUser = dataRepository.UpdateUser(id, userToUpdate);
        return updatedUser;
    }

Data repository code:

        public User UpdateUser(int id, User user) 
    {
        using (var connection = new SqlConnection(connectionString)) 
        {
            connection.Open();
            connection.Execute(@"EXEC dbo.updateUser @UserId = @UserId @UserPic = @UserPic @Name = @Name @Password = @Password",
                new { UserId = id, UserPic = user.UserPic, Name = user.Name, Password = user.Password });
            return GetUser(id);
        }
    }

Sql code:

CREATE PROC dbo.updateUser
(
 @UserId int,
 @UserPic nvarchar(max),
 @Name nvarchar(150),
 @Password nvarchar(150))AS BEGIN
SET NOCOUNT ON
UPDATE [dbo].[User]
SET [UserPic] = @Userpic, [Name] = @Name, [Password] = @Password
WHERE [UserId] = @UserId
SELECT * FROM [dbo].[User] WHERE [UserId]=@UserId END GO

JSON data to update:

{"UserPic":"href","Name":"Maria","Password":"idj333"}

User model:

    public class User 
{
    public int UserId { get; set; }
    public string UserPic { get; set; }
    public string Email { get; set; }
    public string Name { get; set; }
    public string Password { get; set; }
    public DateTime Registered { get; set; }
    public string Type { get; set; }
}

Your code above suggests that you are storing plain text passwords; I hope you're not. — Thom A, Mar 02 '21 at 12:28
@Liv you forgot to use commas between the parameters. This isn't about .NET Core. You'd get the same error if you tried to execute the query with SSMS or Azure Data Studio — Panagiotis Kanavos, Mar 02 '21 at 12:46
@PanagiotisKanavos thanks a lot! yes, it was a silly comma error :) — Curio, Mar 02 '21 at 13:04
@Larnu no, i'm not, just to explore how the .net core system works in general, i'm a newbie — Curio, Mar 02 '21 at 13:16
But why are you using `EXEC` anyway? Why not use `CommandType.StoredProcedure`? Also, your `update...;select...;` can be optimized `UPDATE [dbo].[User] SET [UserPic] ... OUTPUT inserted.* WHERE...` — Charlieface, Mar 02 '21 at 14:42
@Charlieface idk what is a better approach to implement this, simply it's comfortable to use with Dapper. thanks for optimization advice! ^_^ — Curio, Mar 02 '21 at 16:17
I assume already that this is Dapper: the way to do it is [like this](https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper) `.Execute("dbo.updateUser", new { UserId = id, ...}, commandType: CommandType.StoredProcedure` — Charlieface, Mar 02 '21 at 16:40
Does this answer your question? [Is there a way to call a stored procedure with Dapper?](https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper) — Charlieface, Mar 02 '21 at 16:40

score 0 · Accepted Answer · answered Mar 02 '21 at 12:55

When executing SP with input parameters, you need to user commans between them.

using (var connection = new SqlConnection(connectionString)) 
{
    connection.Open();
    connection.Execute(@"EXEC dbo.updateUser @UserId = @UserId, @UserPic = @UserPic, Name = @Name, @Password = @Password",
        new { UserId = id, UserPic = user.UserPic, Name = user.Name, Password = user.Password });
    return GetUser(id);
}

Should work.

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.SqlClient.dll but was not handled in user code

1 Answers1