Passing a parameter to an 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 am trying to write to call the below stored procedure with a 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 much as i understand from the examples i read from here i am sending the 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

Caftan

Did you specify cmd.CommandType = CommandType.StoredProcedure; – Aquiculture

i tried that like ten times, nothing changed but thank you. – Caftan

Answer 1

If you want to call a stored procedure using a SqlCommand, do not execute EXEC GetIslemIdleri, execute just 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));
    }
}

Kristelkristen

i tried that like eight times, nothing changed but thank you. – Caftan

@EmreVeriyaz - Then you have an error elsewhere in your code, I have just tested it locally (on MS SQL 2008 R2), it works correctly. – Kristelkristen

Could also try the shorthand for setting parameters now cmd.Parameters.AddWithValue("@parameter", someValue); – Goldenseal

Answer 2

you forgot to add the prodecure`s name:

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

And do procedure as:

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

This has to work.

Boyette

thanks Mitja. I solved the problem it was actually caused by a null value in my database which causes that the reader is set to null and that error to occur. – Caftan

Answer 3

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

cmd.CommandType = CommandType.StoredProcedure

Bobbee

Answer 4

Set the parameter a bit differenty:

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

Boyette

Answer 5

You're not quite calling the stored procedure properly. You only need to pass the name of the stored procedure (without exec), set the command type to stored procedure, then add the parameters:

var command = new SqlCommand("GetIslemIdleri", conn);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@CARIID", 110));

Bartolome

i tried that like seven times, nothing changed but thank you. – Caftan