PDA

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