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 : stackoverflow.com/questions/37883432/… — Xaero
– Xaero, Commented Jan 14, 2019 at 4:05
Possible duplicate of C# OleDbParameter with Access DateTime query — mjwills
– mjwills, Commented Jan 14, 2019 at 4: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
– Xaero, Commented Jan 14, 2019 at 4:09

Gustav · Accepted Answer · 2019-01-14 07:34:56Z

0

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") + "#;"

answered Jan 14, 2019 at 7:34

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mjwills Over a year ago

Even better would be to use parameters rather than insert the date as a string.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related