How Do I Get Data from OLE DB Query

Question

Allow me to prove how new I am to Visual Basic and OLE DB coding. My intention is to simply retrieve the value of one field from a query that I'm running. The code below works but it seems ugly to me.I am using a DataAdapter and DataSet to fill a DataGridView, because that's the only way to get the information. The SQL retrieves one row so I know that the field I want will always be in Row 0, Cell 0 and I can move that into my variable strArchID. I'm guessing there might be a better way to do this (DataReader) but I can't figure out how to use it. I'd appreciate any suggestions including sample code.

    Try
        Dim mySQLArchID = "SELECT ArchID, ArchUserName, ArchUserDomain, ArchDate, ArchRoot FROM Archives WHERE " &
        "ArchUserName = @UserName AND " &
        "ArchUserDomain = @UserDomain AND " &
        "ArchDate = @MyArchDate AND " &
        "ArchRoot = @MyArchRoot"
        Using myConn As New OleDbConnection(strConnectionString)
            Using myGetID As New OleDbCommand(mySQLArchID, myConn)
                Dim NameParm = New OleDbParameter("UserName", OleDbType.VarChar)
                Dim DomainParm = New OleDbParameter("UserDomain", OleDbType.VarChar)
                Dim DateParm = New OleDbParameter("MyArchDate", OleDbType.Date)
                Dim RootParm = New OleDbParameter("MyArchRoot", OleDbType.VarChar)
                Dim myAdapter As New OleDbDataAdapter
                NameParm.Value = strArchUser
                DomainParm.Value = strArchDomain
                DateParm.Value = dteArchDate
                RootParm.Value = strArchRoot
                myGetID.Parameters.Add(NameParm)
                myGetID.Parameters.Add(DomainParm)
                myGetID.Parameters.Add(DateParm)
                myGetID.Parameters.Add(RootParm)
                myAdapter.SelectCommand = myGetID
                Dim ds As New DataSet
                ds.Clear()
                myAdapter.Fill(ds, "Archives")
                dgvData.DataSource = ds
                dgvData.DataMember = "Archives"
                strArchID = dgvData.Rows(0).Cells(0).Value.ToString
            End Using
        End Using

    Catch ex As Exception
        MessageBox.Show("Get Arch ID did not work")
    End Try

Answer 1

If you want a single value from a database then you create a command object and call ExecuteScalar. Even the parts of your code that you would keep can be simplified a lot.

Dim sql = "SELECT ArchID
           FROM Archives
           WHERE ArchUserName = @ArchUserName
           AND ArchUserDomain = @ArchUserDomain
           AND ArchDate = @ArchDate
           AND ArchRoot = @ArchRoot"

Using connection As New OleDbConnection(strConnectionString),
      command As New OleDbCommand(sql, connection)
    With command.Parameters
        .Add("@ArchUserName", OleDbType.VarChar, 50).Value = strArchUser
        .Add("@ArchUserDomain", OleDbType.VarChar, 50).Value = strArchDomain
        .Add("@ArchDate", OleDbType.Date).Value = dteArchDate
        .Add("@ArchRoot", OleDbType.VarChar, 50).Value = strArchRoot
    End With

    connection.Open()
    strArchID = CStr(command.ExecuteScalar())
End Using

1. Recent VB versions have supported multiline String literals and they help make SQL code far more readable. Even before that though, you could still use an XML literal.
2. Don't include columns in your query that you don't actually need. Use the column name for corresponding parameters unless you have a specific reason for not doing so.
3. You don't need to nest Using blocks unless you need to interleave code between them. A single Using statement can create as many objects as you like.
4. Don't use the same complex expression multiple times. Either use a With block or assign the expression to a variable and then use the multiple times.
5. Don't use multistep code to create and add parameters unless the steps are in different places. Otherwise, call Add and it will return the new parameter object that you can then set the Value property of. Also specify the size for variable-size data types. I have used 50 as an example but you should use your own size from the database.
6. ExecuteScalar can be used to execute any query but will only return the first column of the first row of the result set. If there are no rows, DBNull.Value is returned. The value is returned as an Object reference, so you will need to cast it as the actual type of the data. That cast will not be valid if the result is NULL, so be aware of whether you need to allow for that.

For more ADO.NET examples, see here.