来自C#代码的查询输出与SQL Server查询不同 [英] Query from C# code outputting differently from SQL Server query

问题描述

使用:

• ASP.Net Web窗体应用程序
• C#代码不是vb.net
• 具有硬编码测试数据的SQL Server

注意:此问题不会引起任何错误或代码破坏，但是输出结果与预期的不同.

Note: this issue doesn't cause any errors or cause any disruption in the code, however it outputs differently from expected.

我想做的是使用文件后面的代码填充Gridview，用户单击按钮即可更新该代码.

What I am trying to do is populate a Gridview using code behind file, which can be updated by the user on button click.

要填充的代码:

protected void PopulateReport()
{
    // create connection  and add commands
    SqlConnection con = new SqlConnection(GetConnectionString());
    con.Open();

    if(RP_SelectEmp.Text == "ALL")
    {
         string query1 = "SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate FROM TOR WHERE (RequestStartDate > @StartDate)" +
                                    " AND (RequestEndDate < @EndDate) AND (granted = @State)";

         SqlCommand cmd = new SqlCommand(query1, con);

         // needed conversions
         DateTime startD = Convert.ToDateTime(RP_FromDateSelect.Text);
         DateTime endD = Convert.ToDateTime(RP_EndDateSelect.Text);
         Boolean state = Convert.ToBoolean("True");

         // needed parameters
         cmd.Parameters.AddWithValue("@State", state);
         cmd.Parameters.AddWithValue("@StartDate", startD);
         cmd.Parameters.AddWithValue("@EndDate", endD);

         // import into gridview
         using (SqlDataReader reader = cmd.ExecuteReader())
         {
             if (reader.Read())
             {
                 GridView1.DataSource = reader;
                 GridView1.DataBind();
             }
             else
             {
                 RP_ErroField.Text = "failed to bind data (reader not read) check C# code";
             }
         }

         con.Close();
    }
}

这将编译并返回任何错误，但输出:

This compiles and returns no errors but outputs:

数据库表包含所有正确的数据类型和列名:

The database table includes all the correct data types and column names:

我尝试过的事情:

1. 创建一个静态数据源，并从上面的代码中传入相同的选择字符串(这将返回硬编码事件，并具有图片中所示字段的完全相同的输入)-这告诉我查询不是没错添加数据源，正确事件被抓

我尝试更改代码中的转换，DateTime.Parse和Convert.ToDateTime的结果相同.布尔和布尔值也可以这样说

I have tried changing the conversions in the code, DateTime.Parse and Convert.ToDateTime had the same result. Same can be said for bool and Boolean

我分别尝试了where子句，但没有相同的数据可以显示结果.

I have tried the each where clause separately and got the same no data to display result.

我已经调试了2个小时的if语句，并且所有变量数据都按其应有的方式工作(进入if，转换，设置值，运行读取器和数据绑定)

I have debugged this if statement for 2 hrs and all the variable data is doing exactly what it should (going to the if, converting, setting the values, running the reader, and databinding)

我不知道还能尝试什么.我需要有关解决该问题的行动计划的帮助；也许我错过了一些东西，或者我的方法是错误的/过时的.

I don't know what else to try. I would like help on an action plan to fix this; maybe I am missing something, or my approach is wrong/outdated.

推荐答案

这实际上只是调试练习.

This is really just a debugging exercise.

首先，请仔细检查您是否没有简单地将两个日期选择器控件命名为向后！那经常发生.

First, double-check that you haven't simply named the two date-picker controls backwards! That happens a lot.

下一步:转到SSMS，并进行现有查询:

Next: go to SSMS, and take your existing query:

SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate
FROM TOR 
WHERE (RequestStartDate > @StartDate)
AND (RequestEndDate < @EndDate) AND (granted = @State)

现在；我们知道您已经使用Convert.ToDateTime来解析日期，这很棒.您可能想检查要解析的语言(期望要解析的语言)(是1/2/2018是2月1日还是1月2日?)，以及何时100％确定startD和endD的实际日期是什么，请使用明确的格式将它们放在查询中(以帮助我们进行调试)；用state做同样的事情；例如:

Now; we know that you've used Convert.ToDateTime to parse the dates, and that's great. You might want to check the cultures that it is parsing to what you expect it to parse to (is 1/2/2018 the first of Feb? or the 2nd of Jan?), and when you're 100% sure what the actual date of startD and endD are, prepend these to your query using an unambiguous format (just to help us debug); do the same thing with state; for example:

DECLARE @StartDate datetime = '01 Jan 2018';
DECLARE @EndDate datetime = '03 Jan 2018';
DECLARE @State bit = 1;

还是他们?

DECLARE @StartDate datetime = '01 Jan 2018';
DECLARE @EndDate datetime = '01 March 2018';
DECLARE @State bit = 1;

所以现在我们欺骗了参数，并且您有完全相同的查询:运行它. 99％的时间，执行此操作将向您显示查询出了什么问题.我会期望，SSMS中的查询现在的行为就像您的应用程序中的查询一样.所以;现在去修复它！

So now we have spoofed the parameters and you have the exact same query: run it. 99% of the time, doing this will show you what is wrong with the query. I would expect that the query in SSMS now behaves like the query from your application does. So; now go fix it!

这篇关于来自C#代码的查询输出与SQL Server查询不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！