Querying using data in table and local variables (C# and MS-Access, oledb)

Question

I'm trying to Select data from a table using OLEDB Libraries - Essentially I have a date range both dates in fields within a record (Access), I also have another field which is needed to select data.

I have a local variable holding date/time information, I need to output the data where that value held in the variable is in range with the two dates in a record, and the record holds another value which will be compared against a local variable. Any help, just showing basic syntax and querying would be appreciated.

I've looked for comparing and querying between date ranges and have had no luck.

cmd.CommandText = "SELECT FirstName, LastName, Email, PhoneNumber, 
                   NumberOfGuests, StartDate, NumberOfNights, Breakfast, 
                   RoomType, EmployeeName,  Comments FROM Booking WHERE 
                   StartDate <= " + dtpRoomSearch.Value + "AND + 
                   EndDate >= dtpRoomSearch.Value;

Need to output this information as a string (Excuse Bad Syntax, I don't know where I should be putting speech marks etc.)

I need to Query using both Local Variables and Values held in the fields of a table i.e. StartDate ( addressing field in table ) and EndDate (Addressing Field in table compared with local values. Also, how would I go about structuring the syntax?

This is what I've tried : https://stackoverflow.com/questions/37883432/select-data-from-ms-access-database-by-date-in-c-sharp — Xaero, Jan 14 '19 at 04:05
Possible duplicate of [C# OleDbParameter with Access DateTime query](https://stackoverflow.com/questions/16621695/c-sharp-oledbparameter-with-access-datetime-query) — mjwills, Jan 14 '19 at 04:08
What is labelled as [Date] I need as a local variable, and the two dtp values I need as values from a record in a table. I also need to another condition so if a ID i'm checking for (local) matches a value in the table alongside the conditions stated I then need to output all the values that match the criteria. I'm also struggling on outputting these values, it can be in the form of a messagebox, or a table. I'm sorry I haven't got an example to provide because I don't really know where to start. — Xaero, Jan 14 '19 at 04:09

score 0 · Answer 1 · answered Jan 14 '19 at 07:34

You need the correct syntax and format of the string expressions for the date values:

cmd.CommandText = "SELECT FirstName, LastName, Email, PhoneNumber, 
                   NumberOfGuests, StartDate, NumberOfNights, Breakfast, 
                   RoomType, EmployeeName, Comments FROM Booking WHERE 
                   StartDate <= #" + dtpRoomSearch.ToString("yyyy'/'MM'/'dd") + "# AND 
                   EndDate >= #" + dtpRoomSearch.ToString("yyyy'/'MM'/'dd") + "#;"

Even better would be to use parameters rather than insert the date as a string. — mjwills, Jan 14 '19 at 10:55

Querying using data in table and local variables (C# and MS-Access, oledb)

1 Answers1