CODEWITHSUNDEEP
c#sql.netsql-serverstored-procedures
John
John

Reputation: 111

calling procedure from sql

I am coding winform application where i call procedure in my datagrid. I have method where I define parameters of procedure

 public int Add_Nastavenie(out int typNastav, int nastavID, string hod)
    {
        ResetParameters();
        cmd.CommandText = "add_Nastav";
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter sqlParameter;

        var sqlParameterOut = new SqlParameter("@TypNastav", SqlDbType.Int);
        sqlParameterOut.Direction = ParameterDirection.Output;


        sqlParameter = new SqlParameter("@NastavenieID", SqlDbType.Int);
        sqlParameter.Direction = ParameterDirection.Input;
        sqlParameter.Value = nastavID;
        cmd.Parameters.Add(sqlParameter);

        sqlParameter = new SqlParameter("@Hodnota", SqlDbType.NVarChar, 100);
        sqlParameter.Direction = ParameterDirection.Input;
        sqlParameter.Value = hod;
        cmd.Parameters.Add(sqlParameter);

        var sqlParameterRet = new SqlParameter("retValue", SqlDbType.Int);
        sqlParameterRet.Direction = ParameterDirection.ReturnValue;

        cmd.ExecuteNonQuery();
        typNastav = (int)sqlParameterOut.Value;
        return (int)cmd.Parameters["retvalue"].Value;

    }

then i call procedure this way

 dataConnector.Add_Nastavenie(typNastav,nastavID,hod);

I have an error Argument 1 must be passed with the 'out' keyword

I change it to dataConnector.Add_Nastavenie(out typNastav,nastavID,hod);

error dissapear but application is not working, procedure do nothing .

My try catch exception show : Procedure or function 'add_Nastav' expects parameter '@TypNastav', which was not supplied.

Can somebody help find solution ? Thanks .

Upvotes: 3

Views: 954

Answers (2)

user2502256
user2502256

Reputation: 11

Example:

        SqlCommand com = new SqlCommand("update_outptu_Stock", connect.con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@cg_id ", 1);
            SqlParameter par = new SqlParameter("@id", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };

SQL:

Create proc update_outptu_Stock 
@cg_id int,
@id int output
as
begin
    insert into example(cg_id) values (@cg_id)
    set @id  = SCOPE_IDENTITY() -- get value of id created
end
Go

Get parameter id:

var parvalue = id.value;

Upvotes: 1

Darren
Darren

Reputation: 70728

You haven't added the OUTPUT parameter the stored procedure is expecting:

 cmd.Parameters.Add(sqlParameterOut);

Upvotes: 5

Related Questions