Passing parameter to sql stored procedure in C #

Question

string commandGetIslemIdleri = ("EXEC GetIslemIdleri"); cmd = new SqlCommand(commandGetIslemIdleri, sqlConn); cmd.Parameters.Add(new SqlParameter("@CARIID", 110)); using (var reader = cmd.ExecuteReader()) //error occurs here { while (reader.Read()) { islemidleri.Add(reader.GetInt32(0)); } } 

Above is the code I'm trying to write to invoke the stored procedure below with the parameter CARIID , which is an integer. when I run the code, an error occurs and says "Procedure or function 'GetIslemIdleri' expects parameter '@CARIID', which was not supplied." but as far as I understand from the examples that I read from here , I am sending a parameter with this code cmd.Parameters.Add(new SqlParameter("@CARIID", 110)); I need help, thank you in advance.

 ALTER PROCEDURE [dbo].[GetIslemIdleri] @CARIID int AS BEGIN SET NOCOUNT ON; SELECT ID FROM TBLP1ISLEM WHERE TBLP1ISLEM.CARI_ID=@CARIID END 

Answer 1

If you want to call a stored procedure using SqlCommand, do not execute EXEC GetIslemIdleri , only execute GetIslemIdleri , setting CommandType to CommandType.StoredProcedure :

 cmd = new SqlCommand("GetIslemIdleri", sqlConn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@CARIID", 110)); using (var reader = cmd.ExecuteReader()) //error occurs here { while (reader.Read()) { islemidleri.Add(reader.GetInt32(0)); } } 

Answer 2

You need to make sure your SqlCommand is set to CommandType.StoredProcedure.

 cmd.CommandType = CommandType.StoredProcedure 

Answer 3

you forgot to add the name prodecure:

 cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GetIslemIdleri"; cmd.Parameters.Add("@CARIID", SqlDBType.Int).Value = 110; 

And follow the procedure as:

 CREATE PROCEDURE [dbo].[GetIslemIdleri] ( @CARIID int ) AS BEGIN SET NOCOUNT ON; SELECT ID FROM TBLP1ISLEM WHERE TBLP1ISLEM.CARI_ID = @CARIID END 

That should work.

Answer 4

Set the parameter a little differently:

 cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CARIID", SqlDBType.Int).Value = 110; 

Answer 5

You do not quite correctly call the stored procedure. You only need to pass the name of the stored procedure (without exec), set the type of the command to the stored procedure, and then add the parameters:

 var command = new SqlCommand("GetIslemIdleri", conn); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@CARIID", 110));