Error when trying to get dates given a condition from Access database in WPF

Biel

I'm developing a WPF application that saves user registers and dates into an Access database. I've had no problems inserting the dates, but now I want to retrieve with a DataReader some dates given a certain condition (e.g., all dates from the current month). When I try to read it through my application, the DataReader returns no rows. But when I try the same query on DBeaver, it does return data.

This reading problem only happens when I try to read dates given a certain condition as I said before, since if I try to read all the dates, there's no issues.

I've tried using a parameterized command and also directly inserting the condition with String.Format().

I currently have this function (which tries to calculate how many days in the month are left if we don't count the days saved in the DB):

public static int CalculateDaysLeftInMonth()
{
    int days = 0;
    List<DateTime> dates = new List<DateTime>();

    try
    {
        if (dbConnection.State != ConnectionState.Open)
            dbConnection.Open();

       OleDbCommand dbCommandQuery = new OleDbCommand {
            CommandText = "SELECT * FROM DatesTable WHERE Date LIKE @Condition"
        }

        dbCommandQuery.Parameters.AddWithValue("@Condition", String.Format("{0:yyyy-MM}-%", DateTime.Now));

        OleDbDataReader dbReader = dbCommandQuery.ExecuteReader();

        if (!dbReader.HasRows)
            return -1;

        while (dbReader.Read())
        {
            dates.Add(new FechaFestivo(dbReader.GetDateTime(0).Date));
        }

        dbConnection.Close();

        DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1),
                 endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 
                           DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));

        for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
        {
            if (!dates.Any(ff => ff.Date == date.Date))
            {
                days++;
            }
        }
    }
    catch (OleDbException ex)
    {
        dbConnection.Close();
        return -1;
    }

    return days;
}

This is the parameterized version. I've also tried using:

dbCommandQuery.CommandText = String.Format("SELECT * FROM DatesTable WHERE Date " +
"LIKE '{0:yyyy-MM}-%'", DateTime.Now);

I expect to get a List of dates like this, so I can iterate through them:

list of dates on DBeaver

(CalendarioLaboral would be DatesTable and FechaFestivo would be Dates)

Thank you in advance.

Biel

The code that worked for me in the end is the following one:

public static int CalculateDaysLeftInMonth()
{
    int days = 0;
    List<DateTime> dates = new List<DateTime>();

    try
    {
        if (dbConnection.State != ConnectionState.Open)
            dbConnection.Open();

       OleDbCommand dbCommand = new OleDbCommand {
           CommandText = String.Format("SELECT * FROM {0} WHERE (DATEPART('yyyy', FechaFestivo) = @Year " +
                                       "AND DATEPART ('m', FechaFestivo) = @Month)", TablaCalendario)
       }

       dbCommandQuery.Parameters.AddWithValue("@Year", DateTime.Now.Year);
       dbCommandQuery.Parameters.AddWithValue("@Month", DateTime.Now.Month);

        OleDbDataReader dbReader = dbCommandQuery.ExecuteReader();

        if (!dbReader.HasRows)
            return -1;

        while (dbReader.Read())
        {
            dates.Add(new FechaFestivo(dbReader.GetDateTime(0).Date));
        }

        dbConnection.Close();

        DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1),
                 endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 
                           DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));

        for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
        {
            if (!dates.Any(ff => ff.Date == date.Date))
            {
                days++;
            }
        }
    }
    catch (OleDbException ex)
    {
        dbConnection.Close();
        return -1;
    }

    return days;
}

Using the DatePart function as said @Holger and @June7 in the comments. Thank you!

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Getting error "No Value Given for One or More Parameters" when trying to read from an Access Database with C# and Windows Forms

I am getting an error when trying to get information from the database

Error trying to access database from Windows Service

Error when trying to retrieve from database

I get an error when trying to compare dates in Python pandas dataframe

Syntax error when trying to access a MySql database via Slick

Why do I get this error when I'm trying to create models from database?

Error when trying to get sum of column from database in Symfony2

When trying to retrieve data from the database I get an error "InvalidOperationException was unhandled"

Error when trying to get data from Firestore

I continue to get a key error when trying to access my dictionary

sendgrid error "Access forbidden" when trying to GET user profile API

Error when trying to import from Database with Pandas and SQLAlchemy

Getting an undefined error when trying to print results from database

Error when trying to out out data from a database

React error when trying to pull multiple items from Firestore database

XPath error - Trying to get user by name from the database

StorageException occuring when trying to get image from firebase database into storage

memory exhausted when trying to get data from database (android)

Compilation error when trying to access an attribute from a local class method

403 Forbidden error when trying to access Kubernetes API from a pod

Error when trying to get data from UserDefaults when it is empty

Get error when trying to get a value from a json

Get an error in url, when trying to get data from API

PLACES_API_INVALID_APP error given when trying to access Google Play Services Android

Error when trying to access nativeLibraryDir

Given error when trying to iterate through an exception

vbscript error trying to connect to access database

error when trying to get value from function in react