从数据库返回空值 [英] Return null values from database

问题描述

Hello Guys从数据库中选择一个数据，第二次当我从where子句dt1中选择数据时，我得到返回null值没有得到任何错误但是它返回我的空值



我尝试过：

Hello Guys am select a data from database and second time when i select data from with where clause dt1 so i get return null values am not getting any error but it's return me null values

What I have tried:

con_string.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\Restaurant.accdb;Persist Security Info=False";
            con_string.Open();
            DataSet dsa2 = new DataSet();
            DataTable dt2 = new DataTable();
            dsa2.Tables.Add(dt2);
            OleDbDataAdapter da2 = new OleDbDataAdapter();
            da2 = new OleDbDataAdapter(string.Format("SELECT column2 FROM Total  Where [Date] between #{0}# AND #{1}#  Group By column2", dateTimePicker1.Text, dateTimePicker2.Text), con_string);
            da2.Fill(dt2);
            con_string.Close();
            //--------------------------------------------------------------------------------
            con_string.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\Restaurant.accdb;Persist Security Info=False";
            con_string.Open();
            DataSet dsa = new DataSet();
            DataTable dt1 = new DataTable();
            dsa.Tables.Add(dt1);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da = new OleDbDataAdapter(string.Format(string.Format("SELECT column2,Sum(MediumVal) As [Dine In], Sum(LargeVal) As [Deliery], Sum(RoyalVal) As [Take Away] From ( SELECT column2 As [column2], Switch(column3 like 'DineIn%', 1,True,0) As [MediumVal], Switch(column3 like 'Delivery%',1,True,0) As [LargeVal], Switch(column3 like 'TakeAway%', 1,True,0) As [RoyalVal] FROM Total  Where [Date] between #{{0}}# AND #{{1}}# AND [column2] IN('{0}') ) Group By column2", dt2.Columns["column2"]), dateTimePicker1.Text, dateTimePicker2.Text), con_string);
            da.Fill(dt1);
            dataGridView1.DataSource = dt1;
            con_string.Close();
            dataGridView1.Columns[0].Width = 286;
            dataGridView1.Columns[1].Width = 180;
            dataGridView1.Columns[2].Width = 180;
            dataGridView1.Columns[3].Width = 180;

推荐答案

原因是日期格式没有无论出于何种原因匹配，所以SQL错误，或者没有数据属于范围。



首先不要连接字符串以形成SQL命令：use参数化查询并直接过去DateTimePicker DateTime值，而不是先将它们转换为字符串。这样，SQL使用实际的DateTime，在处理字符串时没有任何误解的风险。您永远不应该连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。始终使用参数化查询。



然后检查您的数据库：确保将[Date]列存储为DATE，DATETIME或DATETIME2值，而不是串。是的，是的 - 我知道。但是你会惊讶地发现有多少人将日期存储为NVARCHAR然后当BETWEEN之类的比较使用NVARCHAR比较规则而不是DATE并且他们没有得到他们想要的结果时会感到惊讶。



一旦全部检查完毕，使用调试器查看你传递的日期范围：SQL / Access不会重新排序开始和结束以使它们合理，所以如果以后的日期是第一个，那么你不会得到任何结果。如果这一切都很好，直接查看数据库表并手动验证该范围内的日期确实存在。



对不起，但我们不能做任何为你！

And the reason is either the date formats don't match for whatever reason, so SQL is getting them wrong, or there is no data which falls in the range.

Start by not concatenating strings to form SQL commands: use a parameterised query and past the DateTimePicker DateTime values directly instead of converting them to strings first. That way, the actual DateTime is used by SQL without any risk of misinterpretation when the string is processed. You should never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

Then check your DB: make sure you are storing the [Date] column as a DATE, DATETIME, or DATETIME2 value, not as a string. Yes, yes - I know. But you'd be surprised how many people do store dates as NVARCHAR and then act all surprised when comparisons like BETWEEN use NVARCHAR comparison rules not DATE and they don't get the results they wanted.

Once that's all checked, use the debugger to look at the date ranges you are passing: SQL / Access will not "reorder" the start and end to make them sensible, so if the later date is first, you won't get any results. If that's all fine, look directly at the database table and manually verify that dates in that range do really exist.

Sorry, but we can't do any of that for you!

这个查询的一部分可能是问题。代码有两个大括号而不是一个。

Part of this query could be the issue. The code have double curly brackets instead of one.

FROM Total  Where [Date] between #{{0}}# AND #{{1}}# AND [column2]

应该是

It should be

FROM Total  Where [Date] between #{0}# AND #{1}# AND [column2]

这篇关于从数据库返回空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！