CODEWITHSUNDEEP
c#stored-proceduresparameter-passingsqlcommand
Bastardo
Bastardo

Reputation: 4152

Passing a parameter to an sql stored procedure in c#

        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

Upvotes: 7

Views: 43092

Answers (5)

Mitja Bonca
Mitja Bonca

Reputation: 4546

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.

Upvotes: 3

Justin Niessner
Justin Niessner

Reputation: 245399

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

Upvotes: 0

Mormegil
Mormegil

Reputation: 8071

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

Upvotes: 7

Tejs
Tejs

Reputation: 41236

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

cmd.CommandType = CommandType.StoredProcedure

Upvotes: 2

Mitja Bonca
Mitja Bonca

Reputation: 4546

Set the parameter a bit differenty:

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

Upvotes: 0

Related Questions