ado filter throwing conflict error

First the code
With rs_cycleCounts
		.ActiveConnection = o_databaseConnection
		.Source = "select * from tblCycleCount Where ShortID ='" & s_shortID & "' order by Location, ItemID, Serial"
		.CursorType = adOpenKeySet
		.CursorLocation = adUseClient
		.LockType = adLockBatchOptimistic
    End With
    Do Until rs_parts.eof
		If  IsNull(rs_parts("serial").Value) or rs_parts("serial").Value = ""  Then
			rs_cycleCounts.Filter = "partno = " & rs_parts("partNo").Value & " AND serial = '' AND location ='" & rs_parts("Location").Value & "'"
			sqlCommandText = "Serial='" & rs_parts("serial").Value & "' AND (( eSeq <= " & rs_parts("eSeq").Value & " AND bSeq >=" & rs_parts("eSeq").Value & ") OR " & _
			"( bSeq <= " & rs_parts("eSeq").Value & " AND eSeq >=" & rs_parts("eSeq").Value & "))" 
			rs_cycleCounts.Filter = sqlCommandText
		End IF

Open in new window

Now the issue when I run just using this for a filter (values from the watch window)
"(( eSeq <= 1196 AND bSeq >=1196) OR ( bSeq <= 1196 AND eSeq >=1196))" everything works as expected. However when I add 1 more field, "Serial='SN000276' AND (( eSeq <= 1196 AND bSeq >=1196) OR ( bSeq <= 1196 AND eSeq >=1196))" I get the following error:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Is the error you get on your page pointing to what would be line 23 above?

Make sure capitalization is correct for field names.  Is the field "Serial" the correct field type/length for the given output "SN000276"?

On line 23 above change from

rs_cycleCounts.Filter = sqlCommandText

Open in new window


response.write sqlCommandText

Open in new window

And lets see if the output is as expected.
Big MontyWeb Ninja at largeCommented:
instead of using the FILTER property, why not just build up a WHERE clause on your conditions and get your data that way. better yet, I would put your sql into a stored procedure and get your data that way:

create PROCEDURE [dbo].[sp_filterParts]
        @shortID varchar( 50 ),
	@partNo int,
	@location varchar( 50 ),
	@serial varchar( 50 ),
	@eSeq int
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	if isNull( serial, '' ) = ''
            select * from tblCycleCount 
            Where ShortID =@shortID
                   and partno = @partNo 
                   and serial = '' 
                   and location = @location
                   order by Location, ItemID, Serial;
           select * from tblCycleCount 
           Where ShortID =@shortID
               and Serial=@serial 
               and ( ( eSeq <= @eSeq and bSeq >=@eSeq ) OR 
			( bSeq <= @eSeq and eSeq >= @eSeq ) );


Open in new window

then, you can use a parameterized query to run the stored procedure:

dim cmd
set cmd = Server.CreateObject("ADODB.Command")

sql = "exec sp_filterParts ?, ?, ?, ?, ?"
        with cmd
            .ActiveConnection = o_databaseConnection
            .CommandText = sql
            .Parameters.Append  .CreateParameter( "shortID", adVarchar, adParamInput, 50, s_shortID )
            .Parameters.Append  .CreateParameter( "partNo", adInteger, adParamInput, , rs_parts("partNo").Value )
            .Parameters.Append  .CreateParameter( "location", adVarchar, adParamInput, 50, rs_parts("Location").Value )
            .Parameters.Append  .CreateParameter( "serial", adVarchar, adParamInput, 50, rs_parts("serial").Value )
            .Parameters.Append  .CreateParameter( "eSeq", adInteger, adParamInput, , rs_parts("eSeq").Value )
        end with
        rs_cycleCounts.Open cmd

Do Until rs_parts.eof
     '-- process data

Open in new window

i learned at an early age of my classic asp learning that the FILTER property is just not worth using and should be avoided. I've always found it to be very unreliable, just my 2 cents worth :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I would agree with avoiding ado filter.  I have never found it reliable.   If you need to requery on the page with similar sql, I will either build my recordset into a function or if the total output is not very large, send it all to the browser and let javascript/jquery filter down in subsequent portions of the page.
bensonwalkerAuthor Commented:
I validated the case and filter still doesn't work... So I'm following your recommendation and using your stored procedure. Thanks for the code and the advise
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.