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

Solution 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));
    }
}

Solution 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.

Solution 3

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

cmd.CommandType = CommandType.StoredProcedure

Comments

[WorldIsRound]

Did you specify cmd.CommandType = CommandType.StoredProcedure;

[Bastardo]

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

[Bastardo]

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

[Bastardo]

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

[Mormegil]

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

[Bastardo]

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.

[reddi.eth]

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