[dba-SQLServer]ADO FInd and SQL Server

My fave approach to this is not to write it this way, simply because it's so
hard to parse (for a human). I prefer this style:

rstBuilding.Filter "fldClusterID = \\txtClusterID\\ AND fldBuildingNumber =
\\txtBuildingNumber\\"

Given such a string, it's trivial to call Replace() once for each text
marker. Given an additional function that I call q(), which is a one-liner
that adds single quotes before and after a given value, then the problem
reduces to something quite trivial.

strSQL = SELECT * FROM somewhere WHERE thisPK = \\myValue1\\ AND thisOtherPK
= \\myValue2 AND so on.

strSQL = Replace(strSQL, "\\myValue1\\", 123)
strSQL = Replace(strSQL, "\\myValue2\\", q(CustomerID)
and so on...

Just my opinion. Could simply be that at my advanced age I have difficulty
reading all those ampersands and form references :-)

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
Mwp.Reid at Queens-Belfast.AC.UK
Sent: Monday, August 25, 2003 12:30 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]ADO FInd and SQL Server


We need to pass multipe values to FInd or Filter in ADO.

Any ideas?

  rstBuilding.Open ("select * from tblBuilding"), cnn, adOpenKeyset,
adLockPessimistic

        rstBuilding.Filter "fldClusterID = " & Val(Nz(txtClusterID)) & " AND
fldBuildingNumber = '" _
            & Nz(txtBuildingNumber) & "'"


Martin

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003