'Procedure or function '...' expects parameter '...', which was not supplied.'

Question

I was developing my project and this error occurred to me:

"System.Data.SqlClient.SqlException: 'Procedure or function' dbo.Bi_medicaoInset 'expects parameter' @ID_interno_disp ', which was not supplied.'"

I have already searched the internet but I can't find anything to help me.

C# code:

SqlConnection connString = new SqlConnection ("Data Source = (LocalDb) \\ MSSQLLocalDB; Initial Catalog = tupaidb; Integrated Security = True");
                    connString.Open ();
                    
SqlCommand command = new SqlCommand ();
command.Connection = connString;
command.CommandText = "[dbo]. [dbo.Bi_medicaoInset]";
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add ("@ID_interno_disp", SqlDbType.Int);
command.Parameters.Add ("@med_hum", SqlDbType.VarChar, (50)). Value = humidity + "%";
command.Parameters.Add ("@med_temp", SqlDbType.VarChar, (50)). Value = temperature + "ºC";
command.Parameters.Add ("@med_lpg", SqlDbType.VarChar, (50)). Value = lpgGas + "%";
command.Parameters.Add ("@med_co", SqlDbType.VarChar, (50)). Value = monoCarbo + "%";
command.Parameters.Add ("@med_fumo", SqlDbType.VarChar, (50)). Value = smoke + "%";
command.ExecuteNonQuery ();
connString.Close ();

Stored Procedure:

USE [tupaidb]
GO
/ ****** Object: StoredProcedure [dbo]. [Dbo.Bi_medicaoInset] Script Date: 5/18/2021 08:29:17 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo]. [Dbo.Bi_medicaoInset]
    @ID_interno_disp int,
    @med_hum Varchar (50),
    @med_temp Varchar (50),
    @med_lpg Varchar (50),
    @med_co Varchar (50),
    @med_fumo Varchar (50)
AT
BEGIN
    insert into Bi_medicao (ID_interno_disp, med_hum, med_temp, med_lpg, med_co, med_fumo)
    values (@ ID_interno_disp, @ med_hum, @ med_temp, @ med_lpg, @ med_co, @ med_fumo)
END

I want the value of the column "ID_interno_disp" of the table "Bi_dispositivo" to pass to the other table with the same column name.

That typically happens when you set the value of a parameter to C# null rather than SQL DBNull.Value. And I don't know why you have a space between @ and the param name? It may have no effect but I would remove it all the same. — Dale K
observation; there's something very wrong with your DB layout if you have objects in the dbo schema called dbo.Something — Marc Gravell
I'd also argue that storing % at the end of everything is problematic. % feels like a display concern not a storage concern. By storing % there you are making it more expensive to store and more expensive to query. Also, (50) should be 50. — mjwills
The short term answer to your immediate question is to use command.Parameters.Add ("@ID_interno_disp", SqlDbType.Int).Value = 1; — mjwills

Marc Gravell Marc Gravell · Accepted Answer · 2021-05-18T07:34:16

take out the spaces
supply a value (parameters with a null value are not sent)

so:

command.CommandText = "[dbo].[dbo.Bi_medicaoInset]";
// ...
command.Parameters.Add("@ID_interno_disp", SqlDbType.Int).Value = somethingHere;

You may also find it a lot easier to use tools like Dapper, which simplify the ADO.NET API:

using var conn = new SqlConnection ("...");
conn.Execute("[dbo].[dbo.Bi_medicaoInset]",
    new { ID_interno_disp = whatever,
       med_hum = humidity + "%",
       med_temp = temperature + "ºC",
       med_lpg = lpgGas + "%",
       med_co = monoCarbo + "%",
       med_fumo = smoke + "%"
    });

'Procedure or function '...' expects parameter '...', which was not supplied.'

2 Answers