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

Did you specify cmd.CommandType = CommandType.StoredProcedure; — WorldIsRound, Mar 23 '11 at 15:45

score 7 · Accepted Answer · answered Mar 23 '11 at 15:46

7

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

answered Mar 23 '11 at 15:46

Mormegil

7,955
4
42
77

i tried that like eight times, nothing changed but thank you. – Bastardo Mar 23 '11 at 15:48
@EmreVeriyaz - Then you have an error elsewhere in your code, I have just tested it locally (on MS SQL 2008 R2), it works correctly. – Mormegil Mar 23 '11 at 16:12
Could also try the shorthand for setting parameters now `cmd.Parameters.AddWithValue("@parameter", someValue);` – reddi.tech Feb 23 '20 at 05:57

score 3 · Answer 2 · answered Mar 24 '11 at 14:37

3

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.

answered Mar 24 '11 at 14:37

Mitja Bonca

4,268
5
24
30

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. – Bastardo Mar 24 '11 at 14:49

score 2 · Answer 3 · answered Mar 23 '11 at 15:45

2

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

cmd.CommandType = CommandType.StoredProcedure

answered Mar 23 '11 at 15:45

Tejs

40,736
10
68
86

score 0 · Answer 4 · answered Mar 23 '11 at 15:45

0

Set the parameter a bit differenty:

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

answered Mar 23 '11 at 15:45

Mitja Bonca

4,268
5
24
30

score 0 · Answer 5 · answered Mar 23 '11 at 15:46

0

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

answered Mar 23 '11 at 15:46

Justin Niessner

242,243
40
408
536

i tried that like seven times, nothing changed but thank you. – Bastardo Mar 23 '11 at 15:47

Passing a parameter to an sql stored procedure in c#

5 Answers5