View Full Version : check if a value is in a recordset
flowergirl
09-04-2011, 09:09 PM
How to check if a value is in a recordset?
Thanks for your help.
mikerickson
09-04-2011, 11:21 PM
="Cat is in H11:L25 " & SUMPRODUCT(--(H11:L25="cat")) & " times."
or use COUNTIF.
flowergirl
09-04-2011, 11:38 PM
Hi,
The recordset contains data from a table in SQL Server. I need to traverse the recordset in order to find if there is a match between a value in the recordset and the given value.
Please help me out with the code.
Thanks for your help.
Bob Phillips
09-05-2011, 01:54 AM
Use filter on the recordset.
RS.Filter = "Gender = 'M'"
flowergirl
09-05-2011, 02:26 AM
This is not working. Can you please suggest an alternative or send the full code.
I am trying to check if a certain value exists in a recordset.
Thanks for your help.
Bob Phillips
09-05-2011, 02:33 AM
Why don't you show us your code and what you want to check for?
flowergirl
09-05-2011, 03:22 AM
Here's my code:
Sub test_multiple_rows()
Dim dbConnection As New ADODB.Connection
Dim dbRecordset As New ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim ary As Variant
Dim i As Integer
Dim j As Integer
Dim n As Integer
strConn = "Driver={SQL Server};Server=Test-db;uid=hrpms;pwd=hrpms;databasename=hr_pms;"
dbConnection.ConnectionString = strConn
dbConnection.ConnectionTimeout = 60
dbConnection.Open
strSQL = "Select * from test_employee"
'dbRecordset.CursorLocation = adUseServer
'dbRecordset.Open strSQL, dbConnection, adOpenDynamic, adLockReadOnly, adCmdText
dbRecordset.ActiveConnection = dbConnection
dbRecordset.Open strSQL
ary = dbRecordset.GetRows()
For i = 0 To UBound(ary)
For j = 0 To UBound(ary)
If ary(i, j) = 2000 Then
MsgBox "Found value"
End If
Next
Next
dbRecordset.Close
dbConnection.Close
Set dbRecordset = Nothing
Set dbConnection = Nothing
End Sub
Aflatoon
09-05-2011, 05:33 AM
Are you trying to find the value in any field in the recordset, or a particular one? Your code ought to work for the former.
Bob Phillips
09-05-2011, 08:12 AM
What is the column name you are checking for that value?
flowergirl
09-05-2011, 08:42 PM
Hi,
I want to search in the Employee_id field only.
Thanks for your help.
Aflatoon
09-05-2011, 10:23 PM
Then xld's initial response is exactly what you need. How did you use it?
flowergirl
09-05-2011, 10:34 PM
Hi,
I tried to use XLD's method.
If dbRecordset.Filter = "Emp_Id=2000" Then ' 2000 is in the database
MsgBox "Found value"
Else
MsgBox "Not Found value"
End If
If dbRecordset.Filter = "Emp_Id=10000" Then ' 10000 is not in the database
MsgBox "Found value"
Else
MsgBox "Not Found value"
End If
But this is always returning "Not Found value"
Aussiebear
09-05-2011, 11:18 PM
Are you sure that its not simply reflecting the the value from the first IF test?
Bob Phillips
09-06-2011, 01:16 AM
What you should do is something along these lines
dbRecordset.Filter = "Emp_Id=2000"
If Not dbecordset.EOF Then
MsgBox "Record found"
End If
flowergirl
09-06-2011, 01:20 AM
Thanks a lot
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.