• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Is this the best way to simulate a Dlookup function?

hi,

I created my own Dlookup routine and I wanted to know if it was a waste of time.

Here is the code.  Is there a better more effiecnt way of doing this?

    Public Function Dlookup(ByVal Field As String, ByVal Table As String, ByVal Condition As String, ByVal  connString as String) As String

        strSQL = "SELECT " & Field & " FROM " & Table & " WHERE " & Condition
        Dim daDLU As New OleDb.OleDbDataAdapter(strSQL, connString)
        Dim dsDLU As New DataSet

        daDLU.Fill(dsDLU)
        Try
            Return dsDLU.Tables(0).Rows(0).Item(0).ToString
        Catch ex As Exception
            Return ""
        End Try
        daDLU.Dispose()
    End Function
0
NevSoFly
Asked:
NevSoFly
  • 3
  • 2
1 Solution
 
Bob LearnedCommented:
What version of .NET do you have?

Bob
0
 
Bob LearnedCommented:
Here is a 2005 version:

Imports System.Data.OleDb

...

  Public Function Dlookup(ByVal Field As String, ByVal Table As String, ByVal Condition As String, ByVal connString As String) As String

    Using connection As New OleDbConnection(connString)
      connection.open()

      Dim commandText As String = String.Format("SELECT {0} FROM {1} WHERE {2}", Field, Table, Condition)

      Using command As New OleDbCommand(commandText, connection)
        Return command.ExecuteScalar()
      End Using

    End Using
  End Function

Bob
0
 
NevSoFlyAuthor Commented:
I have VB2005
0
 
Bob LearnedCommented:
VB.NET 2005 has Using blocks which are very handy, since they automatically handle calling the Dispose method for anything that implements the IDisposable interface.

The String.Format takes the values in the list, and stuffs them in the string at the marker positions {0, 1, 2, ...n}.

If your query returns a single value, then ExecuteScalar is the optimized way to get that value.

Bob
0
 
NevSoFlyAuthor Commented:
Thank you very much.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now