Re: Record Lookup

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" <MikeS@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht news:02D2CF8B-59D8-46FC-B7CD-4D74D89BFAC8@xxxxxxxxxxxxxxxx
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[MVP]" wrote:

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" <MikeS@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
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: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Record Lookup
    ... Number or First Name or Last Name or any criteria field I choose and return ... The problem is that a novice can ask more difficult questions than somebody ... primary key in the table to the field that I wanted to use for the search ... a single Access database table and return the value of one field ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Record Lookup
    ... The problem is that a novice can ask more difficult questions than somebody who knows what he wants. ... primary key in the table to the field that I wanted to use for the search ... criteria and that didn't work. ... >>> a single Access database table and return the value of one field ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Updating the SQL key value
    ... before creating the database, by the time I've come to pick a primary key, ... articles, each of which must be issued under a particular licence (e.g. ... GPL GNU General Public Licence http://www.gnu ... ...
    (comp.lang.php)
  • Re: Concatenate and Null Values -- Features
    ... Tony Toews dislikes cascade deletes as well as cascade updates, ... fence regarding the use of natural versus surrogate (autonumber) keys. ... Database Normalization Tips ... For optimal database design and performance, the primary key of a table ...
    (microsoft.public.access.reports)
Loading