Get a datarow with a query OLEDB - VB.net

Richard Berry100

Is there a better way to get one row from a database. I have been using the Fill Method of the DataAdapter to fill a datatable, and then returning the first row of the table

Dim dr As DataRow
Using cn As New OleDb.OleDbConnection(cnStr)
Dim cmd As New OleDb.OleDbCommand(strSelect, cn)
Dim da As New OleDb.OleDbDataAdapter(cmd)

        Try
            cn.Open()
            da.Fill(dt)
            If dt.Rows.Count >= 1 Then
                dr = dt.Rows(0)
            End If
        Catch ex As Exception
            'Err handling
        Finally
            cn.Close()
            dt = Nothing
        End Try
    End Using
    Return dr

PIEBALDconsult

There sure is. For one thing, if you're using SQL Server you can use SELECT TOP 1 ... If your database doesn't support that, you can use a DataReader and read only one row (but it won't be a DataRow).

Richard Berry100

Thanks PIEBALDconsult The SELECT query should return only one row based on conditions in the WHERE clause, but I was wondering if there was a way to eliminate declaring a DataTable, and then equating the DataRow to the first row from the DataTable?

PIEBALDconsult

Richard.Berry100 wrote:

only one row based on conditions in the WHERE clause

Oh, that's alright then. You probably needn't be too concerned in that case -- if it solves the problem so you can move on and then assess performance and such at a later date. Other than that, I use a DataReader; I avoid DataAdapters except when I'm very lazy -- and then it always bites me back.

Lost User

Richard.Berry100 wrote:

but I was wondering if there was a way to eliminate declaring a DataTable, and then equating the DataRow to the first row from the DataTable?

Why? You can simply load the reader in your datatable and return the first row from a method. That way the table is out of scope and discarded. If you really think that's consuming too much memory, then you should not use a DataRow, but an array of values[^]. Take a look at the section[^] that says "Working with DataReaders".

Bastard Programmer from Hell :suss: