Re: Record Lookup

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" <MikeS@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de news:
6B01D10B-5A7F-41ED-80E9-8D49B11763C5@xxxxxxxxxxxxxxxx
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" wrote:

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" <MikeS@xxxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de
news:
EF0501C3-CC9B-4A5E-A4F3-DBC53163777E@xxxxxxxxxxxxxxxx

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


"rowe_newsgroups" wrote:

On Sep 11, 9:18 am, MikeS <Mi...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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







.

Relevant Pages

  • RE: BindingSource.Find with multiple primary keys
    ... Dim par1 As Integer = 5 ... ' Verweis auf Tabelle ... ' DataRow heraussuchen ... To set the position with one primary key works fine as shown in the code ...
    (microsoft.public.dotnet.languages.vb)
  • Re: determining if this is the first new record in a subform
    ... sorts of errors in the subform... ... Dim rs As DAO.Recordset ... Dim strSql As String ... tblProjectStep.StepNum (Primary Key – Text – StepNum from tblProjectStep) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Conditional Copy/Paste
    ... Dim ws As Worksheet ... 'Add the heading to the criteria area ... 'transfer data to individual City worksheets ... Set cityListRange = Sheets.Range.CurrentRegion ...
    (microsoft.public.excel.misc)
  • Re: Delete a row with a certain phrase?
    ... Dim rTable As Range ... 'Get the criteria in the form of text or number. ... If vCriteria = "False" Then Exit Sub ... If lCol = 0 Then Exit Sub ...
    (microsoft.public.excel.programming)
  • Re: Macro to copy files
    ... If that does not make sense here are a few examples, the criteria is to copy ... The macro seems to work fine until it reaches the third document in the ... 'This example copy all files between certain dates from FromPath to ToPath. ... Dim FromPath As String ...
    (microsoft.public.excel.programming)