Dlookup function

[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

[Spam Catcher]

Re: Dlookup function

"John" <John@nospam.in fovis.co.ukwrot e in
news:eA4#7FggIH A.320@TK2MSFTNG P02.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

--
spamhoneypot@ro gers.com (Do not e-mail)

[John]

Re: Dlookup function

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

Regards

"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
news:Xns9A5CA0A 802031usenethon eypotrogers@127 .0.0.1...

"John" <John@nospam.in fovis.co.ukwrot e in
news:eA4#7FggIH A.320@TK2MSFTNG P02.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
>
--
spamhoneypot@ro gers.com (Do not e-mail)

[Spam Catcher]

Re: Dlookup function

"John" <John@nospam.in fovis.co.ukwrot e in news:ezmv58hgIH A.5160
@TK2MSFTNGP05.p hx.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.


--
spamhoneypot@ro gers.com (Do not e-mail)

[John]

Re: Dlookup function

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.OleDbComm and
Dim Reader As OleDb.OleDbData Reader
Dim Value As Object = DBNull.Value

DLookup = Nothing
Cmd = New OleDb.OleDbComm and("SELECT " & SearchFld & " FROM " & SearchTbl &
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteRead er()
If Microsoft.Visua lBasic.Left(Sea rchFld, 1) = "[" Then
SearchFld = Microsoft.Visua lBasic.Right(Se archFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If Microsoft.Visua lBasic.Right(Se archFld, 1) = "]" Then
SearchFld = Microsoft.Visua lBasic.Left(Sea rchFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetS tring(Reader.Ge tOrdinal(Search Fld)) Is Nothing, "",
Reader.GetStrin g(Reader.GetOrd inal(SearchFld) ))
End If
DLookup = Value

End Function

"Spam Catcher" <spamhoneypot@r ogers.comwrote in message
news:Xns9A5CA34 92FD15usenethon eypotrogers@127 .0.0.1...

"John" <John@nospam.in fovis.co.ukwrot e in news:ezmv58hgIH A.5160
@TK2MSFTNGP05.p hx.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.
>
>
--
spamhoneypot@ro gers.com (Do not e-mail)

[Spam Catcher]

Re: Dlookup function

"John" <John@nospam.in fovis.co.ukwrot e in
news:#uHeqTigIH A.4684@TK2MSFTN GP06.phx.gbl:

DLookup = Nothing
Cmd = New OleDb.OleDbComm and("SELECT " & SearchFld & " FROM " &
SearchTbl & " WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteRead er()

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.

--
spamhoneypot@ro gers.com (Do not e-mail)

[aaron.kempf@gmail.com]

Re: Dlookup function

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.in fovis.co.ukwrot e:

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.OleDbComm and
Dim Reader As OleDb.OleDbData Reader
Dim Value As Object = DBNull.Value
>
DLookup = Nothing
Cmd = New OleDb.OleDbComm and("SELECT " & SearchFld & " FROM " & SearchTbl &
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteRead er()
If Microsoft.Visua lBasic.Left(Sea rchFld, 1) = "[" Then
SearchFld = Microsoft.Visua lBasic.Right(Se archFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If Microsoft.Visua lBasic.Right(Se archFld, 1) = "]" Then
SearchFld = Microsoft.Visua lBasic.Left(Sea rchFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetS tring(Reader.Ge tOrdinal(Search Fld)) Is Nothing, "",
Reader.GetStrin g(Reader.GetOrd inal(SearchFld) ))
End If
DLookup = Value
>
End Function
>
"Spam Catcher" <spamhoney...@r ogers.comwrote in message
>
news:Xns9A5CA34 92FD15usenethon eypotrogers@127 .0.0.1...
>
>
>

"John" <J...@nospam.in fovis.co.ukwrot e in news:ezmv58hgIH A.5160
@TK2MSFTNGP05.p hx.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...@ro gers.com (Do not e-mail)- Hide quoted text -

>
- Show quoted text -

[John]

Re: Dlookup function

Bug Fix 1: Change the two GetString to GetValue.

<aaron.kempf@gm ail.comwrote in message
news:2af4dd78-d959-4e30-9e59-acb595eff262@i1 2g2000prf.googl egroups.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.in fovis.co.ukwrot e:

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.OleDbComm and
Dim Reader As OleDb.OleDbData Reader
Dim Value As Object = DBNull.Value
>
DLookup = Nothing
Cmd = New OleDb.OleDbComm and("SELECT " & SearchFld & " FROM " & SearchTbl
&
" WHERE " & SearchCriteria, DBConnection())
Reader = Cmd.ExecuteRead er()
If Microsoft.Visua lBasic.Left(Sea rchFld, 1) = "[" Then
SearchFld = Microsoft.Visua lBasic.Right(Se archFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If Microsoft.Visua lBasic.Right(Se archFld, 1) = "]" Then
SearchFld = Microsoft.Visua lBasic.Left(Sea rchFld,
Microsoft.Visua lBasic.Len(Sear chFld) - 1)
End If
If (Reader.Read()) Then
Value = IIf(Reader.GetS tring(Reader.Ge tOrdinal(Search Fld)) Is Nothing, "",
Reader.GetStrin g(Reader.GetOrd inal(SearchFld) ))
End If
DLookup = Value
>
End Function
>
"Spam Catcher" <spamhoney...@r ogers.comwrote in message
>
news:Xns9A5CA34 92FD15usenethon eypotrogers@127 .0.0.1...
>
>
>

"John" <J...@nospam.in fovis.co.ukwrot e in news:ezmv58hgIH A.5160
@TK2MSFTNGP05.p hx.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...@ro gers.com (Do not e-mail)- Hide quoted text -

>
- Show quoted text -