ADO.NET DataTable Select Row if Found Get ID

hi,

TBL_Values

ID        value1      value2           value3
==========================
1          10             29                 1
2          30             32                 3
3          34             45                 5

When User passes a value like for eg :   24  (program should check in what range it lies ie value1= value2  and output the corresponding value of value3

considering 24  it lies inbetween 10-29 (value1-value2) so it should show value3 value which is 1

for this I had written code which returns a dataTable. But I am not able to figure out how we can return the value3 value  using DataTable.select
statement

Here is the code

Dim cnn As New SqlClient.SqlConnection

          dim x as integer = 24  'hardcoded to test
        Dim dt As New DataTable

        Dim strsql As String

        cnn.ConnectionString = "Persist Security Info=False;Integrated Security=SSPI;database=test;server=LOGO"
        cnn.Open()
     
        Dim SQLAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("select * from tbl_values", cnn)
        SQLAdapter.Fill(dt)

        Dim dr As DataRow()
           
           dr = dt.select("value1<=x  and x<=value2)  
 

       IF FOUND WE need to get the corresponding value in value3 column / If not found just display message.



         
         
 
sainavya1215Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
YOu can perform search on datatatble. The only condition - the dat must have key

Dim dr As DataRow = dt.Rows.Find(Value of the key)' ID of ths range
'get the value value in one of the fields
        msgbox(dr.Item("Value3")

let me know if need to know how to set the key
LacutahCommented:
A more concise method would be to use a DataView.  A DataView is a subset of the data, and is basically defined by passing a filter, sort order and or rowstate.  In the case of your data, a simple filter should result in just one row retunred.  

As an example, use the following after your " SQLAdapter.Fill(dt)" line:
        Dim value3 As Integer 'Something to hold the value in

        Dim dv As New DataView(dt)
        dv.RowFilter = "value1 <= x and value2 >= x"

        If dv.Count = 0 Then 'Not found
            'do something if not found
            MessageBox.Show("Not found in range...")
        Else
            value3 = CType(dv.Item(0)("value3"), Integer) 'A dataview acts much like a datatable - but has items instead of rows.
            'Do something with value3...
        End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmarinovCommented:
Hi sainavya1215,

i think you are in the correct way , but you use Select methond incorrectly

try this

dr = dt.select("value1<=" & x & " and " & x & "<=value2")  
then

if dr is nothing then
'display error message
else
'display dr("value3")
end if


Regards!
B..M
mmarinov
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

sainavya1215Author Commented:
Thanks Lucutah / mmarinov.

I used Dataview and DataTable Select methods ..Works fine.

When we used   datview.RowFilter or DataTable.select Statements without looping how is it able to find the search range specified.
could u pls explain .......

Thx once again for all the efforts.
mmarinovCommented:
do you mean how to find out the number of the founded records ?

B..M
mmarinov
sainavya1215Author Commented:
i mean when we use dr = dt.select("value1<=" & x & " and " & x & "<=value2")  
 
internally does it loop and check for the specific record
mmarinovCommented:
unfortunatelly i can not answer you at this question because i don't know, but i found long time ago that it is 3-4 times faster than the normal loo for huge tables so may be it is not looping through every records

B>.M
mmarinov
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.