CODEWITHSUNDEEP
c#sqldatems-access
Amrit Sharma
Amrit Sharma

Reputation: 1916

SQL query to retrieve data using date as where clause

I want to retrieve data from database using the following sql query but this is not working. I am trying to fetch data according to input date. In database the date is in format of 03/03/2013. Is following query correct for the problem.

 public DataSet OrderByDate(string date1, string date2)
        {


            //  string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Amrit\\Desktop\\Database.accdb ;Persist Security Info=False;";
            DataSet dataSet = new DataSet();
            OleDbConnection oleConn = new OleDbConnection(connString);

            try
            {
                oleConn.Open();
                string sql = "SELECT Order.OrderNumber, (Customer.Title +SPACE(2)+ Customer.CustomerName) as [Customer Name], Customer.CustomerEbayname, Customer.EmailAddress, Customer.PhoneNumber, (Customer.Address1 + SPACE(2) + Customer.Address2 + SPACE(2)+ Customer.City + SPACE(2) + Customer.PostCode + SPACE(2) + Customer.Country) as Address,  Order.ItemPurchased, Order.PurchasedDate, Order.TotalPrice FROM Customer INNER JOIN [Order] ON Customer.[CustomerID] = Order.[CustomerID] WHERE [PurchasedDate] BETWEEN #date1# AND #date2#";                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, oleConn);
                dataAdapter.Fill(dataSet, "Customer");
            }
            catch (Exception ex)
            {
                MessageBox.Show("An exception has been occured\n" + ex.ToString());
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                oleConn.Close();
            }
            if (dataSet.Tables.Count <= 0)
                return null;
            else
                return dataSet;
        }

Upvotes: 0

Views: 1282

Answers (1)

John Woo
John Woo

Reputation: 263933

since you are using Access, wrap the dates with # not by single quotes.

SELECT  [Order].[Order]Number, 
        (Customer.Title +SPACE(2)+ Customer.CustomerName) as [Customer Name],
        Customer.CustomerEbayname, 
        Customer.EmailAddress, 
        Customer.PhoneNumber, 
        (Customer.Address1 + SPACE(2) + Customer.Address2 + SPACE(2)+ Customer.City + SPACE(2) + Customer.PostCode + SPACE(2) + Customer.Country) as Address,  
        [Order].ItemPurchased, 
        [Order].PurchasedDate, 
        [Order].TotalPrice 
FROM    Customer 
        INNER JOIN [Order] 
            ON Customer.[CustomerID] = [Order].[CustomerID]  
WHERE   [PurchasedDate]= #" + date + "#

Upvotes: 1

Related Questions