Dlookup function

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

Is there a dlookup function in vb.net like in access which returns a column
value from a table for a given criteria? If not, is there a sample I can
look at?

Thanks

Regards
 
Is there a dlookup function in vb.net like in access which returns a
column value from a table for a given criteria? If not, is there a
sample I can look at?
Click to expand...

SELECT MyColumn FROM MyTable WHERE MyColumn = SOMEVALUE ;-)

..NET has several ways to achieve what you want depending on how you're
storing your data.

LINQ
ADO.NET database query
..Find Delegate (supported on some collections)
Dataset/DataView filters
 
Will below do?

Thanks

Regards

Function DLookup(ByVal SearchFld As String, ByVal SearchTbl As String, ByVal
SearchCriteria As String) As Object
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim Value As Object = DBNull.Value

DLookup = Nothing
Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " & SearchTbl &
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteReader()
If Microsoft.VisualBasic.Left(SearchFld, 1) = "[" Then
SearchFld = Microsoft.VisualBasic.Right(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If Microsoft.VisualBasic.Right(SearchFld, 1) = "]" Then
SearchFld = Microsoft.VisualBasic.Left(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetString(Reader.GetOrdinal(SearchFld)) Is Nothing, "",
Reader.GetString(Reader.GetOrdinal(SearchFld)))
End If
DLookup = Value

End Function
 
DLookup = Nothing
Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " &
SearchTbl & " WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteReader()
Click to expand...

Yes, something like that will work.

Also look at ExecuteScalar command, it allows you to return one value.

You should also avoid doing string concatenation in your code because it
is ripe for SQL injection attacks. Instead you should use SQL Parameters
instead.
 
yah now we just need these for the other dozen domain aggregate
functions; thanks

seems to me like it should have shipped as built in functions

-aaron

Will below do?

Thanks

Regards

Function DLookup(ByVal SearchFld As String, ByVal SearchTbl As String, ByVal
SearchCriteria As String) As Object
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim Value As Object = DBNull.Value

DLookup = Nothing
Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " & SearchTbl &
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteReader()
If Microsoft.VisualBasic.Left(SearchFld, 1) = "[" Then
SearchFld = Microsoft.VisualBasic.Right(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If Microsoft.VisualBasic.Right(SearchFld, 1) = "]" Then
SearchFld = Microsoft.VisualBasic.Left(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetString(Reader.GetOrdinal(SearchFld)) Is Nothing, "",
Reader.GetString(Reader.GetOrdinal(SearchFld)))
End If
DLookup = Value

End Function




@TK2MSFTNGP05.phx.gbl:
Click to expand...
What's your data source?
Click to expand...
If it's a database, use a SQL Query.
Click to expand...

- Show quoted text -
Click to expand...
 
Bug Fix 1: Change the two GetString to GetValue.

yah now we just need these for the other dozen domain aggregate
functions; thanks

seems to me like it should have shipped as built in functions

-aaron

Will below do?

Thanks

Regards

Function DLookup(ByVal SearchFld As String, ByVal SearchTbl As String,
ByVal
SearchCriteria As String) As Object
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim Value As Object = DBNull.Value

DLookup = Nothing
Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " & SearchTbl
&
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteReader()
If Microsoft.VisualBasic.Left(SearchFld, 1) = "[" Then
SearchFld = Microsoft.VisualBasic.Right(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If Microsoft.VisualBasic.Right(SearchFld, 1) = "]" Then
SearchFld = Microsoft.VisualBasic.Left(SearchFld,
Microsoft.VisualBasic.Len(SearchFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetString(Reader.GetOrdinal(SearchFld)) Is Nothing, "",
Reader.GetString(Reader.GetOrdinal(SearchFld)))
End If
DLookup = Value

End Function




@TK2MSFTNGP05.phx.gbl:
Click to expand...
What's your data source?
Click to expand...
If it's a database, use a SQL Query.
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top