SQL Server parameterized query not setting bit value in target table

Question

So I have a stored procedure with these parameters:

ALTER PROCEDURE [dbo].[usp_insertOrUpdateTimeMilesNote]
    @id int,
    @ADID varchar(10),
    @projectId int,
    @taskId tinyint,
    @hours tinyint = 0,
    @minutes tinyint = 0,
    @miles smallint = 0,
    @note varchar(max),
    @filename varchar(50),
    @MSVCFlag bit
AS

And I'm calling it like so:

Using db As New SqlCommand("usp_insertOrUpdateTimeMilesNote", New SqlConnection(ConnStr))
    db.CommandType = CommandType.StoredProcedure
    db.Parameters.Add("@id", SqlDbType.Int).Value = 0
    db.Parameters.Add("@ADID", SqlDbType.VarChar, 10).Value = ADID
    db.Parameters.Add("@projectId", SqlDbType.Int).Value = ProjectId
    db.Parameters.Add("@taskId", SqlDbType.Int).Value = 0
    db.Parameters.Add("@hours", SqlDbType.TinyInt).Value = 0
    db.Parameters.Add("@minutes", SqlDbType.TinyInt).Value = 0
    db.Parameters.Add("@miles", SqlDbType.TinyInt).Value = 0
    db.Parameters.Add("@Note", SqlDbType.VarChar, -1).Value = NoteText
    db.Parameters.Add("@filename", SqlDbType.VarChar, 50).Value = ""
    db.Parameters.Add("@MSVCFlag", SqlDbType.Bit).Value = 1
    db.Connection.Open()
    db.ExecuteNonQuery()
    db.Connection.Close()
End Using

In the target table, the MSVCFlag column is always 0 even though I set the parameter's value to 1.

If I do it as a string, it works fine.

"EXEC usp_insertOrUpdateTimeMilesNote " & id & ",'" & ADID & "'," & projectId & "," & taskId & "," & hours & "," & minutes & "," & miles & ",'" & note & "','" & filename & "'," & MSVCFlag & ";"

So what am I doing wrong with the parameter?

EDIT: Love the down vote. Mind explaining why?

I believe you should set it to True and not to 1 - `db.Parameters.Add("@MSVCFlag", SqlDbType.Bit).Value = True` — stuartd, Jul 18 '16 at 21:42
what does the connection string look like.. try wrapping the `db.ExecuteNonQuery` around a `try{}catch{}` — MethodMan, Jul 18 '16 at 21:45
Tried setting the value to True before, and just tried it again. Still shows 0 in the table after the insert. I read somewhere else on StackOverflow that bit values are supposed to be either 0 or 1. — tolsen64, Jul 18 '16 at 22:08

score 0 · Answer 1 · answered Jul 18 '16 at 21:47

0

var objSqlParameter = new SqlParameter
                                  {
                                      SqlDbType = SqlDbType.Bit,
                                      ParameterName = "@MSVCFlag",
                                      Value = true
                                  };

db.Parameters.Add(objSqlParameter)

answered Jul 18 '16 at 21:47

Hassan

1,413
1
10
12

Isn't this just going in the same room, but using a different door? – tolsen64 Jul 18 '16 at 22:38

score 0 · Answer 2 · answered Jul 18 '16 at 21:48

0

1 is a integer value, and not a bit value. You need to set the parameter value equal to true or false like so:

db.Parameters.Add("@MSVCFlag", SqlDbType.Bit).Value = True

answered Jul 18 '16 at 21:48

AzNjoE

733
3
9
18

I tried this, it doesn't work. Still shows 0 in the table. According to [this](https://msdn.microsoft.com/en-us/library/ms177603.aspx), bit is An integer data type that can take a value of 1, 0, or NULL. – tolsen64 Jul 18 '16 at 22:10

score 0 · Answer 3 · answered Jul 18 '16 at 22:51

Well shame on me for not looking into the sproc more deeply. Seems the sproc doesn't even use that parameter. If you send in an @id = 0, it always writes a 0 to that MSVCFlag column (newly inserted row). If you send in an @id > 0, then it updates the MSVCFlag with a 1 for an existing row. So, I shall do the walk of shame out of here.

SQL Server parameterized query not setting bit value in target table

3 Answers3