'Must declare the scalar variable "@SKU".'

Question

I am using dapper to insert to a database, have been looking at my code to find what has happened, could not find anything out of the ordinary.

The code works with the SELECT statement, but not when I do an insert I always get the error:

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@SKU".

When I remove the first parameter from the database, the class, the function and the procedure, I always get the same error for the first parameter.

public class Products
{
    public string SKU;
    public string Title;
    public string ImageLink;
}

Using dapper function to insert:

public void insertItem(Products newProduct)
{
    using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SellersDB")))
    {
        List<Products> dbnewProduct = new List<Products>();
        dbnewProduct.Add(newProduct);

        connection.Execute("dbo.Item_Insert @SKU, @Title, @ImageLink", dbnewProduct);
    }
}

The procedure:

CREATE PROCEDURE [dbo].[Item_Insert]

@SKU nchar(10),
@Title nchar(100),
@ImageLink nchar(50)

AS
BEGIN
SET NOCOUNT ON;

insert into dbo.ProductsTable (SKU, Title, ImageLink) values (@SKU, @Title, @ImageLink);

END

The database:

CREATE TABLE [dbo].[ProductsTable] (

[SKU]       NCHAR (10)  NULL,
[Title]     NCHAR (100) NULL,
[ImageLink] NCHAR (50)  NULL
);

The error happened on the excite line.

The whole point of an ORM like dapper is that you dont need custom stored procedures. Example: https://dapper-tutorial.net/insert — ADyson, Apr 07 '19 at 09:40
@ADyson not necessarily, [Dapper fully supports stored procedures](https://dapper-tutorial.net/execute) and a lot of people prefer using it this way. Much easier to implement custom logic, validation, etc. with procedures. — MarcinJ, Apr 07 '19 at 09:44
i use it to organize the code and easily change things while developing. — bob mason, Apr 07 '19 at 09:54
@marcinJ...of course, I use them myself with entity Framework. But for a simple insert such as this there doesn't seem much need for a procedure, it's just an extra thing to maintain where all the fields to be inserted must be enumerated (twice). That was my point really. — ADyson, Apr 07 '19 at 09:57

score 2 · Accepted Answer · answered Apr 07 '19 at 09:58

Dapper wants properties, not fields; try:

public class Products
{
    public string SKU {get;set;}
    public string Title {get;set;}
    public string ImageLink {get;set;}
}

and try again; you only need to pass the single object - you don't need a list here. You can combine this with the CommandType.StoredProcedure approach if you choose (as noted by MarcinJ) - but be careful that this exchanges positional parameter passing (in the original question) to named parameter passing - so be sure to check that this doesn't change the meaning.

score 1 · Answer 2 · answered Apr 07 '19 at 09:40

1

The way to call a stored procedure with Dapper is:

connection.Execute("Item_Insert", dbnewProduct, commandType: CommandType.StoredProcedure);

Also, you don't need a list, you can simply use newProduct there.

answered Apr 07 '19 at 09:40

MarcinJ

3,471
2
14
18

it did not work, i got: 'Procedure or function 'Item_Insert' expects parameter '@SKU', which was not supplied.'. – bob mason Apr 07 '19 at 09:49
minor note: this changes from *positional* parameter passing (in the question) to *named* parameter passing. This might be fine, or it might change the meaning. Just something to be careful about. – Marc Gravell Apr 07 '19 at 10:02

'Must declare the scalar variable "@SKU".'

2 Answers2