奇怪的是没有从数据库中获取记录。 [英] Weirdly no records fetch from database.

问题描述

大家好，

我的选择查询面临一个大问题，我无法理解。

问题是：我尝试在给定日期内从数据库表中获取记录，但没有返回任何记录。

我的代码：

 public SQLiteDataReader GetByDate（DateTime start，DateTime end）
 {
 SQLiteDataReader reader = null; 
 using（SQLiteDataAdapter _adpt = new SQLiteDataAdapter（））
 {
 _adpt.SelectCommand = new SQLiteCommand（）; 
 _adpt.SelectCommand.Connection = new SQLiteConnection（ConnectionString）; 
 _adpt.SelectCommand.Connection.Open（）; 
 _adpt.SelectCommand.CommandType = CommandType.Text; 
 _adpt.SelectCommand.CommandText = Scripts.SalesDetails.GetByDate; 
 Console.WriteLine（Start：{0}，start.ToString（yyyy-MM-dd hh：mm：ss））; 
 Console.WriteLine（end：{0}，end.ToString（yyyy-MM-dd hh：mm：ss））; 
 _adpt.SelectCommand.Parameters.AddWithValue（@ start，'+ start.ToString（yyyy-MM-dd hh：mm：ss）+'）; 
 _adpt.SelectCommand.Parameters.AddWithValue（@ end，'+ end.ToString（yyyy-MM-dd hh：mm：ss）+'）; 
 reader = _adpt.SelectCommand.ExecuteReader（）; 
 //Console.WriteLine(reader.HasRows）; 
} 
返回读者; 
} 

查询是：

公共静态字符串GetByDate =选择sd.InvNo sID，cust.ID cID，cust.name name，sd.InvDate，sd.netTotal Total，（sd.netTotal- ct.due）DueAmt，sd.ID from（SalesDetails sd inner join sdd上的Customers cust。 custID = cust.ID）左连接（选择sum（金额）到期，来自custTrans group by SalesID的SalesID）ct on sd.ID = ct.SalesID where sd.InvDate in @start and @end; 

我的桌子上有记录，我在SQLite浏览器上运行相同的代码，返回记录（正如我预期的那样）。

请帮帮我...



提前感谢。



我的尝试：



我的SQLite浏览器查询是（它工作正常）： -

选择sd.InvNo sID，cust.ID cID，cust.name name，sd.InvDate，sd.netTotal Total，（sd.netTotal-ct.due）DueAmt，sd.ID from（SalesDetails sd inner join sd.custID = cust。 ID）左连接（选择sum（amount）到期，SalesID来自custTrans group by SalesID）ct on sd.ID = ct.SalesID其中sd.InvDate介于'2017-05-14 11:09:02'和'2017-06-14 11:09:03' 
 
我也从参数值中删除单引号，但没有任何作用.. 
 
 
 _adpt.SelectCommand.Parameters.AddWithValue（@ start ，start.ToString（yyyy-MM-dd hh：mm：ss））; 
 _adpt.SelectCommand.Parameters.AddWithValue（@ end，end.ToString（yyyy-MM-dd hh：mm：ss））; 
 
解决方案
 
试试这个：
 
 
 _ adpt.SelectCommand.Parameters.AddWithValue（  @ start，start）; 
 _adpt.SelectCommand.Parameters.AddWithValue（  @ end，end）; 
 
 
 
Hi All,

I am facing a big problem with my select query, and I can't figure it out.

Problem is: I try to fetch records from database table within given date, but no records are returned.

My code:

public SQLiteDataReader GetByDate(DateTime start, DateTime end)
        {
            SQLiteDataReader reader = null;
            using (SQLiteDataAdapter _adpt = new SQLiteDataAdapter())
            {
                _adpt.SelectCommand = new SQLiteCommand();
                _adpt.SelectCommand.Connection = new SQLiteConnection(ConnectionString);
                _adpt.SelectCommand.Connection.Open();
                _adpt.SelectCommand.CommandType = CommandType.Text;
                _adpt.SelectCommand.CommandText = Scripts.SalesDetails.GetByDate;
                Console.WriteLine("Start : {0}", start.ToString("yyyy-MM-dd hh:mm:ss"));
                Console.WriteLine("end : {0}", end.ToString("yyyy-MM-dd hh:mm:ss"));
                _adpt.SelectCommand.Parameters.AddWithValue("@start","'"+ start.ToString("yyyy-MM-dd hh:mm:ss")+"'");
                _adpt.SelectCommand.Parameters.AddWithValue("@end","'" +end.ToString("yyyy-MM-dd hh:mm:ss")+"'");
                reader = _adpt.SelectCommand.ExecuteReader();
                //Console.WriteLine(reader.HasRows);
            }
            return reader;
        }


Query is:

public static string GetByDate = "select sd.InvNo sID,cust.ID cID,cust.name name,sd.InvDate, sd.netTotal Total,(sd.netTotal- ct.due) DueAmt,sd.ID from (SalesDetails sd inner join Customers cust on sd.custID =cust.ID ) left join (select sum(amount) due,SalesID from custTrans group by SalesID) ct on sd.ID=ct.SalesID where sd.InvDate between @start and @end";


I have records on my table, and I ran same code on SQLite browser its return the records(as i expected).

please help me with this..



thanks in advance.



What I have tried:



my SQLite browser query is(it works fine):-

select sd.InvNo sID,cust.ID cID,cust.name name,sd.InvDate, sd.netTotal Total,(sd.netTotal- ct.due) DueAmt,sd.ID from (SalesDetails sd inner join Customers cust on sd.custID =cust.ID ) left join (select sum(amount) due,SalesID from custTrans group by SalesID) ct on sd.ID=ct.SalesID where sd.InvDate between '2017-05-14 11:09:02' and '2017-06-14 11:09:03'


I also remove the single quote from parameter value, but nothing works..

_adpt.SelectCommand.Parameters.AddWithValue("@start",start.ToString("yyyy-MM-dd hh:mm:ss"));
_adpt.SelectCommand.Parameters.AddWithValue("@end",end.ToString("yyyy-MM-dd hh:mm:ss"));
解决方案
Try this:

_adpt.SelectCommand.Parameters.AddWithValue("@start",start);
_adpt.SelectCommand.Parameters.AddWithValue("@end",end);


                        
这篇关于奇怪的是没有从数据库中获取记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！