Record Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have searched Google with every possible combination of key words I can
imagine and have not been able to find an answer to what I think is a very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to spell it out
for me.

I am trying to write a very simple program in VB.Net 2005. that will search
a single Access database table and return the value of one field based on
criteria that I provide for any other field I choose. The only way I have
had any success is if I search by the primary key field, which does not give
me the versatility that I need.

Can anyone point me to some sample code that will help me accomplish this?

Thanks in advance.

Mike
 
I have searched Google with every possible combination of key words I can
imagine and have not been able to find an answer to what I think is a very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to spell it out
for me.

I am trying to write a very simple program in VB.Net 2005. that will search
a single Access database table and return the value of one field based on
criteria that I provide for any other field I choose. The only way I have
had any success is if I search by the primary key field, which does not give
me the versatility that I need.

Can anyone point me to some sample code that will help me accomplish this?

Thanks in advance.

Mike

You should connect with the OleDb database providers and then do the
search with a simple Sql query.

If you post a bit of the target table's schema (column names mainly)
and tell me which fields you need to search I'll be more than happy to
write up some simple code for you.

Thanks,

Seth Rowe
 
The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord = ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by "ITNBR". I
have
tried changing the primary field and "DisplayMember" to "ITNBR". This will
change the field in my combo box but I get an error when I do the lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike
 
From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the DataRowCollection
object belongs must have at least one column designated as a primary key
column. See the PrimaryKey property for more information about how to create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows using a
more general criteria.

A a side note (and I know this is not your code) IMO it would better to
check explicitely if the criteria returns nothing. The code doesn't work if
a row is not found. It raises an error and then display a message saying
that the row is not found. Actually it will display the message regardless
of the real reason for which the code fails (for example you could test if
the found row is nothing so that an error is raised only if you have a real
error)...
 
Patrice, thank you for your comments and effort. Unfortunately, that doesn't
tell me what I need to know. As I originally mentioned, I am a novice and
any help would have to be in the form of very precise syntax or at least a
very good example. I also mentioned that I tried setting changing the
primary key in the table to the field that I wanted to use for the search
criteria and that didn't work.

Any additional information you could add would be greatly appreciated.

Thanks,

Mike

Patrice said:
From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the DataRowCollection
object belongs must have at least one column designated as a primary key
column. See the PrimaryKey property for more information about how to create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows using a
more general criteria.

A a side note (and I know this is not your code) IMO it would better to
check explicitely if the criteria returns nothing. The code doesn't work if
a row is not found. It raises an error and then display a message saying
that the row is not found. Actually it will display the message regardless
of the real reason for which the code fails (for example you could test if
the found row is nothing so that an error is raised only if you have a real
error)...

---
Patrice

MikeS said:
The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord =
ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by "ITNBR".
I
have
tried changing the primary field and "DisplayMember" to "ITNBR". This
will
change the field in my combo box but I get an error when I do the lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike
 
MikeS,

The problem is that a novice can ask more difficult questions than somebody
who knows what he wants.

You have at least two options:
find what you want in direct in the database
find what you want in a datatable.

For both you got an answer. The first is very well with very hug databases.
The latter is better if it is about smal databases.

Cor

MikeS said:
Patrice, thank you for your comments and effort. Unfortunately, that
doesn't
tell me what I need to know. As I originally mentioned, I am a novice and
any help would have to be in the form of very precise syntax or at least a
very good example. I also mentioned that I tried setting changing the
primary key in the table to the field that I wanted to use for the search
criteria and that didn't work.

Any additional information you could add would be greatly appreciated.

Thanks,

Mike

Patrice said:
From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the
DataRowCollection
object belongs must have at least one column designated as a primary key
column. See the PrimaryKey property for more information about how to
create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows using
a
more general criteria.

A a side note (and I know this is not your code) IMO it would better to
check explicitely if the criteria returns nothing. The code doesn't work
if
a row is not found. It raises an error and then display a message saying
that the row is not found. Actually it will display the message
regardless
of the real reason for which the code fails (for example you could test
if
the found row is nothing so that an error is raised only if you have a
real
error)...

---
Patrice

"MikeS" <[email protected]> a écrit dans le message de
(e-mail address removed)...
The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord =
ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by
"ITNBR".
I
have
tried changing the primary field and "DisplayMember" to "ITNBR". This
will
change the field in my combo box but I get an error when I do the
lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike


:

I have searched Google with every possible combination of key words
I
can
imagine and have not been able to find an answer to what I think is
a
very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to spell
it
out
for me.

I am trying to write a very simple program in VB.Net 2005. that will
search
a single Access database table and return the value of one field
based
on
criteria that I provide for any other field I choose. The only way
I
have
had any success is if I search by the primary key field, which does
not
give
me the versatility that I need.

Can anyone point me to some sample code that will help me accomplish
this?

Thanks in advance.

Mike

You should connect with the OleDb database providers and then do the
search with a simple Sql query.

If you post a bit of the target table's schema (column names mainly)
and tell me which fields you need to search I'll be more than happy to
write up some simple code for you.

Thanks,

Seth Rowe
 
For example a self contained sample :


Dim dt As New DataTable
dt.Columns.Add("Field1", GetType(String))
dt.Columns.Add("Field2", GetType(String))
dt.Columns.Add("Label", GetType(String))
dt.Rows.Add(New Object() {"1", "A", "This is row 1A."})
dt.Rows.Add(New Object() {"2", "B", "This is row 2B."})

Dim SelectedRows() As DataRow
SelectedRows = dt.Select("Field1=1")
MsgBox(SelectedRows(0)("Label"))
SelectedRows = dt.Select("Field2='B'")
MsgBox(SelectedRows(0)("Label"))
SelectedRows = dt.Select("Field2='Z'")
MsgBox(SelectedRows.Length)

Note that :

- The select method returns all rows matching the criteria. In your case you
could make sure that you get only a single row back (or what if you use
another criteria that returns more rows ?)

- Note that the length is 0 in the 3rd case and you can check that in case
your criteria would return no records.

I suggest the online documentation :

- http://msdn2.microsoft.com/en-us/library/63bf39c2.aspx (all technologies
in particular ADO.NET) for broad overviews/introductory material

- for example http://msdn2.microsoft.com/en-us/library/63bf39c2.aspx for
particular references and you have often code samples

You can use the object browser to quickly browse classes or methods for a
particular class befor e checking the doc for the method that looks the one
you are looking for...

--

Patrice



MikeS said:
Patrice, thank you for your comments and effort. Unfortunately, that
doesn't
tell me what I need to know. As I originally mentioned, I am a novice and
any help would have to be in the form of very precise syntax or at least a
very good example. I also mentioned that I tried setting changing the
primary key in the table to the field that I wanted to use for the search
criteria and that didn't work.

Any additional information you could add would be greatly appreciated.

Thanks,

Mike

Patrice said:
From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the
DataRowCollection
object belongs must have at least one column designated as a primary key
column. See the PrimaryKey property for more information about how to
create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows using
a
more general criteria.

A a side note (and I know this is not your code) IMO it would better to
check explicitely if the criteria returns nothing. The code doesn't work
if
a row is not found. It raises an error and then display a message saying
that the row is not found. Actually it will display the message
regardless
of the real reason for which the code fails (for example you could test
if
the found row is nothing so that an error is raised only if you have a
real
error)...

---
Patrice

"MikeS" <[email protected]> a écrit dans le message de
(e-mail address removed)...
The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord =
ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by
"ITNBR".
I
have
tried changing the primary field and "DisplayMember" to "ITNBR". This
will
change the field in my combo box but I get an error when I do the
lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike


:

I have searched Google with every possible combination of key words
I
can
imagine and have not been able to find an answer to what I think is
a
very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to spell
it
out
for me.

I am trying to write a very simple program in VB.Net 2005. that will
search
a single Access database table and return the value of one field
based
on
criteria that I provide for any other field I choose. The only way
I
have
had any success is if I search by the primary key field, which does
not
give
me the versatility that I need.

Can anyone point me to some sample code that will help me accomplish
this?

Thanks in advance.

Mike

You should connect with the OleDb database providers and then do the
search with a simple Sql query.

If you post a bit of the target table's schema (column names mainly)
and tell me which fields you need to search I'll be more than happy to
write up some simple code for you.

Thanks,

Seth Rowe
 
Cor....thank you for your vote of confidence. Maybe this sounded more
difficult than it actually is. To put it in simple terms, all I want to do
is seach a table for ONE unique record and read only ONE field from it. I
don't want to edit it....I don't want to modify it.....I don't want to delete
it.....all I want to do is read ONE field from it. And I don't want to
search by the primary key field. I want to be able to search by Employee
Number or First Name or Last Name or any criteria field I choose and return
his or her "Shoe Size".

In VB for Apps I can do this by using a simple statement such as:

MyValue = DLookup("[RequestedField]", "tablename", "[CriteriaField] = " &
textbox)

I just wanted to do the same thing in VB.Net. I apologize for making it so
difficult.

Thanks,

Mike


Cor Ligthert said:
MikeS,

The problem is that a novice can ask more difficult questions than somebody
who knows what he wants.

You have at least two options:
find what you want in direct in the database
find what you want in a datatable.

For both you got an answer. The first is very well with very hug databases.
The latter is better if it is about smal databases.

Cor

MikeS said:
Patrice, thank you for your comments and effort. Unfortunately, that
doesn't
tell me what I need to know. As I originally mentioned, I am a novice and
any help would have to be in the form of very precise syntax or at least a
very good example. I also mentioned that I tried setting changing the
primary key in the table to the field that I wanted to use for the search
criteria and that didn't work.

Any additional information you could add would be greatly appreciated.

Thanks,

Mike

Patrice said:
From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the
DataRowCollection
object belongs must have at least one column designated as a primary key
column. See the PrimaryKey property for more information about how to
create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows using
a
more general criteria.

A a side note (and I know this is not your code) IMO it would better to
check explicitely if the criteria returns nothing. The code doesn't work
if
a row is not found. It raises an error and then display a message saying
that the row is not found. Actually it will display the message
regardless
of the real reason for which the code fails (for example you could test
if
the found row is nothing so that an error is raised only if you have a
real
error)...

---
Patrice

"MikeS" <[email protected]> a écrit dans le message de
(e-mail address removed)...

The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord =
ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by
"ITNBR".
I
have
tried changing the primary field and "DisplayMember" to "ITNBR". This
will
change the field in my combo box but I get an error when I do the
lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike


:

I have searched Google with every possible combination of key words
I
can
imagine and have not been able to find an answer to what I think is
a
very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to spell
it
out
for me.

I am trying to write a very simple program in VB.Net 2005. that will
search
a single Access database table and return the value of one field
based
on
criteria that I provide for any other field I choose. The only way
I
have
had any success is if I search by the primary key field, which does
not
give
me the versatility that I need.

Can anyone point me to some sample code that will help me accomplish
this?

Thanks in advance.

Mike

You should connect with the OleDb database providers and then do the
search with a simple Sql query.

If you post a bit of the target table's schema (column names mainly)
and tell me which fields you need to search I'll be more than happy to
write up some simple code for you.

Thanks,

Seth Rowe
 
Mike,

You need for that the command.executescalar, however VBNet is when you are a
newbie not as simple as VBA. To get a field from a database you need:
a connection
a connectionstring
a command
an SQL string.

The SQL string can be as simple as
"Select Name from Employees where EmployeeNumber = '1'"
(I spare you the use of parameters for the moment).

This can mean as simple like this as it is for VBNet (typed here in the
message so watch simple typing or other errors)

Dim conn as New SQLConnection("The connectionString")

http://www.connectionstrings.com/

dim cmd as new OleDB.OleDBcommand("theSQLString", conn)

dim TheName as string = cmd.ExecuteScalar

Cor


MikeS said:
Cor....thank you for your vote of confidence. Maybe this sounded more
difficult than it actually is. To put it in simple terms, all I want to
do
is seach a table for ONE unique record and read only ONE field from it. I
don't want to edit it....I don't want to modify it.....I don't want to
delete
it.....all I want to do is read ONE field from it. And I don't want to
search by the primary key field. I want to be able to search by Employee
Number or First Name or Last Name or any criteria field I choose and
return
his or her "Shoe Size".

In VB for Apps I can do this by using a simple statement such as:

MyValue = DLookup("[RequestedField]", "tablename", "[CriteriaField] = " &
textbox)

I just wanted to do the same thing in VB.Net. I apologize for making it
so
difficult.

Thanks,

Mike


Cor Ligthert said:
MikeS,

The problem is that a novice can ask more difficult questions than
somebody
who knows what he wants.

You have at least two options:
find what you want in direct in the database
find what you want in a datatable.

For both you got an answer. The first is very well with very hug
databases.
The latter is better if it is about smal databases.

Cor

MikeS said:
Patrice, thank you for your comments and effort. Unfortunately, that
doesn't
tell me what I need to know. As I originally mentioned, I am a novice
and
any help would have to be in the form of very precise syntax or at
least a
very good example. I also mentioned that I tried setting changing the
primary key in the table to the field that I wanted to use for the
search
criteria and that didn't work.

Any additional information you could add would be greatly appreciated.

Thanks,

Mike

:

From http://msdn2.microsoft.com/en-us/library/ydd48eyk.aspx (Find
documentation) :

[To use the Find method, the DataTable object to which the
DataRowCollection
object belongs must have at least one column designated as a primary
key
column. See the PrimaryKey property for more information about how to
create
a primary key column.]

So Find can only search using the primary key...

Depending on what you are trying to do you could use the
DataTable.Select
method or DataTable.DefaultView.RowFilter property to select rows
using
a
more general criteria.

A a side note (and I know this is not your code) IMO it would better
to
check explicitely if the criteria returns nothing. The code doesn't
work
if
a row is not found. It raises an error and then display a message
saying
that the row is not found. Actually it will display the message
regardless
of the real reason for which the code fails (for example you could
test
if
the found row is nothing so that an error is raised only if you have a
real
error)...

---
Patrice

"MikeS" <[email protected]> a écrit dans le message de
(e-mail address removed)...

The Access file name is "ItemMaster.dbf"
The table name is "Itemlist"
The field names are "ID (primary), ITNBR, ITDSC, UCDEF"

This is the code that populates the combobox:

cboItemNum.DataSource = ItemMasterDataSet1.Tables("ItemList")
cboItemNum.DisplayMember = "ID"

The following is the code snippet I found to search the table:

Dim strItemNum As String
Dim drSelectedRecord As DataRow
strItemNum = cboItemNum.Text

drSelectedRecord =
ItemMasterDataSet1.ItemList.Rows.Find(strItemNum)

Try
lblItemDesc2.Text = drSelectedRecord(1).ToString & _
" - " & drSelectedRecord(2).ToString

Catch ex As Exception
MsgBox("Record " & strItemNum & " was not found.")

End Try

The problem is I can only search by "ID" and I want to search by
"ITNBR".
I
have
tried changing the primary field and "DisplayMember" to "ITNBR".
This
will
change the field in my combo box but I get an error when I do the
lookup.

Let me know if you need any additional information.

Thanks in advance.

Mike


:

I have searched Google with every possible combination of key
words
I
can
imagine and have not been able to find an answer to what I think
is
a
very
simple question and probably has a very simple solution.

Keep in mind.....I am a NOVICE programmer, so you will have to
spell
it
out
for me.

I am trying to write a very simple program in VB.Net 2005. that
will
search
a single Access database table and return the value of one field
based
on
criteria that I provide for any other field I choose. The only
way
I
have
had any success is if I search by the primary key field, which
does
not
give
me the versatility that I need.

Can anyone point me to some sample code that will help me
accomplish
this?

Thanks in advance.

Mike

You should connect with the OleDb database providers and then do
the
search with a simple Sql query.

If you post a bit of the target table's schema (column names
mainly)
and tell me which fields you need to search I'll be more than happy
to
write up some simple code for you.

Thanks,

Seth Rowe
 
Seth,

I was hoping you would write some simple code to demostrate this (as you
mentioned). Did you forget me?

Here is an example of what I'm trying to do. Say you have an employee
database with the fields ID, EMPNUM, FNAME, LNAME, USERID, HIREDATE, DEPT,
ETC. I wan't to be able to lookup and employee my EMPNUM and return the
contents of DEPT to a variable. I may later want to lookup by USERID or some
other unique field and return the value of yet another field. The only way I
could get this to work is to to the lookup by ID which is the primary field
in the table. I tried changing the primary key to EMPNUM but it still didn't
work.

Can you give me some guidance (in simple terms, preferably in the correct
syntax) on how to accomplish this?

Thanks in advance,

Mike
 
I was hoping you would write some simple code to demostrate this (as you
mentioned). Did you forget me?

I didn't forget you - I just assumed the samples Cor and Patrice gave
you would suffice.
Here is an example of what I'm trying to do. Say you have an employee
database with the fields ID, EMPNUM, FNAME, LNAME, USERID, HIREDATE, DEPT,
ETC. I wan't to be able to lookup and employee my EMPNUM and return the
contents of DEPT to a variable. I may later want to lookup by USERID or some
other unique field and return the value of yet another field. The only way I
could get this to work is to to the lookup by ID which is the primary field
in the table. I tried changing the primary key to EMPNUM but it still didn't
work.

I would ditch the datatable all together, and talk straight to the
database.

This is typed in the message so it could have some errors:

/////////////////////////
Imports System.Data.OleDb

Using conn As New OleDbConnection("MyConnectionString"), com As
OleDbCommand = conn.CreateCommand()
conn.Open()
com.CommandType = Data.CommandType.Text
com.CommandText = "Select DEPT From TableName Where EMPNUM =
@EMPNUM"
com.Parameters.Add("@EMPNUM", OleDbType.VarChar).Value = "555"
Dim dept As String = String.Empty
Try
dept = com.ExecuteScalar().ToString()
Catch ex As NullReferenceException
dept = "No Employee Found"
End Try
End Using
///////////////////////////

If you want a different search then you'll need to modify the Sql
string you pass to the database.

Thanks,

Seth Rowe
 
Seth,

Thank you very much. I had to make a couple of adjustments but it worked
great.

The "conn" and "com" had to be entered into two separate (nested) "using/end
using" statements.

Thanks again.

Mike
 
The "conn" and "com" had to be entered into two separate (nested) "using/end
using" statements.

They shouldn't have to - the comma seperator should allow both to work
with the single using block. It doesn't matter either way, as both
will compile the same, it's merely personal preference.

Thanks,

Seth Rowe
 
Back
Top