Stack Exchange
Getting weird value in C#
Getting exactly correct data in stored procedure. but when i am trying to get that value in C# datatable getting wrong data. not sure why i am getting that.
Here is my stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetSurveyStatistic]
    (@SurveyID int,
     @NameOfSubmitter varchar(200),
     @NameOfPrivacyContact varchar(200),
     @HspOrganizationalName varchar(200),
     @HspSiteNumber varchar(200),
     @FromDate datetime,
     @ToDate datetime,
     @weekly bit)
AS
BEGIN
    IF (@ToDate IS NOT NULL) 
        SET @ToDate = DATEADD(DAY, 1, @ToDate)

    IF @Weekly = 0 
    BEGIN
        SELECT
            CAST(CAST(StartedDateTime AS date) AS varchar(10)) Value, 
            COUNT(*) cnt
        FROM
            SubmittedSurveys
        WHERE
            (SurveyID = @SurveyID OR @SurveyID IS NULL)
            AND (StartedDateTime >= @FromDate OR @FromDate IS NULL)
            AND (StartedDateTime <= @ToDate OR @ToDate IS NULL)
            AND (LOWER(ProvidedNameOfSubmitter) LIKE LOWER(@NameOfSubmitter) + '%' 
                 OR @NameOfSubmitter IS NULL OR @NameOfSubmitter = '')
            AND (LOWER(NameOfPrivacyContact) LIKE LOWER(@NameOfPrivacyContact) + '%' 
                 OR @NameOfPrivacyContact IS NULL
                 OR @NameOfPrivacyContact = '')
            AND (LOWER(HspOrganizationalName) LIKE LOWER(@HspOrganizationalName) + '%'  
                 OR @HspOrganizationalName IS NULL
                 OR @HspOrganizationalName = '')
            AND (LOWER(HspSiteNumber) LIKE LOWER(@HspSiteNumber) 
                 OR @HspSiteNumber IS NULL OR @HspSiteNumber = '')
        GROUP BY
            CAST(StartedDateTime AS date)
        ORDER BY 
            1
    END
    ELSE BEGIN
        SELECT
            CAST(DATEPART(WEEK, StartedDateTime) AS varchar(10)) Value, 
            COUNT(*) cnt
        FROM
            SubmittedSurveys
        WHERE
            (SurveyID = @SurveyID OR @SurveyID IS NULL)
            AND (StartedDateTime >= @FromDate OR @FromDate IS NULL)
            AND (StartedDateTime <= @ToDate OR @ToDate IS NULL)
            AND (LOWER(ProvidedNameOfSubmitter) LIKE LOWER(@NameOfSubmitter) + '%' 
                 OR @NameOfSubmitter IS NULL OR @NameOfSubmitter = '')
            AND (LOWER(NameOfPrivacyContact) LIKE LOWER(@NameOfPrivacyContact) + '%' 
                 OR @NameOfPrivacyContact IS NULL
                 OR @NameOfPrivacyContact = '')
            AND (LOWER(HspOrganizationalName) LIKE LOWER(@HspOrganizationalName) + '%'  
                 OR @HspOrganizationalName IS NULL
                 OR @HspOrganizationalName = '')
            AND (LOWER(HspSiteNumber) LIKE LOWER(@HspSiteNumber) 
                 OR @HspSiteNumber IS NULL OR @HspSiteNumber = '')
        GROUP BY
            CAST(DATEPART(WEEK, StartedDateTime) AS varchar(10))
        ORDER BY 
            1
    END
END

and here is the results

but in front end getting "Value" ="Date" instead of week value
Here is the code of front end
public static List<SubmittedSurveys> GetSurveyStatistic(int SubmittedSurveyId, DateTime                 
     StartedDateTime, string NameOfSubmitter, string NameOfPrivacyContact, string 
     HspOrganizationalName, string HspSiteNumber,
     DateTime CompletedDateTime,bool weekly)
{
     try
     {
         DatabaseProviderFactory factory = new DatabaseProviderFactory();
         Database _wohcDB = factory.Create("SurveyToolDBEntities");
         // SqlDatabase _wohcDB = DatabaseFactory.CreateDatabase("SurveyToolDBEntities") as 
      SqlDatabase;

         string sqlCommand = "[GetSurveyStatistic]";

         DbCommand dbCommand = _wohcDB.GetStoredProcCommand(sqlCommand);

         _wohcDB.AddInParameter(dbCommand, "@SurveyID", DbType.Int32, SubmittedSurveyId);
         _wohcDB.AddInParameter(dbCommand, "@FromDate", DbType.DateTime, StartedDateTime);
         _wohcDB.AddInParameter(dbCommand, "@ToDate", DbType.DateTime, CompletedDateTime);
         _wohcDB.AddInParameter(dbCommand, "@NameOfSubmitter", DbType.String, NameOfSubmitter);
         _wohcDB.AddInParameter(dbCommand, "@NameOfPrivacyContact", DbType.String, NameOfPrivacyContact);
         _wohcDB.AddInParameter(dbCommand, "@HspOrganizationalName", DbType.String, HspOrganizationalName);
         _wohcDB.AddInParameter(dbCommand, "@HspSiteNumber", DbType.String, HspSiteNumber);
         _wohcDB.AddInParameter(dbCommand, "@weekly", DbType.String, weekly);

         DataSet ds = _wohcDB.ExecuteDataSet(dbCommand);

         _submittedSurvey = ReportsController.DbConverter.DataTable2List<SubmittedSurveys>(ds.Tables[0]);
         //_submittedSurvey[0].SurveyReports = ReportsController.DbConverter.DataTable2List<Reports>(ds.Tables[1]);
    }
    catch (Exception ex)
    {
        string message = ex.Message.ToString();
    }

    return _submittedSurvey;
}

Here is the screenshot of front end where I am getting wrong value as a date instead of week

Can somebody help me to resolve this issue. This is so weird. i also tried to cast in int but getting error that date can not convert into int. I am not sure that why I am getting wrong data in front end and getting right data in backend?
Please help me.

What is the exact value of weekly? Do not guess. Why are you using DbType.String for a boolean / bit?

the weekly Value is 53. I am converting weeks into nvarchar(10). thats why i am using string in front end. to get "53" as a string

I am referring to _wohcDB.AddInParameter(dbCommand, "@weekly", DbType.String, weekly);.

the weekly Value is 53 No it isn't. It has a data type of bool (bool weekly).

sorry corrected but getting same value as date

I will ask again - what is the value of weekly (the C# boolean)?

Just run the else part then it will work fine. with this query it will always go to if statement because of @Weekly =0.

yeah thats worked in my case. As you said i run else part only removed if else statement. Thank you so much

This only worked since you were passing the wrong value of weekly.

Try to Change:
   _wohcDB.AddInParameter(dbCommand, "@weekly", DbType.String, weekly);

To:
   _wohcDB.AddInParameter(dbCommand, "@weekly", DbType.Boolean, weekly);

