Solved

Find an item by non-key column in dataset

Posted on 2004-10-11
3
219 Views
Last Modified: 2010-04-23
Hi, i have an established connection, a valid dataset and a working dataadapter. There is a table and few columns in it. Let me define

dataset: ds
dataadapter: daTable
table: Table
columns in table: itemNo, itemName, UPCcode
key column: itemNo

i need to find out an itemNo by UPCcode value using dataset, not direct connection to database
what should my command look like?

Thanks in advance
0
Comment
Question by:UnFiRe
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
GohdanTheMoblin earned 500 total points
ID: 12281122
Try this:      

        Dim dr As DataRow() = ds.Tables("Table").Select("UPCcode = " & value)
        If dr.Length > 0 Then
            Dim itemNo As Integer = CInt(dr(0)("itemNo"))
        End If

Assuming itemNo is an Integer.
0
 
LVL 1

Author Comment

by:UnFiRe
ID: 12287668
Hi,
This expression worked to some extent. Thanks a lot for it
However, there is some problem.
I have another column called "OLDITEM" in the same table
It is a text field. In fact all fields are text fields because itemNo and OLDITEM and UPCCode can include letters.
My modified expression of a function to define itemNo by either upc or olditem looks like that:

*******************
Public Function itemNoBy(ByVal byWhat As String, ByVal num As String) As String
        Dim itemNo As String
        Dim dr As DataRow()
       
        'parameters passed to this function are either "upc" or "old"
        Select Case byWhat
            Case "upc"
                dr = myRef.dsJB.Itemweb.Select("UPCCODE = " & num)
            Case "old"
                dr = myRef.dsJB.Itemweb.Select("OLDITEM = " & num)
        End Select

        If dr.Length > 0 Then
            itemNo = dr(0)("itemNo").ToString
        Else
            itemNo = "Not Found"
        End If

        Return itemNo
End Function

***********************

Now, when i pass upc - everything works out perfect, however, when i pass old - i get this error. Why would i get it? All fields have absolutely the same properties, they are all text fields and in my dataset they are all String fields.

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll

Additional information: Cannot perform '=' operation on System.String and System.Int32.
0
 
LVL 2

Expert Comment

by:GohdanTheMoblin
ID: 12308720
Oh, I think I see what's happening.  Suppose num is 555.

When you construct the statement
"UPCCODE = " & num

it is constructed as
"UPCCODE = 555"

which is a numeric search, not a string search.  Try changing those statements to
"UPCCODE = '" & num & "'"

It's tough to see, but there are single quotes in there.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question