sainavya1215
asked on
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=tes t;server=L OGO"
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.
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=tes
cnn.Open()
Dim SQLAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
do you mean how to find out the number of the founded records ?
B..M
mmarinov
B..M
mmarinov
ASKER
i mean when we use dr = dt.select("value1<=" & x & " and " & x & "<=value2")
internally does it loop and check for the specific record
internally does it loop and check for the specific record
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
B>.M
mmarinov
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