CODEWITHSUNDEEP
c#sql-serverstored-procedures
pikk
pikk

Reputation: 855

Get value from stored procedure

i have the following stored procedure in sql server and i am trying to take the permission value with C#. 

CREATE PROCEDURE [dbo].[GetPermission]
@userName varchar(50),
@permission int output
AS
BEGIN

select @permission = PERMISSION from USERS where UserName = @userName

END;

My C# code is the following:

        SqlCommand cmd = new SqlCommand(
            "sp_getPermission", conn);


        cmd.CommandType = CommandType.StoredProcedure;


        cmd.Parameters.Add(
            new SqlParameter("@UserName", textBox1.Text));
        cmd.Parameters.Add(
           new SqlParameter("@permission", "none"));




        SqlDataReader rdr = null;

        rdr = cmd.ExecuteReader();
        MessageBox.Show( rdr["Permission"].ToString() );

But i get the following error on the last line of C# code:

Invalid attempt to read when no data is present.

Any suggestions?

Upvotes: 0

Views: 6677

Answers (3)

Curtis
Curtis

Reputation: 103428

You need to Read your SqlDataReader:

rdr = cmd.ExecuteReader();
if (rdr.HasRows()){
   rdr.Read()
   MessageBox.Show( rdr["Permission"].ToString() );
   rdr.Close()
}

However, I don't believe you need a SqlDataReader for this situation. The following should work:

cmd.Parameters.Add(
           new SqlParameter("@permission", "none"));
cmd.Parameters["@permission"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

MessageBox.Show(cmd.Parameters["@permission"].Value);

Upvotes: 0

Gustavo F
Gustavo F

Reputation: 2206

Let's simply this:

first, remove the output parameter @permission, then, change your procedure like this:

CREATE PROCEDURE [dbo].[GetPermission]
@userName varchar(50)
AS
BEGIN

select PERMISSION from USERS where UserName = @userName

END;

and for read the permission, use the ExecuteScalar method:

SqlCommand cmd = new SqlCommand(
    "sp_getPermission", conn);


cmd.CommandType = CommandType.StoredProcedure;


cmd.Parameters.Add(
    new SqlParameter("@UserName", textBox1.Text));

var permission = (System.Int32)cmd.ExecuteScalar();

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147374

You need to set the ParameterDirection of the permission parameter on the SqlCommand to ParameterDirection.Output

Also, as you are not resulting a resultset, no need to use ExecuteReader. Just do:

cmd.ExecuteNonQuery();

Upvotes: 0

Related Questions