SQLLite - Select bookings between two dates

Question

I am trying to use SQLlite for a hostel booking system created using Xamarin.Forms. I would like to search for booking records that exist between two dates of a given ID and room type.

// Count number of bookings between these dates at the hostel for the specified room type
int count = App.Database.CheckValidBooking(hostel_ID, hostel_room_type, DPickerFrom.Date, DPickerTo.Date).Count();

public List<PaidBookings> CheckValidBooking(int ID, string type, DateTime period_start, DateTime period_end)
{
    return database.Query<PaidBookings>("SELECT * FROM PaidBookings WHERE ID = ? AND Type = ? AND checkIn >= ? AND checkOut <= ?;", ID, type, period_start, period_end);
}

I have an error caused by the SQL statement when executed:

public List<PaidBookings> CheckValidBooking(int ID, string type, DateTime period_start, DateTime period_end)
{
    return database.Query<PaidBookings>("SELECT * FROM PaidBookings WHERE ID = ? AND Type = ? AND checkIn >= ? AND checkOut <= ?;", ID, type, period_start, period_end);
}

System.NullReferenceException: Object reference not set to an instance of an object.

How can I resolve this? Thank you.

Does this answer your question? [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Fildor, Nov 18 '19 at 12:38
Have you checked `database` and all the parameters for `null`? — Fildor, Nov 18 '19 at 12:39

Erwin Draconis · Accepted Answer · 2019-11-18T13:27:03.627

-1

you should put your variable between cotes 'your variable'

try this :

database.Query<PaidBookings>(String.Format("SELECT * FROM PaidBookings WHERE ID = '{0}' AND Type = '{1}' AND checkIn >= '{2}' AND checkOut <= '{4}';", ID, type, period_start, period_end));

like @Shawn has mentioned this is a bad practice, normally you should use command paramter like below :

SqlCommand cmd = new SqlCommand();
cmd.CommandText =String.Format("SELECT * FROM PaidBookings WHERE ID = @ID AND Type = @Type AND checkIn >= @checkIn AND checkOut <= @checkOut;");

//  add parameter to command object
cmd.Parameters.Add(new SqlParameter("@ID", yourId));

....

// execut the commend
 cmd.ExecuteNonQuery();

edited Nov 18 '19 at 13:27

answered Nov 18 '19 at 12:48

Erwin Draconis

764
8
20

Er, no. That changes the question marks from parameters that are bound to values to string literals holding a question mark, which is not at all what you want. – Shawn Nov 18 '19 at 12:51
1

No, that's bad too -- binding user-supplied values to parameters is the only way to go. Trying to insert them directly into a query string like that opens you up to sql injection and other errors. – Shawn Nov 18 '19 at 13:19

SQLLite - Select bookings between two dates

1 Answers1