Getting a bit value from stored procedure in C#

Question

The situation is following, i have a stored procedure in SQL Server , this has a few output parameters, one of them is a bit type, what I want is to take the value of that parameter, but I have a conversion error, InvalidCastException.

This is my code:

public void exec()
       {
            String strConnString = "Server=.\\SQLEXPRESS;Database=recalls;Integrated Security=true";
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "findVinCamp";
            int c = Int32.Parse(campaing.Text);
            cmd.Parameters.Add("@camp", SqlDbType.Int).Value = c;
            cmd.Parameters.Add("@vin", SqlDbType.VarChar, 100).Value = vin.Text;
            cmd.Parameters.Add("@desc", SqlDbType.NVarChar, 255).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@st", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@bit", SqlDbType.Bit).Direction = ParameterDirection.Output;
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            bit = (int)cmd.Parameters["@bit"].Value; //Exception Here
            if (bit == 1)
            {
                desc.Text = cmd.Parameters["@desc"].Value.ToString();
                stt.Text = cmd.Parameters["@st"].Value.ToString();
                camp = cmd.Parameters["@camp"].Value.ToString();
                if (stt.Text.Equals("APPLIED"))
                {
                    stt.ForeColor = System.Drawing.Color.LawnGreen;
                }
                else
                {
                    stt.ForeColor = System.Drawing.Color.Firebrick;
                    label3.Enabled = true;
                    newstatus.Enabled = true;
                    update.Enabled = true;
                }
            }
            else
            {
                MessageBox.Show("Doesn't exits!");
            }
    }

I'm trying to assign the bit parameter to a int variable. Any question post on comments.

What is the exact error you are getting? – Robert Columbia Sep 11 '16 at 01:01 — Robert Columbia, Sep 11 '16 at 01:01
@RobertColumbia `InvalidCastException` – TimeToCode Sep 11 '16 at 01:03 — TimeToCode, Sep 11 '16 at 01:03
@RobertColumbia i put the solution in own answer – TimeToCode Sep 11 '16 at 01:20 — TimeToCode, Sep 11 '16 at 01:20

score 4 · Accepted Answer · answered Sep 11 '16 at 01:12

4

I change the (int) to this, now works perfectly:

Boolean lol = Convert.ToBoolean(cmd.Parameters["@bit"].Value);

answered Sep 11 '16 at 01:12

TimeToCode

901
2
16
34

meganaut · Answer 2 · 2016-09-11T02:16:38.367

1

I believe a bit will convert to a boolean. Which should make your code a bit simpler too.

i.e.

...
var bit = (bool)cmd.Parameters["@bit"].Value;
if (bit)
{
...

edited Sep 11 '16 at 02:16

answered Sep 11 '16 at 01:00

meganaut

493
2
10

Instead of casting to int. Cast to bool. – meganaut Sep 11 '16 at 01:07
You should add that to your answer. – Robert Columbia Sep 11 '16 at 01:13

score 1 · Answer 3 · edited May 23 '17 at 11:53

Use this following Line

bool isConfirmed = (bool)cmd.Parameters["@bit"].Value;
if(isConfirmed ){
                desc.Text = cmd.Parameters["@desc"].Value.ToString();
                stt.Text = cmd.Parameters["@st"].Value.ToString();
                camp = cmd.Parameters["@camp"].Value.ToString();
                if (stt.Text.Equals("APPLIED"))
                {
                    stt.ForeColor = System.Drawing.Color.LawnGreen;
                }
                else
                {
                    stt.ForeColor = System.Drawing.Color.Firebrick;
                    label3.Enabled = true;
                    newstatus.Enabled = true;
                    update.Enabled = true;
                }

}
else{
MessageBox.Show("Doesn't exits!");
}

**UPDATE: **if the bit column allows nulls -- many ways you can do this

bool isConfirmed = cmd.Parameters["@bit"].Value as bool? ?? null;

and also read this- SQL Server Data Types and Their .NET Framework Equivalents

score 0 · Answer 4 · answered Sep 11 '16 at 04:27

0

You are trying to convert your boolean output to INT, please convert it in Boolean:

bool bitValue= Convert.ToBoolean(cmd.Parameters["@bit"].Value)

answered Sep 11 '16 at 04:27

Sandip - Frontend Developer

14,397
4
35
61

Getting a bit value from stored procedure in C#

4 Answers4