Checking for an SQL result in VB.NET

Question

I need to check if my SQL statement returned any results from the Access db I'm using to store my data.

I have this code atm:

        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\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
            While dr.Read()

            End While
        End If
        dr.Close()

I would like to be able to check (maybe via a boolean value or something) that this result returned a query. I'm using VB.net.

Thanks

Answer 1

The simple answer, I guess, is this:

    Dim hasResults as Boolean = false
    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\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
        While dr.Read()
             hasResults = true
        End While
    End If
    dr.Close()

My vb-syntax is a little rusty, but you get the idea.
But I'm not sure that this is what you actually want.

Another variant could be

Dim hasResults as Boolean = false
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\database.mdb;")
cn.Open()
cmd = New OleDbCommand("SELECT 1 FROM(" & roomvar.ToLower() & ") WHERE (((" & roomvar.ToLower() & ".date)=" & Chr(34) & dtpDate.Value.Date & Chr(34) & "))", cn)
dr = cmd.ExecuteReader
    If dr.Read() Then
         hasResults = true
    End If
End If
dr.Close()

Answer 2

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\database.mdb;")
    cn.Open()
    Using cmd As New OleDbCommand(String.Format("SELECT * FROM({0}) WHERE ((({0}.date)=""{1}""))", roomvar.ToLower(), dtpDate.Value.Date), cn)
        Using dr As OleDbDataReader = cmd.ExecuteReader()
            hasResults = dr.Read()
        End Using
    End Using
End Using

I strongly suggest you look at passing the dtpDate value as a parameter instead of a literal value in the command text, e.g.:

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\database.mdb;")
    cn.Open()
    Using cmd As New OleDbCommand(String.Format("SELECT * FROM({0}) WHERE ((({0}.date)=?))", roomvar.ToLower()), cn)
        cmd.Parameters.Add("@dtpDate", OleDbType.Date).Value = dtpDate.Value.Date
        Using dr As OleDbDataReader = cmd.ExecuteReader()
            hasResults = dr.Read()
        End Using
    End Using
End Using

Answer 3

Simple. Use the OleDbDataReader.HasRows Property after your call to ExecuteReader.

    cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Computing Project\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

    hasRows = dr.HasRows

    dr.Close()