Dlookup function - Visual Basic .NET

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

Mar 9 '08 #1

Subscribe Post Reply

10729

Spam Catcher

"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

John

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

Spam Catcher

"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

John

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

Spam Catcher

"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

aaron.kempf

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

John

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

Similar topics

DLookUp of multiple records in a query

by: KLAU | last post by:

I have a field that retrieves information from an expression in a query. I have used a DLookup function to get the calculated field from the query. However, the relationship is 1-to-many so one...

Microsoft Access / VBA

DLookup problem

by: David B | last post by:

I have a Public function which creates a back up copy of a back end. It works fine with the source path and source file as C:\folder\etc. I thought I would try to store the paths in a table so...

Microsoft Access / VBA

Dlookup function gives "You canceled the previous operation" errmsg

by: Ronny Sigo | last post by:

Hello all, I already put the same question, only now I have more to tell ... Although I used this code before in the same routine (only the fieldname of the table differs) ___ at this point in the...

Microsoft Access / VBA

My Dlookup is 'canceled'

by: Thelma Lubkin | last post by:

I am still struggling with trying to match a user supplied string with strings in a field of a table, where spaces are to be ignored. Arno suggested that I use the Dlookup function, but I...

Microsoft Access / VBA

Help with DLookup

by: Don Sealer | last post by:

I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as...

Microsoft Access / VBA

Dlookup

by: nzyui | last post by:

Trying to lookup a field from a table called condition this is a separate table not linked to anything: setup as shown Code S M L DG 1 2 3 RI 2 4 5 need...

Microsoft Access / VBA

Help with DLookup/ I'm stumped!

by: Don | last post by:

Can someone help me fix my DLookup problem. I'm far from proficiency with Access. I've been creating databases for several years for work with the help of many of you and trial and error. I have...

Microsoft Access / VBA

Using DLOOKUP Function to fill out Multiple Text Boxes ... Is this even possible

by: jonvan20 | last post by:

I have been having trouble with a simple Dlookup command that was Reccommended to me by a nice fellow named Vic, On the other hand I have statements like this that wont run they give me a run time...

Microsoft Access / VBA

Criteria works for FindFirst but not for Dlookup when text containsdouble quotes

by: Denise | last post by:

Front end is Access 2002, back end is linked Oracle tables. My users need to describe things in feet and inches and want to use the standard ' and " abbrevations. On a testing form I go to a...

Microsoft Access / VBA

DLookup Question

by: Breezwell | last post by:

This is probably a simple question for someone out there. I understand that the DLookup function takes has the following syntax: DLookup(expression,domain,) From what I have read, domain can...

Microsoft Access / VBA

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA