By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,645 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,645 IT Pros & Developers. It's quick & easy.

Dlookup function

P: n/a
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
Mar 9 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"John" <Jo**@nospam.infovis.co.ukwrote in
news:eA*************@TK2MSFTNGP02.phx.gbl:
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?
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

--
sp**********@rogers.com (Do not e-mail)
Mar 9 '08 #2

P: n/a
What is a fast way to do this? Sorry, but too many answers is no answer.

Regards

"Spam Catcher" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
"John" <Jo**@nospam.infovis.co.ukwrote in
news:eA*************@TK2MSFTNGP02.phx.gbl:
>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?

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

--
sp**********@rogers.com (Do not e-mail)

Mar 9 '08 #3

P: n/a
"John" <Jo**@nospam.infovis.co.ukwrote in news:ezmv58hgIHA.5160
@TK2MSFTNGP05.phx.gbl:
What is a fast way to do this? Sorry, but too many answers is no answer.
What's your data source?

If it's a database, use a SQL Query.
--
sp**********@rogers.com (Do not e-mail)
Mar 9 '08 #4

P: n/a
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

"Spam Catcher" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
"John" <Jo**@nospam.infovis.co.ukwrote in news:ezmv58hgIHA.5160
@TK2MSFTNGP05.phx.gbl:
>What is a fast way to do this? Sorry, but too many answers is no answer.

What's your data source?

If it's a database, use a SQL Query.
--
sp**********@rogers.com (Do not e-mail)

Mar 9 '08 #5

P: n/a
"John" <Jo**@nospam.infovis.co.ukwrote in
news:#u**************@TK2MSFTNGP06.phx.gbl:
DLookup = Nothing
Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " &
SearchTbl & " WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteReader()
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.

--
sp**********@rogers.com (Do not e-mail)
Mar 10 '08 #6

P: n/a
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

On Mar 9, 1:34*pm, "John" <J...@nospam.infovis.co.ukwrote:
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

"Spam Catcher" <spamhoney...@rogers.comwrote in message

news:Xn**********************************@127.0.0. 1...
"John" <J...@nospam.infovis.co.ukwrote in news:ezmv58hgIHA.5160
@TK2MSFTNGP05.phx.gbl:
What is a fast way to do this? Sorry, but too many answers is no answer..
What's your data source?
If it's a database, use a SQL Query.
--
spamhoney...@rogers.com (Do not e-mail)- Hide quoted text -

- Show quoted text -
Mar 12 '08 #7

P: n/a
Bug Fix 1: Change the two GetString to GetValue.

<aa*********@gmail.comwrote in message
news:2a**********************************@i12g2000 prf.googlegroups.com...
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

On Mar 9, 1:34 pm, "John" <J...@nospam.infovis.co.ukwrote:
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

"Spam Catcher" <spamhoney...@rogers.comwrote in message

news:Xn**********************************@127.0.0. 1...
"John" <J...@nospam.infovis.co.ukwrote in news:ezmv58hgIHA.5160
@TK2MSFTNGP05.phx.gbl:
What is a fast way to do this? Sorry, but too many answers is no
answer.
What's your data source?
If it's a database, use a SQL Query.
--
spamhoney...@rogers.com (Do not e-mail)- Hide quoted text -

- Show quoted text -

Mar 12 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.