VB and ADO not giving accurate results

[ChrisQuick]

Private Sub DoFindOwner(strOwnerName As String)
Dim cnnAccess As New ADODB.Connection
Dim rsOwner As New ADODB.Recordset
Dim strSQLSelect As String
Dim rsArray() As Variant
Dim intRecs As Integer

strSQLSelect = "select akpar_,akpsad,tcnam1,tcnam2,akpar from rpmout where tcnam1 like '%" & strOwnerName & "%' or tcnam2 like '%" & strOwnerName & "%' order by tcnam1"
cnnAccess.open "mydsn"
'cnnAccess.open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=""d:\mydir\rpmout.mdb"";"

On Error Resume Next
rsOwner.open strSQLSelect, cnnAccess

If Not rsOwner.EOF Then
rsArray = rsOwner.GetRows
intRecs = UBound(rsArray, 2)
MsgBox intRecs, vbInformation
Else
MsgBox "No records", vbInformation
End If


rsOwner.Close
Set rsOwner = Nothing
cnnAccess.Close
Set cnnAccess = Nothing
End Function


If I run the function and my strOwnerName is equal to 'smith' for example, I get all the records that have smith in either the tcnam1 or tcnam2 fields. If my strOwnerName is equal to 'e' i only get one record??

I can do the same query (with the 'e')in MS Access 2000(after changing the % to *) and get back over 40K records.

Any ideas? chrisquick@aol.com
Geographic Information System (GIS), ASP, some Oracle

 

[DougP]

If this is an Access database then use Asterisk's * not percents % they is usually for SQL Server.

When ever I have trouble with variables I try to "hard code" in a value just to test it.
I took your SQL string pasted into Access 2000 and massaged it a bit.

strSQLSelect = "SELECT rpmout.akpar_, rpmout.akpsad, rpmout.tcnam1, rpmout.tcnam2, rpmout.akpar
FROM rpmout
WHERE (((rpmout.tcnam1) Like '*smith*')) OR (((rpmout.tcnam2) Like '*jones*'));"

also is this your field
akpar_
or is it
akpar_akpsad

there's a comma in there ???
"select akpar_,akpsad,tcnam1




DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[dpaulson]

If this is an Access database then use Asterisk's * not percents % they is usually for SQL Server.



Not true. The '%' is the correct wildcard when using ADO.

David Paulson

 

[dpaulson]

Take out the on error goto statement. You shouldn't need it anyway. At least it will display an error if there is one in you sql.
David Paulson

 

[ChrisQuick]

The akpar_ and akpsad are two seperate fields. I can also run the same query with the where clause as:

where tcnam1 like '%e%' or tcnam2 like '%e%'

using ADO through Active Server Pages and get over 40K records (the correct results), so i don't think it is an ADO issue. Seems to only come out this way in VB. chrisquick@aol.com
Geographic Information System (GIS), ASP, some Oracle

 

[MarkSweetland]

I'd try and use the Trim() function on the passed variable in case the strOwnerName is somehow includes hard spaces and your SQL LIKE statement converts it to: "%e %"


Mark

 

[MatsHulten]

VB and ASP uses the same objects for connecting to DB:s so if this code works fine there, it is defenetily an error with your code.

I'd try the following:
Do a debug.print on strSqlSelect before executing the question, and check if the syntax is valid and returns the desired result. (Just to be sure.)

Remove the onerror statement.

Check the syntax for the getrows-method. In MSDN they use a GetRowsOK-wrapper function, that the claim is necessary for the function to work. May be a clue...

Good Luck
-Mats