Re: Ordering a filtered proximity search

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Feb 5, 4:25 am, "Daniel Crichton" <msn...@xxxxxxxxxxxxxxxx> wrote:
Organic wrote on Mon, 4 Feb 2008 10:14:29 -0800 (PST):

Creating appended queries with WHERE 1 = @p1 is a technique I learned
from VB programmers in the MSDN Forum. It has always worked for me so
I have used it for complex searches like this. I have always assumed
that 1 = @p1 is a placeholder that simply opens the query to additional
parameters with values that can be defined at runtime.

As I stated before, it'll only work if @p1 has a value starting with 1. Go
back to other applications you've written that use this technique and look
at what they set @p1 to. I'm pretty sure you should be setting the p1
parameter to 1 in your code, but you seem to have missed that step. It's
also unnecessary, for the reasons I posted in my earlier replies. It's a
technique I've seen, albeit rarely, and I've been programming VB for 14
years writing commercial applications.

As I reflect on your comments above (and the apparent limitations of
this approach), I am inclined to go to Plan B, which is a stored
procedure that accomplishes essentially the same thing and is probably
a bit faster and more secure. I have one bug with the SP that I need
to work out, and may bring it before this forum if I can't get it
solved on my own.

An SP is a better idea if you can manage it. Try to steer away from dynamic
SQL if at all possible, but if you do use dynamic SQL you've at least taken
the right steps in using parameters rather than concatenating values inline.

As you requested, here is the complete code for the proximity search
with multiple filters that derive from the specific appended query
parameters. It runs fine and allows all 6 filters to work quite well.
However it doesn't have the ORDER BY clause in the query:
Dim p1 As New SqlParameter("@p1", 1)
command.Parameters.Add(p1)

This is the only place you reference p1, and it doesn't get given a value. I
cannot see how this could possibly work even without the ORDER BY. It looks
like you might have missed a couple of commas out of the SqlParameter line,
and that 1 should be in the value position, not in the datatype position
it's in now. If all this is supposed to do is set @p1 to 1, then you can
skip this step and just change @p1 to 1 in the initial string.

command.CommandText = myQuery.ToString

Before this line, you should be able to print out the value of
myQuery.ToString. If it's got WHERE 1 = @p1 AND in it, and you still haven't
set the value of the parameter p1 then the syntax is invalid.

I've done a Google search for "WHERE 1 = @p1" to find other posts about this
technique, and the only result is this thread. Searching for "WHERE 1 = 1"
finds thousands of results - and for this reason I'm convinced you've made a
mistake here.

--
Dan

Dan:

I don't have an answer to your questions/comments other than it seems
to work for most simple FTS queries.

But you have convinced me to abandon this approach and just use a
stored procedure.

Best wishes, Dave
.

Relevant Pages

  • Re: Ordering a filtered proximity search
    ... from VB programmers in the MSDN Forum. ... with multiple filters that derive from the specific appended query ... and that 1 should be in the value position, not in the datatype position ...
    (microsoft.public.sqlserver.fulltext)
  • Microsoft Cleanliness
    ... I guess I've been working with A97 too long. ... In the database window they added a couple of wizards to clutter it up instead of. ... MS preceeds each table with the word TABLE and each query with "QUERY". ... I guess MS programmers have never heard a dropdown can have more than one column. ...
    (comp.databases.ms-access)
  • Re: Different MSSQL output date format from the same PHP script
    ... Especially when "SELECT *" queries just do have their uses. ... And the fact your programs and databases evolve makes it even more important to specify column names. ... You wouldn't want to ever want to, say, run a query returning all ... You may think it's not bad, but many other, more experienced programmers will tell you it is. ...
    (comp.lang.php)
  • "insert/delete queries" <--- careless parlance ?
    ... people that post to the newsgroups aren't programmers at ... query" "update ... When I learned SQL I was taught that the language ... >Data Manipulation Language commands ...
    (microsoft.public.access.queries)