check if a value is in a recordset

[flowergirl]

How to check if a value is in a recordset?

Thanks for your help.

[mikerickson]

="Cat is in H11:L25 " & SUMPRODUCT(--(H11:L25="cat")) & " times."

or use COUNTIF.

[flowergirl]

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]

Use filter on the recordset.

[vba]

RS.Filter = "Gender = 'M'"[/vba]

[flowergirl]

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]

Why don't you show us your code and what you want to check for?

[flowergirl]

Here's my code:

[VBA]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
[/VBA]

[Aflatoon]

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]

What is the column name you are checking for that value?

[flowergirl]

Hi,

I want to search in the Employee_id field only.

Thanks for your help.

[Aflatoon]

Then xld's initial response is exactly what you need. How did you use it?

[flowergirl]

Hi,

I tried to use XLD's method.

[vba]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[/vba]
But this is always returning "Not Found value"

[Aussiebear]

Are you sure that its not simply reflecting the the value from the first IF test?

[Bob Phillips]

What you should do is something along these lines

[vba]

dbRecordset.Filter = "Emp_Id=2000"
If Not dbecordset.EOF Then

MsgBox "Record found"
End If[/vba]

[flowergirl]

Thanks a lot