Solved

Find an item by non-key column in dataset

Posted on 2004-10-11
3
209 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now