Click here to Skip to main content
15,068,645 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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"));
Posted
Updated 13-Jun-17 21:17pm
v3

1 solution

Try this:
C#
_adpt.SelectCommand.Parameters.AddWithValue("@start",start);
_adpt.SelectCommand.Parameters.AddWithValue("@end",end);
   
Comments
JayantaChatterjee 14-Jun-17 3:22am
   
I also tried that, but no result.. :-(
Mehdi Gholam 14-Jun-17 3:29am
   
Try yyyy-MM-dd HH:mm:ss
JayantaChatterjee 14-Jun-17 3:35am
   
same, no result..
is there anything I missed?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900