Value returned from SQL Server stored procedure not converting

First Last

6y1.3k.NET

I have a row returned from SQL Server and an integer field value that is not converting properly to boolean in C#.

I want to convert an integer 1 to boolean true. But it does not. It converts the 1 to false or thinks it is 0 and converts to false.

blogPublishedByBlogId.LikeDisabled = Convert.ToBoolean(getblogPublishedByBlogIdReader["LikeDisabled"]);

Here is the row returned from the stored procedure:

The `LikeDisabled` column is a 1, however it gets converted to a 0 in C#. Why? A simple thing like this should work. It does not make sense. The integer column LikeCount works fine.

Here is the code that shows it after the stored procedure is called and taking the data and putting it into a model.

I put in 2 lines of test set of code to see the integer value, so I convert to integer which is returned by the store procedure as 1, yet I get a 0.

Here is the fully populated model a few lines down. The booleans are both false when the `LikeDisabled` one should be = true.

Here is the model class:

using System;
using System.ComponentModel.DataAnnotations;
namespace GbngWebApi2.Models
{
public class BlogPublishedByBlogId
{
public int BlogId { get; set; }
public string BlogTitle { get; set; }
public string BlogContent { get; set; }
public int LikeCount { get; set; }
public int DisLikeCount { get; set; }
public DateTime ModifiedDateTime { get; set; }
public DateTime CreatedDateTime { get; set; }
public bool LikeDisabled { get; set; }
public bool DisLikeDisabled { get; set; }
}
}

Here is the call to the stored procedure and it is the screen shots above:

public BlogPublishedByBlogIdResults GetBlogPublishedByBlogId(string userName, string ipAddress, int blogId, int userId)
{
BlogPublishedByBlogIdResults blogPublishedByBlogIdResults = new BlogPublishedByBlogIdResults();
SqlDataReader getblogPublishedByBlogIdReader = null;
try
{
dbFunc.OpenDB();
SqlCommand cmd = new SqlCommand("dbo.GetBlogPublishedByBlogId", dbFunc.objConn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@a_UserName", userName);
cmd.Parameters.AddWithValue("@a_IpAddress", ipAddress);
cmd.Parameters.AddWithValue("@a_BlogId", blogId);
cmd.Parameters.AddWithValue("@a_UserId", userId);
getblogPublishedByBlogIdReader = cmd.ExecuteReader();
// There will be only 1 entry.
while (getblogPublishedByBlogIdReader.Read())
{
BlogPublishedByBlogId blogPublishedByBlogId = new BlogPublishedByBlogId();
blogPublishedByBlogIdResults.Status = Convert.ToInt32(getblogPublishedByBlogIdReader["Status"]);
blogPublishedByBlogId.BlogId = Convert.ToInt32(getblogPublishedByBlogIdReader["BlogId"]);
blogPublishedByBlogId.BlogTitle = getblogPublishedByBlogIdReader["BlogTitle"].ToString();
blogPublishedByBlogId.BlogContent = getblogPublishedByBlogIdReader["BlogContent"].ToString();
blogPublishedByBlogId.LikeCount = Convert.ToInt32(getblogPublishedByBlogIdReader["LikeCount"]);
blogPublishedByBlogId.DisLikeCount = Convert.ToInt32(getblogPublishedByBlogIdReader["DisLikeCount"]);
blogPublishedByBlogId.ModifiedDateTime = Convert.ToDateTime(getblogPublishedByBlogIdReader["ModifiedDateTime"]);
blogPublishedByBlogId.CreatedDateTime = Convert.ToDateTime(getblogPublishedByBlogIdReader["CreatedDateTime"]);
// Test code to see what the value is before trying to convert to boolean below.
int likeDisabled = Convert.ToInt32(getblogPublishedByBlogIdReader["LikeDisabled"]);
int DislikeDisabled = Convert.ToInt32(getblogPublishedByBlogIdReader["DisLikeDisabled"]);
blogPublishedByBlogId.LikeDisabled = Convert.ToBoolean(getblogPublishedByBlogIdReader["LikeDisabled"]);
blogPublishedByBlogId.DisLikeDisabled = Convert.ToBoolean(getblogPublishedByBlogIdReader["DisLikeDisabled"]);
blogPublishedByBlogIdResults.BlogPublishedByBlogId = blogPublishedByBlogId;
}
return blogPublishedByBlogIdResults;
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
}
finally
{
if (getblogPublishedByBlogIdReader != null)
{
getblogPublishedByBlogIdReader.Close();
}
dbFunc.CloseDB();
}
}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

First Last6y ago
I discovered I was not sending in a parameter to the stored procdure. So this is not a problem at all. Thanks anyway.
First Last6y ago
Here is the stored procedure being called:
USE [DBGbngDev]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBlogPublishedByBlogId]')
AND type in (N'P', N'PC'))
    BEGIN
       DROP PROCEDURE [dbo].GetBlogPublishedByBlogId
    END
GO

CREATE procedure [dbo].[GetBlogPublishedByBlogId]

@a_UserName   varchar(250) = NULL,
@a_IpAddress  varchar(250) = NULL,
@a_BlogId     int = NULL,
@a_UserId     int = NULL

AS
BEGIN

    DECLARE @RowCount                  int,
        @ReturnCode                int,
        @CurrentDateTime           datetime,
        @Count                     int,
        @UserType                  varchar(25) = '',
        @LikeOrDislikeIndicator    char(1) = '',   -- 'L' for like, 'D' for dislike.
        @BlogId                    int = 0,
        @BlogTitle                 varchar(250) = '',
        @BlogContent               varchar(max) = '',
        @LikeCount                 int = 0,
        @DisLikeCount              int = 0,
        @ModifiedDateTime          datetime = NULL,
        @CreatedDateTime           datetime = NULL,
        @LikeDisabled              int = 0,
        @DisLikeDisabled           int = 0,
        @Message                   varchar(max),
        @ApiMessageOut             varchar(max),
        @ApiAccessSwitchOut        bit

    SELECT @CurrentDateTime = GETDATE()


    DECLARE @ErrorLine      AS INT;
    DECLARE @ErrorMessage       AS VARCHAR(2048);
    DECLARE @ErrorNumber        AS INT;
    DECLARE @ErrorSeverity      AS INT;
    DECLARE @ErrorState     AS INT;
    DECLARE @DatabaseName       AS VARCHAR(255);
    DECLARE @ServerName     AS VARCHAR(255);
    DECLARE @ErrorDescription   AS VARCHAR(MAX);
    DECLARE @CRLF           AS VARCHAR(2);

    BEGIN TRY

       SET NOCOUNT ON;

       IF ( ( @a_UserName  = '' OR @a_UserName  IS NULL ) OR ( @a_IpAddress = '' OR @a_IpAddress IS NULL )
       OR ( @a_BlogId IS NULL ) OR ( @a_UserId IS NULL ) )
          BEGIN
             SELECT @Message = 'Warning - invalid parameters. They cannot be null or empty.'

             IF ( @a_UserName = '' OR @a_UserName IS NULL )
                 BEGIN
                    SET @a_UserName = 'No "user name" parameter provided.'
                 END

             IF ( @a_IpAddress = '' OR @a_IpAddress IS NULL )
                 BEGIN
                    SET @a_IpAddress = 'No "ip address" parameter provided.'
                 END

            RAISERROR (@Message, 16, 1)
          END
       ELSE
          BEGIN
               -- Do the API security check. If this user is valid, you can continue with further processing.
               SELECT @ReturnCode = -1
               EXECUTE @ReturnCode = dbo.GetApiAccess
                         @CurrentDateTime,
                         @a_UserName,
                         @a_IpAddress,
                         @a_ApiAccessSwitchFromGet = @ApiAccessSwitchOut OUTPUT,
                         @a_ApiMessageFromGet = @ApiMessageOut OUTPUT

               IF @ReturnCode = -1
                    BEGIN
                       RAISERROR ('Critical Error - procedure GetBlogPublishedByBlogId failed during execute of procedure GetApiAccess', 16, 1 )
                    END

               -- Web api access was granted.
               IF @ApiAccessSwitchOut = 1
                   BEGIN
                       SELECT @BlogId = Blogid
                          ,@BlogTitle = BlogTitle
                          ,@BlogContent = BlogContent
                          ,@LikeCount = LikeCount
                          ,@DisLikeCount = DisLikeCount
                          ,@ModifiedDateTime = ModifiedDateTime
                                      ,@CreatedDateTime = CreatedDateTime
                       FROM dbo.Blog
                       WHERE ( BlogId = @a_BlogId AND ActiveSwitch = 1 )

                       SELECT @ReturnCode = @@ERROR,
                                      @RowCount = @@ROWCOUNT

                       IF @ReturnCode <> 0
                           BEGIN
                              SELECT @Message = 'Critical Error - procedure GetBlogPublishedByBlogId during the 1st select.'
                              RAISERROR (@Message, 16, 1)
                           END

                        IF @RowCount = 0
                            BEGIN
                                   SELECT 2 as Status
                                  ,0 as Blogid
                                  ,'' as BlogTitle
                                  ,'' as BlogContent
                                  ,0 as LikeCount
                                  ,0 as DisLikeCount
                                  ,NULL as ModifiedDateTime
                                                  ,NULL as CreatedDateTime
                                  ,@LikeDisabled as LikeDisabled
                                          ,@DisLikeDisabled as DisLikeDisabled
                            END
                        ELSE
                            BEGIN
                            SELECT @LikeOrDislikeIndicator = LikeOrDislikeIndicator
                            FROM dbo.[UserBlogPreference]
                            WHERE ( BlogId = @a_BlogId AND UserId = @a_UserId )

                            SELECT @ReturnCode = @@ERROR,
                                   @RowCount = @@ROWCOUNT

                                IF @ReturnCode <> 0
                                   BEGIN
                                    SELECT @Message = 'Critical Error - procedure GetBlogPublishedByBlogId during the 2nd select.'
                                    RAISERROR (@Message, 16, 1)
                                   END

                                IF ( @RowCount = 0 )
                                    BEGIN
                                        SELECT @LikeDisabled = 0
                                                               ,@DisLikeDisabled = 0
                                    END
                                ELSE
                                    BEGIN
                                    IF @LikeOrDislikeIndicator = 'L'
                                       BEGIN
                                        SELECT @LikeDisabled = 1
                                                                   ,@DisLikeDisabled = 0
                                       END
                                    ELSE
                                       BEGIN
                                          SELECT @LikeDisabled = 0
                                                                 ,@DisLikeDisabled = 1
                                       END
                                    END

                                -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                                -- Return data.
                                --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                                             SELECT 1 as Status
                                         ,@Blogid as Blogid
                                         ,@BlogTitle as BlogTitle
                                         ,@BlogContent as BlogContent
                                         ,@LikeCount as LikeCount
                                         ,@DisLikeCount as DisLikeCount
                                         ,@ModifiedDateTime as ModifiedDateTime
                                                                 ,@CreatedDateTime as CreatedDateTime
                                         ,@LikeDisabled as LikeDisabled
                                                         ,@DisLikeDisabled as DisLikeDisabled
                            END
                   END
               ELSE
                   BEGIN

                      RAISERROR (@ApiMessageOut, 16, 1 )
                  END
          END

       RETURN 0

    END TRY

    BEGIN CATCH
        -- Gather these first.
        SELECT @ErrorLine = ERROR_LINE()
               ,@ErrorMessage = ERROR_MESSAGE()
               ,@ErrorNumber = ERROR_NUMBER()
               ,@ErrorSeverity = ERROR_SEVERITY()
               ,@ErrorState = ERROR_STATE()
               ,@DatabaseName = CAST(DB_NAME() AS VARCHAR)
               ,@ServerName = CAST(SERVERPROPERTY ( 'ServerName' ) AS VARCHAR)
               ,@CRLF = CHAR(13) + CHAR(10)


            SET @ErrorDescription = 'From stored procedure: '  + ERROR_PROCEDURE()
                                                + '. Error Line: '  + CAST(@ErrorLine AS VARCHAR)
                                                + '. Error Message: '   + @ErrorMessage
                                            + ' Error Number: ' + CAST(@ErrorNumber AS VARCHAR)
                                    + '. Error Severity: ' + CAST(@ErrorSeverity AS VARCHAR)
                                            + '. Error State: ' + CAST(@ErrorState AS VARCHAR)
                                            + '. Database Name: '  + @DatabaseName
                                            + '. Server Name: ' + @ServerName

        IF (XACT_STATE() <> 0)
            BEGIN
               ROLLBACK TRANSACTION
            END

        IF (@ErrorSeverity = 16) AND (@ErrorState = 2)
            BEGIN
               -- Just send the validation message.
               RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

            END
         ELSE
             -- Otherwise, it's a more severe, log it, and send a detailed message.
            BEGIN
               -- Log the critical error.
               BEGIN TRY
                  EXEC dbo.InsertBlogErrorLog
                     @a_LogDateTime = @CurrentDateTime,
                     @a_UserName = @a_UserName,
                     @a_UserIpAddress = @a_IpAddress,         -- Setting to the user's Ip Address.
                     @a_ObjectID = @@PROCID,
                     @a_MessageType = 'S/P Critical Error',
                     @a_LogMessage = @ErrorDescription
               END TRY

               BEGIN CATCH
                  -- Stack the messages.
                  SELECT @Message = 'Critical Error - procedure InsertBlogErrorLog failed. A log entry cannot be made. Do not continue. Contact IT. Initial error message: ' + @ErrorMessage
                  RAISERROR(@Message, 16, 1)
               END CATCH

               SELECT @message = 'Critical Error - do not continue. Contact IT and provide this log date: ' + Convert(VARCHAR, @CurrentDateTime,21)
               RAISERROR(@Message, 16, 1)
            END

         -- Returns failure.
         RETURN 1

    END CATCH

END
First Last6y ago
Here is the table definition:
USE [DBGbngDev]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Blog]
(
  [BlogId] [int] IDENTITY(1,1) NOT NULL,             -- PK
  [UserId] [int] NOT NULL,                           -- FK, the author of the blog.
  [BlogCategoryId] [int] NOT NULL,                   -- FK
  [BlogTitle] [varchar](250) NOT NULL,
  [BlogContent] [varchar](max) NOT NULL,
  [ActiveSwitch] [bit] NOT NULL,
  [LikeCount] [int] NOT NULL,
  [DisLikeCount] [int] NOT NULL,
  [ModifiedDateTime] [datetime] NOT NULL,
  [ModifiedUserId] [int] NOT NULL,
  [CreatedDateTime] [datetime] NOT NULL,
  [CreatedUserId] [int] NOT NULL,
CONSTRAINT [PK_Blog] PRIMARY KEY CLUSTERED
(
    [BlogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Blog] WITH CHECK ADD CONSTRAINT [FK_Blog_UserId] FOREIGN KEY([UserId]) REFERENCES [dbo].[User] ([UserId])
GO

ALTER TABLE [dbo].[Blog] WITH CHECK ADD CONSTRAINT [FK_Blog_BlogCategoryId] FOREIGN KEY([BlogCategoryId]) REFERENCES [dbo].[BlogCategory] ([BlogCategoryId])
GO

ALTER TABLE [dbo].[Blog] ADD  CONSTRAINT [DF_Blog_CreatedDateTime]  DEFAULT (getdate()) FOR [CreatedDateTime]
GO

ALTER TABLE [dbo].[Blog] ADD  CONSTRAINT [DF_Blog_ModifiedDateTime]  DEFAULT (getdate()) FOR [ModifiedDateTime]
GO

SET ANSI_PADDING OFF
GO
Jignesh Kumar6y ago
Hi,

Could you please share your procedure and table structure ? Which will help us to identify root cause.
First Last6y ago
Jignesh...thats the problem, I am NOT getting the correct value. int likeDisabled receives the result of the convert function which should be a 1 as that is what is returned from the stored procedure, but the outcome is a 0.
int likeDisabled = Convert.ToInt32(getblogPublishedByBlogIdReader["LikeDisabled"]);
Jignesh Kumar6y ago
Hi,

Please if you are getting int values correct from procedure then convert that into bool values. Please have a look below code,
// Test code to see what the value is before trying to convert to boolean below.
               int likeDisabled = Convert.ToInt32(getblogPublishedByBlogIdReader["LikeDisabled"]);
               int DislikeDisabled = Convert.ToInt32(getblogPublishedByBlogIdReader["DisLikeDisabled"]);

               blogPublishedByBlogId.LikeDisabled = Convert.ToBoolean(likeDisabled);

               blogPublishedByBlogId.DisLikeDisabled = Convert.ToBoolean(DislikeDisabled);