Error when no rows returned from SELECT statement

[Arkaine]

Hello , I don't know why I am catching an error when I do a select statement that returns no rows. I am Using SQL Server (yes i am using oledb but only for testing/learning purposes).


Here is the SQL statement
SELECT * FROM tblLogIn WHERE UserName= '" & UserName & "' AND Password= '" & Password & "'"

Here is the error

System.Data.OleDb.OleDbException: Object or data matching the name, range, or selection criteria was not found within the scope of this operation. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleRow(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at OptimaBackEndTest.LogIn.ExecuteQuery(String SQL, String DB) in C:\Inetpub\wwwroot\OptimaBackEndTest\login.aspx.vb:line 36

If it returns a row then I get no error.

Here is the code for the page

Imports System.Data.OleDb
Public Class LogIn
    Inherits System.Web.UI.Page
    Protected WithEvents lblBadLogIn As System.Web.UI.WebControls.Label
    Protected WithEvents txtUserName As System.Web.UI.HtmlControls.HtmlInputText
    Protected WithEvents txtPassword As System.Web.UI.HtmlControls.HtmlInputText
    Protected WithEvents lblConnectionError As System.Web.UI.WebControls.Label
    Dim Conn As OleDbConnection, Command As OleDbCommand, DataRead As OleDbDataReader
    Protected WithEvents btnLogIn As System.Web.UI.WebControls.Button
 
 
    Public Sub ExecuteQuery(ByVal SQL As String, ByVal DB As String)
        If Application("Connection") Is Nothing Then
            Try
                Conn = New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Get(DB))
                Conn.Open()
                Command = New OleDbCommand(SQL, Conn)
                DataRead = Command.ExecuteReader(CommandBehavior.SingleRow)
            Catch ex As Exception
                Response.Write(ex)
                lblBadLogIn.Text = "Wrong UserName/Password Combonation."
                Exit Sub
            End Try
        Else
            lblConnectionError.Text = "<div align='center'><b>Error:</b> There is already a connection to the database.<br>Please close the connection and try again.</div>"
        End If
        Response.Redirect("index.aspx")
    End Sub
 
    
 
    Private Sub btnLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogIn.Click
        Dim UserName As String, Password As String
        UserName = Request.Form("txtUserName")
        Password = Request.Form("txtPassword")
        ExecuteQuery("SELECT * FROM tblLogIn WHERE UserName= '" & UserName & "' AND Password= '" & Password & "'", "Test")
    End Sub
End Class

What i want to do is check to see how many rows were returned and if the number of rows returned was <=0 then send the message "bad login" or something like that. But i never get a chance to check the rows becasue it just errors out.

I currently am just setting the bad login text when i get the error and this works, but it doesn't seem right because I could get an error for a totaly different reason and wouldn't know it.

I wanted to use If DataRead.FieldCount <= 0 Then etc...

Any idea why i'm getting this error?

Thanks in advance!

[Lunatic3]

For datareader object you use Read method that does two things:
1-Checks if there is a row after current positon.
2-If there is a row then moves to that row.
So you should do:

If  DataRead.Read Then
' Some code Here
End if

[Arkaine]

Ok i figured out the problem... I was using

DataRead = Command.ExecuteReader(CommandBehavior.SingleRow)

and for some reason it was giving an error if no row was returned... but if i use

DataRead = Command.ExecuteReader(CommandBehavior.CloseConnection)

It works just fine.

Thanks for the help though, it got me thinking!