Checking for result from MS Access database (VB.NET)

Question

In my .NET application I have to manage bookings for rooms. I enter the room booking via an SQL query to a MS Access database using an INSERT INTO statement.

Before I insert the booking, I need to check if a booking already exists for that time period, and if it does stop the user from being able to book at that time.

I have written code to retrieve a database result on the day and time of the booking they want to book, and the fact that Access would return data would mean that the user is trying to book over someone else.

But I'm stuck on how I can check if I have a resuklt returned. My code:

    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Database\database.mdb;")
    cn.Open()
    cmd = New OleDbCommand("SELECT * FROM(" & roomvar.ToLower() & ") WHERE (((" & roomvar.ToLower() & ".date)=" & Chr(34) & dtpDate.Value.Date & Chr(34) & "))", cn)
    dr = cmd.ExecuteReader
    If dr.HasRows = True Then
        MsgBox("There is an existing booking")
    End If
    dr.Close()

But this doesn't work, it displays the messagebox regardless of if there is a row returned or not. How can I check if a row was returned?

Thanks.

Answer 1

cmd = New OleDbCommand("SELECT Count(*) FROM(" & roomvar.ToLower() & ") WHERE (((" & roomvar.ToLower() & ".date)=" & Chr(34) & dtpDate.Value.Date & Chr(34) & "))", cn)
dim alreadyBooked as Integer = cmd.ExecuteScalar

If alreadyBooked > 0 Then
   Msgbox "There is an existing booking"
End If

EDIT: vb.net is not the language I use. You might have to apply cast on cmd.ExecuteScalar.
Also, I would suggest the use of Using statement.

link: http://www.pluralsight.com/community/blogs/fritz/archive/2005/04/28/7834.aspx