Ordering a filtered proximity search

It might be easier to fire up profiler or profiler90 and filter on the
account you are using to conduct the search with.

You will then see plain text the query being issued. Daniel is
probably correct, something is probably wrong with your query as
generated.

On Feb 4, 6:09 am, Organic Man wrote:
> On Feb 4, 5:01 am, "Daniel Crichton" wrote:
>
>
>
>
>
> > Organic wrote  on Sun, 3 Feb 2008 13:26:02 -0800 (PST):
>
> > > I am creating a Full-Text Search application for a Visual Basic project
> > > and am having a problem with my sql query when I try to use filtering
> > > on a proximity search and try to order the results.
> > > I start with a simple CONTAINS FTS that is filtered and ordered.  It
> > > works fine and allows filtering and ordering of results.
> > > myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> > > Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> > > FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> > > (SectionText, ' """ & SearchTerm1 & """ ') Order by
> > > FullDocuments.FullDocumentID")
> > > Then I adapt it for Proximity like this:
> > > myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> > > Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> > > FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> > > (SectionText, ' """ & SearchTerm1 & """ AND """ & SearchTerm2 & """ ')
> > > Order by FullDocuments.FullDocumentID")
> > > This proximity query returns returns ordered results.  But when I try
> > > to filter the search with a combo box like I did for the simple
> > > CONTAINS search I get this error:
> > > Incorrect syntax near the keyword 'AND'
> > > If I remove the order clause like this:
> > > myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> > > Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> > > FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> > > (SectionText, ' """ & SearchTerm1 & """ AND """ & SearchTerm2 & """
> > > ')")
> > > The query works fine and I can filter it with the combobox, but of
> > > course the results are not ordered as I wish.
> > > I assume the proximity search error relates to an interaction of AND
> > > 1=@p1 and my order clause, and have tried moving them around in the
> > > query in various ways but no luck.
> > > Any thoughts on how to get the proximity search with a filter to also
> > > include the order clause?
>
> > Add a line of code printing out the SQL statement where you get the syntax
> > error - you should then be able to spot what is going wrong.
>
> > --
> > Dan
>
> Hi Dan.
>
> I am not sure what you mean by adding a line of code printing out the
> SQL statement.  When I do the search with the filter, I get the
> message box with this error: "Incorrect syntax near the keyword
> 'AND'".  There are no details - only an "OK'" button to close the
> message box.
>
> Here is the original code with the proximity (NEAR) version of the SQL
> statement that is giving me problems.
>
>             Dim Connection As New SqlConnection(DataSource)
>             Dim command As New SqlCommand()
>             command.Connection = Connection
>             Dim myQuery As New System.Text.StringBuilder()
>             myQuery.Append("SELECT FullDocuments.FullDocNo,
> FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER JOIN
> Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1
> AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
> SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")
>
>             Dim p1 As New SqlParameter("@p1", 1)
>             command.Parameters.Add(p1)
>
>             If cmbProximityDocType.SelectedIndex > 0 Then
>                 myQuery.Append(" AND DocType = @p2")
>                 Dim p2 As New SqlParameter("@p2", SqlDbType.VarChar,
> 10)
>                 p2.Value = cmbProximityDocType.Text.ToString
>                 command.Parameters.Add(p2)
>             End If
>
> It doesn't seem to matter if the SQL statement uses CONTAINS NEAR OR
> CONTAINS AND (as I posted above), the issue is the same.  I assume
> that it relates to the  use of "AND" between "1 = @p1" and
> "CONTAINS".  But I am puzzled as to why I only get the error message
> when I use the filter and order clause at the same time.  Otherwise
> the syntax seems ok and I am able to do the search which returns
> ordered results.
>
> If the use of the keyword "AND" between "1 = @p1" and "CONTAINS" is
> the issue, I am surprised that it runs at all, which it seems to do
> quite well so long as I don't try to filter it at runtime.- Hide quoted text -
>
> - Show quoted text -
 >> Stay informed about: Ordering a filtered proximity search 

Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):

> I am creating a Full-Text Search application for a Visual Basic project
> and am having a problem with my sql query when I try to use filtering
> on a proximity search and try to order the results.

> I start with a simple CONTAINS FTS that is filtered and ordered. It
> works fine and allows filtering and ordering of results.

> myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> (SectionText, ' """ & SearchTerm1 & """ ') Order by
> FullDocuments.FullDocumentID")

> Then I adapt it for Proximity like this:

> myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> (SectionText, ' """ & SearchTerm1 & """ AND """ & SearchTerm2 & """ ')
> Order by FullDocuments.FullDocumentID")

> This proximity query returns returns ordered results. But when I try
> to filter the search with a combo box like I did for the simple
> CONTAINS search I get this error:

> Incorrect syntax near the keyword 'AND'

> If I remove the order clause like this:

> myQuery.Append("SELECT FullDocuments.FullDocNo, FullDocuments.DocType,
> Details.YearGiven FROM FullDocuments INNER JOIN Details ON
> FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1 AND CONTAINS
> (SectionText, ' """ & SearchTerm1 & """ AND """ & SearchTerm2 & """
> ')")

> The query works fine and I can filter it with the combobox, but of
> course the results are not ordered as I wish.

> I assume the proximity search error relates to an interaction of AND
> 1=@p1 and my order clause, and have tried moving them around in the
> query in various ways but no luck.

> Any thoughts on how to get the proximity search with a filter to also
> include the order clause?


Add a line of code printing out the SQL statement where you get the syntax
error - you should then be able to spot what is going wrong.

--
Dan
 >> Stay informed about: Ordering a filtered proximity search 

On Feb 4, 9:34 am, "Daniel Crichton" wrote:
> Organic wrote on Mon, 4 Feb 2008 03:09:48 -0800 (PST):
>
> > On Feb 4, 5:01 am, "Daniel Crichton" wrote:
>
> >> Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):
>
> >>> I am creating a Full-Text Search application for a Visual Basic
> >>> project and am having a problem with my sql query when I try to use
> >>> filtering on a proximity search and try to order the results.
> >>> I start with a simple CONTAINS FTS that is filtered and ordered. It
> >>> works fine and allows filtering and ordering of results.
>
> >>> The query works fine and I can filter it with the combobox, but of
> >>> course the results are not ordered as I wish.
> >>> I assume the proximity search error relates to an interaction of AND
> >>> 1=@p1 and my order clause, and have tried moving them around in the
> >>> query in various ways but no luck.
> >>> Any thoughts on how to get the proximity search with a filter to
> >>> also include the order clause?
>
> >> Add a line of code printing out the SQL statement where you get the
> >> syntax error - you should then be able to spot what is going wrong.
>
> >> --
> >> Dan
>
> > Hi Dan.
> > I am not sure what you mean by adding a line of code printing out the
> > SQL statement. When I do the search with the filter, I get the message
> > box with this error: "Incorrect syntax near the keyword 'AND'". There
> > are no details - only an "OK'" button to close the message box.
>
> You can add a line to your application to show you what it's sending to SQL
> Server. After you build the SQL string, add a Debug.Print line (or whatever
> your version of VB uses, I still use VB6)
>
> > Here is the original code with the proximity (NEAR) version of the SQL
> > statement that is giving me problems.
> > Dim Connection As New SqlConnection(DataSource)
> > Dim command As New SqlCommand()
> > command.Connection = Connection
> > Dim myQuery As New System.Text.StringBuilder()
> > myQuery.Append("SELECT FullDocuments.FullDocNo,
> > FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER JOIN
> > Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1
> > AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
> > SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")
> > Dim p1 As New SqlParameter("@p1", 1)
> > command.Parameters.Add(p1)
>
> This appears to allow @p1 to be empty. This might be your problem, if I'm
> reading it right (I use VB6 and ADO, not ADO.NET) and it doesn't have a
> value then you might get
>
> WHERE 1 = AND CONTAINS
>
> which isn't valid syntax. What is the point of @p1? If it has a value of 1
> then the query will return results if the rest of the statement, if it has
> any other numeric value then no results will be returned, and if it's empty
> or a non-numeric value then you'll get a syntax error. Why do you need it?
> Why not just have an IF statement in your code that checks the value of the
> variable p1, and if it's not 1 then doesn't even run the code as that would
> be more efficient.
>
> --
> Dan

Dan, Thanks for your input.

I am using Visual Studio 2008, Beta 2. I will be downloading the new
real version soon, so if this is a Beta 2 issue I will know that is
the problem, but I doubt its a beta issue.

@p1 simply allows me to append the query. In this case there are 3
textboxes and 3 comboboxes for filtering. I only included the first
one to keep my original post simple. This is not the most elegant
method but is usually very reliable, even with the full range of FTS
options - well, until I hit his snag by including more than one search
variable, filtering it, and ordering it at the same time.

I have been trying out a dynamic stored procedure using "Where 1=1"
and it works pretty well for proximity searches with multiple search
terms, complex filtering, and ordering - but also hit a small snag
with it so decided to try the less elegant but usually more reliable
appended query approach.

I have tried Hillary's suggestion for using profiler but can't get
Profiler to open the source table ("Only tables created by SQL
Profiler can be opened").

Since the appended query code works if I don't include the ORDER BY
clause, I may just not use ordering in my SQL statement and allow the
columns containing the search results to be ordered at runtime in my
datagrid, giving the user the choice of how to order the results
rather than forcing it in my SQL.
 >> Stay informed about: Ordering a filtered proximity search 

On Feb 4, 11:01 am, "Daniel Crichton" wrote:
> Organic wrote on Mon, 4 Feb 2008 07:40:39 -0800 (PST):
>
> > On Feb 4, 9:34 am, "Daniel Crichton" wrote:
>
> >> Organic wrote on Mon, 4 Feb 2008 03:09:48 -0800 (PST):
>
> >>> On Feb 4, 5:01 am, "Daniel Crichton"
>
> >>> wrote:
>
> >>>> Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):
>
> >>>>> I am creating a Full-Text Search application for a Visual Basic
> >>>>> project and am having a problem with my sql query when I try to
> >>>>> use filtering on a proximity search and try to order the results.
> >>>>> I start with a simple CONTAINS FTS that is filtered and ordered.
> >>>>> It works fine and allows filtering and ordering of results.
>
> >>>>> The query works fine and I can filter it with the combobox, but of
> >>>>> course the results are not ordered as I wish.
> >>>>> I assume the proximity search error relates to an interaction of
> >>>>> AND 1=@p1 and my order clause, and have tried moving them around
> >>>>> in the query in various ways but no luck.
> >>>>> Any thoughts on how to get the proximity search with a filter to
> >>>>> also include the order clause?
>
> >>>> Add a line of code printing out the SQL statement where you get the
> >>>> syntax error - you should then be able to spot what is going wrong.
>
> >>>> --
> >>>> Dan
>
> >>> Hi Dan.
> >>> I am not sure what you mean by adding a line of code printing out
> >>> the
> >>> SQL statement. When I do the search with the filter, I get the
> >>> message box with this error: "Incorrect syntax near the keyword
> >>> 'AND'". There are no details - only an "OK'" button to close the
> >>> message box.
>
> >> You can add a line to your application to show you what it's sending
> >> to SQL
> >> Server. After you build the SQL string, add a Debug.Print line (or
> >> whatever your version of VB uses, I still use VB6)
>
> >>> Here is the original code with the proximity (NEAR) version of the
> >>> SQL statement that is giving me problems.
> >>> Dim Connection As New SqlConnection(DataSource)
> >>> Dim command As New SqlCommand()
> >>> command.Connection = Connection
> >>> Dim myQuery As New System.Text.StringBuilder()
> >>> myQuery.Append("SELECT FullDocuments.FullDocNo,
> >>> FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER
> >>> JOIN
> >>> Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1
> >>> AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
> >>> SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")
> >>> Dim p1 As New SqlParameter("@p1", 1)
> >>> command.Parameters.Add(p1)
>
> >> This appears to allow @p1 to be empty. This might be your problem, if
> >> I'm reading it right (I use VB6 and ADO, not ADO.NET) and it doesn't
> >> have a value then you might get
>
> >> WHERE 1 = AND CONTAINS
>
> >> which isn't valid syntax. What is the point of @p1? If it has a value
> >> of 1 then the query will return results if the rest of the statement,
> >> if it has any other numeric value then no results will be returned,
> >> and if it's empty or a non-numeric value then you'll get a syntax
> >> error. Why do you need it?
> >> Why not just have an IF statement in your code that checks the value
> >> of the variable p1, and if it's not 1 then doesn't even run the code
> >> as that would be more efficient.
>
> >> --
> >> Dan
>
> > Dan, Thanks for your input.
> > I am using Visual Studio 2008, Beta 2. I will be downloading the new
> > real version soon, so if this is a Beta 2 issue I will know that is the
> > problem, but I doubt its a beta issue.
> > @p1 simply allows me to append the query. In this case there are 3
> > textboxes and 3 comboboxes for filtering. I only included the first
> > one to keep my original post simple. This is not the most elegant
> > method but is usually very reliable, even with the full range of FTS
> > options - well, until I hit his snag by including more than one search
> > variable, filtering it, and ordering it at the same time.
> > I have been trying out a dynamic stored procedure using "Where 1=1"
> > and it works pretty well for proximity searches with multiple search
> > terms, complex filtering, and ordering - but also hit a small snag with
> > it so decided to try the less elegant but usually more reliable
> > appended query approach.
>
> WHERE 1=1 just means "return all rows", because 1 will always equal 1. It
> doesn't make any sense to have it in your query. And as I pointed out, if p1
> is empty then your SQL syntax is messed up.
>
> For instance,
>
> SELECT * FROM MyTable WHERE 1=1
>
> will return all the rows in MyTable, and is the same as SELECT * FROM
> MyTable
>
> SELECT * FROM MyTable WHERE 1=1 AND Col1 = 'wibble'
>
> is the same as SELECT * FROM MyTable WHERE Col1 = 'wibble'
>
> SELECT * FROM MyTable WHERE 1=0
>
> will return no rows, and you might as well not run the query at all if
> you're expecting results.
>
> Where did you get the idea that WHERE 1=1 does something special?
>
> As to debugging, in VB6 I would put
>
> Debug.Write adoRec.Source
>
> where adoRec is an ADODB.Recordset object, to see what the string being
> passed to SQL Server is before executing it. You should be able to do
> something similar.
>
> > I have tried Hillary's suggestion for using profiler but can't get
> > Profiler to open the source table ("Only tables created by SQL
> > Profiler can be opened").
> > Since the appended query code works if I don't include the ORDER BY
> > clause, I may just not use ordering in my SQL statement and allow the
> > columns containing the search results to be ordered at runtime in my
> > datagrid, giving the user the choice of how to order the results rather
> > than forcing it in my SQL.
>
> I think it working when you remove the ORDER BY is just coincidental - you
> should be concentrating on getting VB to print to the Immediate window (or
> whatever the debug area is called now) to show what the SQL string is before
> you pass it to SQL Server, until you do this you'll have a hard time working
> out what's causing it.
>
> --
> Dan

This is my procedure:

I run the program and set the search parameters for the proximity
search and select a filtering option from the combobox control. I
click the search button to begin the search.

I have set break points in the code so that I can monitor the values
in the Locals window.

As I step through the code, the initial value of myQuery cannot be
determined. Here is the printout:

"Chars In order to evaluate an indexed property, the property must be
qualified and the arguments must be explicitly supplied by the user."

As I continue to step through the code and get to the p2 parameter
that is appended to the query, the value of myQuery reads:

"Chars Argument not specified for parameter 'index' of 'Public
Property Chars(index As Integer) As Char'."

I don't know if this is referring to a data type problem with the p2
appended query, or simply an inability to determine the value of the
filter, or something else. Any thoughts on what this means?




when I get to the appended query p2 the value of the query
 >> Stay informed about: Ordering a filtered proximity search 

Organic wrote on Mon, 4 Feb 2008 03:09:48 -0800 (PST):

> On Feb 4, 5:01 am, "Daniel Crichton" wrote:
>> Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):



>>> I am creating a Full-Text Search application for a Visual Basic
>>> project and am having a problem with my sql query when I try to use
>>> filtering on a proximity search and try to order the results.
>>> I start with a simple CONTAINS FTS that is filtered and ordered. It
>>> works fine and allows filtering and ordering of results.

>>> The query works fine and I can filter it with the combobox, but of
>>> course the results are not ordered as I wish.
>>> I assume the proximity search error relates to an interaction of AND
>>> 1=@p1 and my order clause, and have tried moving them around in the
>>> query in various ways but no luck.
>>> Any thoughts on how to get the proximity search with a filter to
>>> also include the order clause?

>> Add a line of code printing out the SQL statement where you get the
>> syntax error - you should then be able to spot what is going wrong.

>> --
>> Dan

> Hi Dan.

> I am not sure what you mean by adding a line of code printing out the
> SQL statement. When I do the search with the filter, I get the message
> box with this error: "Incorrect syntax near the keyword 'AND'". There
> are no details - only an "OK'" button to close the message box.

You can add a line to your application to show you what it's sending to SQL
Server. After you build the SQL string, add a Debug.Print line (or whatever
your version of VB uses, I still use VB6)

> Here is the original code with the proximity (NEAR) version of the SQL
> statement that is giving me problems.

> Dim Connection As New SqlConnection(DataSource)
> Dim command As New SqlCommand()
> command.Connection = Connection
> Dim myQuery As New System.Text.StringBuilder()
> myQuery.Append("SELECT FullDocuments.FullDocNo,
> FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER JOIN
> Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1
> AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
> SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")

> Dim p1 As New SqlParameter("@p1", 1)
> command.Parameters.Add(p1)

This appears to allow @p1 to be empty. This might be your problem, if I'm
reading it right (I use VB6 and ADO, not ADO.NET) and it doesn't have a
value then you might get

WHERE 1 = AND CONTAINS

which isn't valid syntax. What is the point of @p1? If it has a value of 1
then the query will return results if the rest of the statement, if it has
any other numeric value then no results will be returned, and if it's empty
or a non-numeric value then you'll get a syntax error. Why do you need it?
Why not just have an IF statement in your code that checks the value of the
variable p1, and if it's not 1 then doesn't even run the code as that would
be more efficient.

--
Dan
 >> Stay informed about: Ordering a filtered proximity search 

Organic wrote on Mon, 4 Feb 2008 07:40:39 -0800 (PST):

> On Feb 4, 9:34 am, "Daniel Crichton" wrote:
>> Organic wrote on Mon, 4 Feb 2008 03:09:48 -0800 (PST):

>>> On Feb 4, 5:01 am, "Daniel Crichton"
>>> wrote:

>>>> Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):

>>>>> I am creating a Full-Text Search application for a Visual Basic
>>>>> project and am having a problem with my sql query when I try to
>>>>> use filtering on a proximity search and try to order the results.
>>>>> I start with a simple CONTAINS FTS that is filtered and ordered.
>>>>> It works fine and allows filtering and ordering of results.

>>>>> The query works fine and I can filter it with the combobox, but of
>>>>> course the results are not ordered as I wish.
>>>>> I assume the proximity search error relates to an interaction of
>>>>> AND 1=@p1 and my order clause, and have tried moving them around
>>>>> in the query in various ways but no luck.
>>>>> Any thoughts on how to get the proximity search with a filter to
>>>>> also include the order clause?

>>>> Add a line of code printing out the SQL statement where you get the
>>>> syntax error - you should then be able to spot what is going wrong.

>>>> --
>>>> Dan

>>> Hi Dan.
>>> I am not sure what you mean by adding a line of code printing out
>>> the
>>> SQL statement. When I do the search with the filter, I get the
>>> message box with this error: "Incorrect syntax near the keyword
>>> 'AND'". There are no details - only an "OK'" button to close the
>>> message box.

>> You can add a line to your application to show you what it's sending
>> to SQL
>> Server. After you build the SQL string, add a Debug.Print line (or
>> whatever your version of VB uses, I still use VB6)

>>> Here is the original code with the proximity (NEAR) version of the
>>> SQL statement that is giving me problems.
>>> Dim Connection As New SqlConnection(DataSource)
>>> Dim command As New SqlCommand()
>>> command.Connection = Connection
>>> Dim myQuery As New System.Text.StringBuilder()
>>> myQuery.Append("SELECT FullDocuments.FullDocNo,
>>> FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER
>>> JOIN
>>> Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 = @p1
>>> AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
>>> SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")
>>> Dim p1 As New SqlParameter("@p1", 1)
>>> command.Parameters.Add(p1)

>> This appears to allow @p1 to be empty. This might be your problem, if
>> I'm reading it right (I use VB6 and ADO, not ADO.NET) and it doesn't
>> have a value then you might get

>> WHERE 1 = AND CONTAINS

>> which isn't valid syntax. What is the point of @p1? If it has a value
>> of 1 then the query will return results if the rest of the statement,
>> if it has any other numeric value then no results will be returned,
>> and if it's empty or a non-numeric value then you'll get a syntax
>> error. Why do you need it?
>> Why not just have an IF statement in your code that checks the value
>> of the variable p1, and if it's not 1 then doesn't even run the code
>> as that would be more efficient.

>> --
>> Dan

> Dan, Thanks for your input.

> I am using Visual Studio 2008, Beta 2. I will be downloading the new
> real version soon, so if this is a Beta 2 issue I will know that is the
> problem, but I doubt its a beta issue.

> @p1 simply allows me to append the query. In this case there are 3
> textboxes and 3 comboboxes for filtering. I only included the first
> one to keep my original post simple. This is not the most elegant
> method but is usually very reliable, even with the full range of FTS
> options - well, until I hit his snag by including more than one search
> variable, filtering it, and ordering it at the same time.


> I have been trying out a dynamic stored procedure using "Where 1=1"
> and it works pretty well for proximity searches with multiple search
> terms, complex filtering, and ordering - but also hit a small snag with
> it so decided to try the less elegant but usually more reliable
> appended query approach.

WHERE 1=1 just means "return all rows", because 1 will always equal 1. It
doesn't make any sense to have it in your query. And as I pointed out, if p1
is empty then your SQL syntax is messed up.

For instance,

SELECT * FROM MyTable WHERE 1=1

will return all the rows in MyTable, and is the same as SELECT * FROM
MyTable



SELECT * FROM MyTable WHERE 1=1 AND Col1 = 'wibble'

is the same as SELECT * FROM MyTable WHERE Col1 = 'wibble'



SELECT * FROM MyTable WHERE 1=0

will return no rows, and you might as well not run the query at all if
you're expecting results.


Where did you get the idea that WHERE 1=1 does something special?


As to debugging, in VB6 I would put

Debug.Write adoRec.Source

where adoRec is an ADODB.Recordset object, to see what the string being
passed to SQL Server is before executing it. You should be able to do
something similar.



> I have tried Hillary's suggestion for using profiler but can't get
> Profiler to open the source table ("Only tables created by SQL
> Profiler can be opened").

> Since the appended query code works if I don't include the ORDER BY
> clause, I may just not use ordering in my SQL statement and allow the
> columns containing the search results to be ordered at runtime in my
> datagrid, giving the user the choice of how to order the results rather
> than forcing it in my SQL.

I think it working when you remove the ORDER BY is just coincidental - you
should be concentrating on getting VB to print to the Immediate window (or
whatever the debug area is called now) to show what the SQL string is before
you pass it to SQL Server, until you do this you'll have a hard time working
out what's causing it.

--
Dan
 >> Stay informed about: Ordering a filtered proximity search 

Organic wrote on Mon, 4 Feb 2008 09:03:01 -0800 (PST):

> On Feb 4, 11:01 am, "Daniel Crichton" wrote:
>> Organic wrote on Mon, 4 Feb 2008 07:40:39 -0800 (PST):

>>> On Feb 4, 9:34 am, "Daniel Crichton"
>>> wrote:

>>>> Organic wrote on Mon, 4 Feb 2008 03:09:48 -0800 (PST):

>>>>> On Feb 4, 5:01 am, "Daniel Crichton"

>>>>> wrote:

>>>>>> Organic wrote on Sun, 3 Feb 2008 13:26:02 -0800 (PST):

>>>>>>> I am creating a Full-Text Search application for a Visual Basic
>>>>>>> project and am having a problem with my sql query when I try to
>>>>>>> use filtering on a proximity search and try to order the
>>>>>>> results.
>>>>>>> I start with a simple CONTAINS FTS that is filtered and ordered.
>>>>>>> It works fine and allows filtering and ordering of results.

>>>>>>> The query works fine and I can filter it with the combobox, but
>>>>>>> of course the results are not ordered as I wish.
>>>>>>> I assume the proximity search error relates to an interaction of
>>>>>>> AND 1=@p1 and my order clause, and have tried moving them around
>>>>>>> in the query in various ways but no luck.
>>>>>>> Any thoughts on how to get the proximity search with a filter to
>>>>>>> also include the order clause?

>>>>>> Add a line of code printing out the SQL statement where you get
>>>>>> the syntax error - you should then be able to spot what is going
>>>>>> wrong.

>>>>>> --
>>>>>> Dan

>>>>> Hi Dan.
>>>>> I am not sure what you mean by adding a line of code printing out
>>>>> the
>>>>> SQL statement. When I do the search with the filter, I get the
>>>>> message box with this error: "Incorrect syntax near the keyword
>>>>> 'AND'". There are no details - only an "OK'" button to close the
>>>>> message box.

>>>> You can add a line to your application to show you what it's
>>>> sending to SQL
>>>> Server. After you build the SQL string, add a Debug.Print line (or
>>>> whatever your version of VB uses, I still use VB6)

>>>>> Here is the original code with the proximity (NEAR) version of the
>>>>> SQL statement that is giving me problems.
>>>>> Dim Connection As New SqlConnection(DataSource)
>>>>> Dim command As New SqlCommand()
>>>>> command.Connection = Connection
>>>>> Dim myQuery As New System.Text.StringBuilder()
>>>>> myQuery.Append("SELECT FullDocuments.FullDocNo,
>>>>> FullDocuments.DocType, Details.YearGiven FROM FullDocuments INNER
>>>>> JOIN
>>>>> Details ON FullDocuments.FullDocNo = Details.FullDocNo WHERE 1 =
>>>>> @p1
>>>>> AND CONTAINS (SectionText, ' """ & SearchTerm1 & """ NEAR """ &
>>>>> SearchTerm2 & """ ') Order by FullDocuments.FullDocumentID")
>>>>> Dim p1 As New SqlParameter("@p1", 1)
>>>>> command.Parameters.Add(p1)

>>>> This appears to allow @p1 to be empty. This might be your problem,
>>>> if
>>>> I'm reading it right (I use VB6 and ADO, not ADO.NET) and it
>>>> doesn't have a value then you might get

>>>> WHERE 1 = AND CONTAINS

>>>> which isn't valid syntax. What is the point of @p1? If it has a
>>>> value of 1 then the query will return results if the rest of the
>>>> statement, if it has any other numeric value then no results will
>>>> be returned, and if it's empty or a non-numeric value then you'll
>>>> get a syntax error. Why do you need it?
>>>> Why not just have an IF statement in your code that checks the
>>>> value of the variable p1, and if it's not 1 then doesn't even run
>>>> the code as that would be more efficient.

>>>> --
>>>> Dan

>>> Dan, Thanks for your input.
>>> I am using Visual Studio 2008, Beta 2. I will be downloading the
>>> new real version soon, so if this is a Beta 2 issue I will know that
>>> is the problem, but I doubt its a beta issue.
>>> @p1 simply allows me to append the query. In this case there are 3
>>> textboxes and 3 comboboxes for filtering. I only included the first
>>> one to keep my original post simple. This is not the most elegant
>>> method but is usually very reliable, even with the full range of FTS
>>> options - well, until I hit his snag by including more than one
>>> search variable, filtering it, and ordering it at the same time.
>>> I have been trying out a dynamic stored procedure using "Where 1=1"
>>> and it works pretty well for proximity searches with multiple search
>>> terms, complex filtering, and ordering - but also hit a small snag
>>> with it so decided to try the less elegant but usually more reliable
>>> appended query approach.

>> WHERE 1=1 just means "return all rows", because 1 will always equal
>> 1. It doesn't make any sense to have it in your query. And as I
>> pointed out, if p1 is empty then your SQL syntax is messed up.

>> For instance,

>> SELECT * FROM MyTable WHERE 1=1

>> will return all the rows in MyTable, and is the same as SELECT * FROM
>> MyTable

>> SELECT * FROM MyTable WHERE 1=1 AND Col1 = 'wibble'

>> is the same as SELECT * FROM MyTable WHERE Col1 = 'wibble'

>> SELECT * FROM MyTable WHERE 1=0

>> will return no rows, and you might as well not run the query at all
>> if you're expecting results.

>> Where did you get the idea that WHERE 1=1 does something special?

>> As to debugging, in VB6 I would put

>> Debug.Write adoRec.Source

>> where adoRec is an ADODB.Recordset object, to see what the string
>> being passed to SQL Server is before executing it. You should be able
>> to do something similar.

>>> I have tried Hillary's suggestion for using profiler but can't get
>>> Profiler to open the source table ("Only tables created by SQL
>>> Profiler can be opened").
>>> Since the appended query code works if I don't include the ORDER BY
>>> clause, I may just not use ordering in my SQL statement and allow
>>> the columns containing the search results to be ordered at runtime
>>> in my datagrid, giving the user the choice of how to order the
>>> results rather than forcing it in my SQL.

>> I think it working when you remove the ORDER BY is just coincidental
>> - you should be concentrating on getting VB to print to the Immediate
>> window (or whatever the debug area is called now) to show what the
>> SQL string is before you pass it to SQL Server, until you do this
>> you'll have a hard time working out what's causing it.

>> --
>> Dan

> This is my procedure:

> I run the program and set the search parameters for the proximity
> search and select a filtering option from the combobox control. I
> click the search button to begin the search.

> I have set break points in the code so that I can monitor the values in
> the Locals window.

> As I step through the code, the initial value of myQuery cannot be
> determined. Here is the printout:

> "Chars In order to evaluate an indexed property, the property must be
> qualified and the arguments must be explicitly supplied by the user."

> As I continue to step through the code and get to the p2 parameter that
> is appended to the query, the value of myQuery reads:

> "Chars Argument not specified for parameter 'index' of 'Public
> Property Chars(index As Integer) As Char'."

> I don't know if this is referring to a data type problem with the p2
> appended query, or simply an inability to determine the value of the
> filter, or something else. Any thoughts on what this means?


No idea. I haven't used VB.NET yet.

However, I'm still confused by what "1 = @p1" will achieve. In the code
you've posted so far you've include some string building, and added 2
parameters - but where is the code that sets the value of the @p1 parameter?
Where is the code that passes the myQuery string into the SQLCommand object?
Your code example is incomplete, but given that you set the value of @p2
when you create the parameter, and you've not set the value of @p1 earlier
in the code, I think I see why it's not working and why you've got that "1 =
" in there.

I've seen the use of 1=1 at the start of WHERE in some dynamic SQL building
where the coder doesn't want to have to worry about whether the SQL already
has a WHERE clause or not, they just want to append "AND ..." each time, and
it doesn't matter if an earlier piece of the SQL is left out due to program
flow. It looks like that's what you're doing here, but you've made a
fundamental error - you missed out the second 1.

a = "SELECT * FROM MyTable WHERE 1=1"
b =" AND Col1 = 'blah'"
c = " AND Col2 = 'wibble'"

if you take a+b+c , you get

SELECT * FROM MyTable WHERE 1=1 AND Col1 = 'blah' AND Col2 = 'wibble'

which is valid SQL. If b= '', then the result is

SELECT * FROM MyTable WHERE 1=1 AND Col2 = 'wibble'

which is still valid.

However, if the code requires the 1 to be included in the first string
concatenation, and it's missing, things start to go horribly wrong.

a = "SELECT * FROM MyTable WHERE 1="
b ="1 AND Col1 = 'blah'"
c = " AND Col2 = 'wibble'"

a+b+c =
SELECT * FROM MyTable WHERE 1=1 Col1 = 'blah' AND Col2 = 'wibble'

which is valid

if b = '' however, then a+b+c becomes

SELECT * FROM MyTable WHERE 1= AND Col2 = 'wibble'

and you get the syntax you're seeing.

So, go back to your code, and find where you're setting the value of the @p1
parameter. Check the value - if it's empty, or doesn't start with 1, then
your query won't work.

--
Dan
 >> Stay informed about: Ordering a filtered proximity search 

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
 >> Stay informed about: Ordering a filtered proximity search 

On Feb 5, 4:25 am, "Daniel Crichton" 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
 >> Stay informed about: Ordering a filtered proximity search