Equivalent of dlookup

[John]

Hi

Is there an equivalent in vb.net for access's dlookup function? I am trying
to get a value from a table based on a condition without going through all
the dataset/dataadapter set-up.

I need something like this; return client.company where client.id = 10.

Thanks

Regards

 

[Val Mazur]

Hi John,

Use Select method of DataTable. It will return array of DataRows, matching
your criteria

 

[Miha Markic]

Hi John,

In addition to Val's solution.
You can use also DataTable.Rows.Find method if you are querying on primary
key.

 

[John]

Thanks. Could you give me a small code example how to start? Considering
that this table is not yet referenced in the project in any way.

Thanks

Regards

 

[Miha Markic]

Hi John,

Assuming you have DataTable instance myTable with primary key of one integer
column, you might do:
[C#]
DataRow row = myTable.Rows.Find(2); // where 2 is value of primary key you
are searching
[VB.NET]
Dim row As DataRow = myTable.Rows.Find(2)

If you have pk with more columns, you should pass new object[col1value,
col2value, ..] instead of 2.

 

[John]

Silly point, how does datatable relate to the table in the database on the
disk? Do I drag the table onto a dataset from the server explorer?

Thanks

Regards

Hi John,

Assuming you have DataTable instance myTable with primary key of one integer
column, you might do:
[C#]
DataRow row = myTable.Rows.Find(2); // where 2 is value of primary key you
are searching
[VB.NET]
Dim row As DataRow = myTable.Rows.Find(2)

If you have pk with more columns, you should pass new object[col1value,
col2value, ..] instead of 2.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thanks. Could you give me a small code example how to start? Considering
that this table is not yet referenced in the project in any way.

Thanks

Regards


through
all

 

[Val Mazur]

Hi,

DataTable could be a result of the selection from the table or joined
tables. There is not one-to-one relation between DataTable and table in a
database, but it could be in case if you call some sort of SELECT * FROM
MyTable SQL statement. If you do this, then you will have DataTable, which
will contain exactly same data as your table. But in case if your table is a
big one, it could take some time

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

Silly point, how does datatable relate to the table in the database on the
disk? Do I drag the table onto a dataset from the server explorer?

Thanks

Regards

Hi John,

Assuming you have DataTable instance myTable with primary key of one integer
column, you might do:
[C#]
DataRow row = myTable.Rows.Find(2); // where 2 is value of primary key
you
are searching
[VB.NET]
Dim row As DataRow = myTable.Rows.Find(2)

If you have pk with more columns, you should pass new object[col1value,
col2value, ..] instead of 2.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thanks. Could you give me a small code example how to start?
Considering
that this table is not yet referenced in the project in any way.

Thanks

Regards


"Miha Markic" <miha at rthand com> wrote in message
Hi John,

In addition to Val's solution.
You can use also DataTable.Rows.Find method if you are querying on primary
key.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Hi

Is there an equivalent in vb.net for access's dlookup function? I
am
trying
to get a value from a table based on a condition without going through
all
the dataset/dataadapter set-up.

I need something like this; return client.company where client.id
= 10.

Thanks

Regards

 

[John]

Hi

I have come up with the following;

Dim dtClients As DataTable
Dim i As Long

i = 10
dtClients = ds.tblClients
dtClients.Rows.Find(i)
MsgBox(dtClients.Columns("Company").ToString)

My questions are;

1. How do I know if find has succeeded?

2. Once found how do I get the value of the column "Company"?

3. If table tblclients is used on a separate form, would using the datatable
dtClients effect the binding context of tblclients on the other form?

Thanks

Regards

Hi,

DataTable could be a result of the selection from the table or joined
tables. There is not one-to-one relation between DataTable and table in a
database, but it could be in case if you call some sort of SELECT * FROM
MyTable SQL statement. If you do this, then you will have DataTable, which
will contain exactly same data as your table. But in case if your table is a
big one, it could take some time

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

Silly point, how does datatable relate to the table in the database on the
disk? Do I drag the table onto a dataset from the server explorer?

Thanks

Regards

Hi John,

Assuming you have DataTable instance myTable with primary key of one integer
column, you might do:
[C#]
DataRow row = myTable.Rows.Find(2); // where 2 is value of primary key
you
are searching
[VB.NET]
Dim row As DataRow = myTable.Rows.Find(2)

If you have pk with more columns, you should pass new object[col1value,
col2value, ..] instead of 2.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thanks. Could you give me a small code example how to start?
Considering
that this table is not yet referenced in the project in any way.

Thanks

Regards


"Miha Markic" <miha at rthand com> wrote in message
Hi John,

In addition to Val's solution.
You can use also DataTable.Rows.Find method if you are querying on
primary
key.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Hi

Is there an equivalent in vb.net for access's dlookup function? I
am
trying
to get a value from a table based on a condition without going through
all
the dataset/dataadapter set-up.

I need something like this; return client.company where client.id
=
10.

Thanks

Regards

 

[Jay B. Harlow [MVP - Outlook]]

John,
In addition to the other comments, have you looked at using the
IDbCommand.ExecuteScalar method?

The IDbCommand.ExecuteScalar is implemented by all the ADO.NET Command
objects (Sql, OleDb, ODBC, Oracle).

For details see:
http://msdn.microsoft.com/library/d...tml/cpconobtainingsinglevaluefromdatabase.asp

I need something like this; return client.company where client.id = 10.

With ExecuteScalar you need to use a valid Select statement that returns a
single column in a single row.

select company from client where id = 10

Hope this helps
Jay

 

[Miha Markic]

Hi John,

Find returns a DataRow instance - it succeeded it the return value it not
null.
Once that you have nonnull instance you get the value as normal.
dr("Company")...
Find doesn't affect binding.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Hi

I have come up with the following;

Dim dtClients As DataTable
Dim i As Long

i = 10
dtClients = ds.tblClients
dtClients.Rows.Find(i)
MsgBox(dtClients.Columns("Company").ToString)

My questions are;

1. How do I know if find has succeeded?

2. Once found how do I get the value of the column "Company"?

3. If table tblclients is used on a separate form, would using the datatable
dtClients effect the binding context of tblclients on the other form?

Thanks

Regards

Hi,

DataTable could be a result of the selection from the table or joined
tables. There is not one-to-one relation between DataTable and table in a
database, but it could be in case if you call some sort of SELECT * FROM
MyTable SQL statement. If you do this, then you will have DataTable, which
will contain exactly same data as your table. But in case if your table

is

a
big one, it could take some time

--
Val Mazur
Microsoft MVP
Check Virus Alert, stay updated
http://www.microsoft.com/security/incident/blast.asp

Silly point, how does datatable relate to the table in the database on the
disk? Do I drag the table onto a dataset from the server explorer?

Thanks

Regards


"Miha Markic" <miha at rthand com> wrote in message
Hi John,

Assuming you have DataTable instance myTable with primary key of one
integer
column, you might do:
[C#]
DataRow row = myTable.Rows.Find(2); // where 2 is value of primary key
you
are searching
[VB.NET]
Dim row As DataRow = myTable.Rows.Find(2)

If you have pk with more columns, you should pass new object[col1value,
col2value, ..] instead of 2.
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Thanks. Could you give me a small code example how to start?
Considering
that this table is not yet referenced in the project in any way.

Thanks

Regards


"Miha Markic" <miha at rthand com> wrote in message
Hi John,

In addition to Val's solution.
You can use also DataTable.Rows.Find method if you are querying on
primary
key.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rhand.com

Hi

Is there an equivalent in vb.net for access's dlookup function? I
am
trying
to get a value from a table based on a condition without going
through
all
the dataset/dataadapter set-up.

I need something like this; return client.company where client.id
=
10.

Thanks

Regards