>>>>>Hi everyone, I could use some advice on this.
>>>>>We can define a select statement like this

>>>>>Dim SQL As String = "SELECT [tipid], [rtaid], [projectname], [location], [shortdesc], [sponsor] FROM [projects] WHERE (archived = @aFilter) ORDER BY [sponsor], [projectname]"
>>>>>Dim oCommand As New SqlCommand(SQL, oConn)
>>>>>oCommand.Parameters.AddWithValue("aFilter", somevalue)

but what do we do when we need WHERE (@acomplicatedstring) which may not involve the archived field but may or may not involve others? Is it appropriate to build the string by concatenation and not use parameters in this case? I am trying to allow the user to build a query that allows any number of variables.
>>>>>
>>>>>Thanks
>>>>
>>>>Instead of building a query that has the parameter values in it, you could set parameters for all fields you might need. Set the uneeded parameters to null and ignore them in the command..
>>>>
>>>>ie..
>>>>
>>>>where (@param1 is null or field1=@param1) and (@param2 is null or field2=@param2)...
>>>
>>>This is not a good way to write a query for performance reasons. Please check Erland's blog I quoted earlier.
>>
>>Thanks Naomi. That give me a little vision on how to do this. Let me ask another related question. Suppose we have the user build this complex "filter" using these techniques. Is there a good way to persist this filter across several pages?
>>(I'm still hung on the VFP way of thinking.)


>This looks like a good candidate for building a stored procedure that builds a command string based on parameters that contain the user's selection criteria and then uses sp_sqlexec to execute the string. If you want to do that on the .NET side of things, you could use StringBuilder.
>

Thanks Linda. That's basically what I'm doing but in the code behind.