Ado.Net question

Ok I am doing a check to see if a name is in a database or not. Returns true if it is, false if it isn't there..

Is the following code the best practical way to do this or is there a better way and I'm being clueless?

VB Code:

Private Function IsAccount(ByVal strSQL As String) As Boolean
 
        Dim oConn As OleDbConnection = New OleDbConnection(sConn)
        Dim cmd As OleDbCommand = New OleDbCommand(strSQL, oConn)
        Dim r As OleDbDataReader = Nothing
        Dim retValue As Boolean = True
 
        Try
            'Open the connection
            oConn.Open()
 
            'Get the reader
            r = cmd.ExecuteReader
 
            'Check to see if this account name exists
            If (r.Read) Then
                retValue = True
            Else
                retValue = False
            End If
 
        Catch ex As Exception
            retValue = True
        Finally
            If (Not r Is Nothing) Then r.Close()
            oConn.Close()
        End Try
 
        Return retValue
 
    End Function

Any insight, critisizm, or general "You should'nt do it that way" or "Here's a better way" comments are all welcomed...

Where is the SQL statement? And by 'in the database' do you just mean in a specific table?

I would have the SQL query return a count of the number of times the name is found and then execute the command scalar so just one value has to come across. That should improve performance and network traffic.

Quote:

---

Originally posted by Edneeis
Where is the SQL statement? And by 'in the database' do you just mean in a specific table?

I would have the SQL query return a count of the number of times the name is found and then execute the command scalar so just one value has to come across. That should improve performance and network traffic.

---

Hey Edneeis, the SQL statement is passed through the function itself from another method. Yes it is checking one table in the Access database, it would look like this..

VB Code:

"SELECT AcctName FROM Accounts WHERE AcctName = 'Edneeis'"

You're saying to use a scalar() call and just return the count, mind elaborating on that a bit? I know what the scalar() call does but how would you query just a count or return just a count ?

Use this as the sql statement:
SELECT Count(AcctName) FROM Accounts WHERE AcctName = 'Edneeis'

VB Code:

Private Function IsAccount(ByVal strSQL As String) As Boolean
        'where does sConn come from?
        Dim oConn As New OleDb.OleDbConnection(sConn)
        Dim cmd As New OleDb.OleDbCommand(strSQL, oConn)
        Dim cnt As Integer=0
 
        Try
            'Open the connection
            oConn.Open()
            'Get the count
            cnt = CType(cmd.ExecuteScalar, Integer)
        Catch ex As Exception
        Finally
            oConn.Close()
        End Try
 
        'return boolean based on count
        Return (cnt > 0)
    End Function

Thanks again Edneeis, making my life easier one line at a time ;)