Please excuse the crosspost from the Access forms group.
We are trying to implement a keyword search functionality in an Access 2002
project. We have set up SQL Server 2000 for full-text searching. Testing
SQL Server through Query Analyzer produces the expected results.
In a search tab on a form in an Access project, we dynamically construct a
WHERE clause for a subform. A keyword search is implemented by adding the
CONTAINS predicate to the ServerFilter string.
Once constructed, the ServerFilter is then applied to the subform and the
subform requeried to display the search's results. In testing, when we enter
criteria into the keyword field, Access crashes with no preceeding error
message. The crash occurs on the Requery statement for the subform. On the
screen, the subform fields all go blank, one gets the #Name error appearing
in it, and then the "Access has encountered . . . " message appears. An
example of an exact ServerFilter string producing the error is
"CONTAINS(*,'data')".
The documentation with Access says that the ServerFilter property is a WHERE
clause (without the WHERE). Is there an undocumented limitation that
predicates like CONTAINS cannot be used in the ServerFilter string, or am I
missing something? Any help appreciated.
can you run profiler on SQL Server to see what the sql command access is
running on SQL Server is.
if you can run this command in query analyzer, the problem is not with SQL
Server FTS, but rather with Access.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Smith" <PGCS@.online.nospam> wrote in message
news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> Please excuse the crosspost from the Access forms group.
> We are trying to implement a keyword search functionality in an Access
2002
> project. We have set up SQL Server 2000 for full-text searching.
Testing
> SQL Server through Query Analyzer produces the expected results.
> In a search tab on a form in an Access project, we dynamically construct a
> WHERE clause for a subform. A keyword search is implemented by adding the
> CONTAINS predicate to the ServerFilter string.
> Once constructed, the ServerFilter is then applied to the subform and the
> subform requeried to display the search's results. In testing, when we
enter
> criteria into the keyword field, Access crashes with no preceeding error
> message. The crash occurs on the Requery statement for the subform. On
the
> screen, the subform fields all go blank, one gets the #Name error
appearing
> in it, and then the "Access has encountered . . . " message appears. An
> example of an exact ServerFilter string producing the error is
> "CONTAINS(*,'data')".
> The documentation with Access says that the ServerFilter property is a
WHERE
> clause (without the WHERE). Is there an undocumented limitation that
> predicates like CONTAINS cannot be used in the ServerFilter string, or am
I
> missing something? Any help appreciated.
|||The culprit is definitely Access. About that there was really no question.
I am hoping someone who uses SQL Server full text searching may have
encountered this problem with Access and know how to resolve it. Access is
producing a string of SQL SELECT statements (54 to be exact) which are in
the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
With each successive call by Access, an additional set of parentheses is
added around the CONTAINS predicate.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you run profiler on SQL Server to see what the sql command access is
> running on SQL Server is.
> if you can run this command in query analyzer, the problem is not with SQL
> Server FTS, but rather with Access.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Smith" <PGCS@.online.nospam> wrote in message
> news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> 2002
> Testing
a[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> enter
> the
> appearing
> WHERE
am
> I
>
|||Paul,
To the best of my knowledge, no one has reported a similar problem via this
newsgroup and I've been replying and monitoring it since it's inception in
late 2000. I'd recommend that you open a support case with Microsoft PSS SQL
Server &/or Access support to see if they have had past customers who have
encountered this possible bug. If you do, could you post back here what the
result was?
Other than the multiple additional set of parentheses that Access is adding,
can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
'string') via Access and if so do you get the same error? Is your goal to
issue SQL FTS queries to SQL Server as the backend to an Access front-end?
Thanks,
John
"Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> The culprit is definitely Access. About that there was really no
question.
> I am hoping someone who uses SQL Server full text searching may have
> encountered this problem with Access and know how to resolve it. Access
is[vbcol=seagreen]
> producing a string of SQL SELECT statements (54 to be exact) which are in
> the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
> With each successive call by Access, an additional set of parentheses is
> added around the CONTAINS predicate.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...
SQL[vbcol=seagreen]
construct[vbcol=seagreen]
> a
> the
> the
we[vbcol=seagreen]
error[vbcol=seagreen]
On[vbcol=seagreen]
An
> am
>
|||For anyone else who might encounter this problem: we seem to have resolved
the problem here. One of our developers noticed that we had gotten angled
quotation mark characters rather than the typical straight apostrophe as a
string delimiter. We aren't quite sure how this happened, but once this was
corrected, things seemed to start working correctly.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23Ic$xQxiEHA.2812@.tk2msftngp13.phx.gbl...
> Paul,
> To the best of my knowledge, no one has reported a similar problem via
this
> newsgroup and I've been replying and monitoring it since it's inception in
> late 2000. I'd recommend that you open a support case with Microsoft PSS
SQL
> Server &/or Access support to see if they have had past customers who have
> encountered this possible bug. If you do, could you post back here what
the
> result was?
> Other than the multiple additional set of parentheses that Access is
adding,[vbcol=seagreen]
> can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
> 'string') via Access and if so do you get the same error? Is your goal to
> issue SQL FTS queries to SQL Server as the backend to an Access front-end?
> Thanks,
> John
>
> "Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
> news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> question.
> is
in[vbcol=seagreen]
is[vbcol=seagreen]
> SQL
Access[vbcol=seagreen]
> construct
adding[vbcol=seagreen]
and[vbcol=seagreen]
> we
> error
> On
> An
a[vbcol=seagreen]
that[vbcol=seagreen]
or
>
Showing posts with label keyword. Show all posts
Showing posts with label keyword. Show all posts
Sunday, March 25, 2012
Sunday, February 19, 2012
about search query
I would like to write query which can
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea
SQL Queries are case insensitive.
No idea. I am unable to understand your question.
|||
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?
|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||
Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea
1. ignore whether the search keyword is Upper case or lower Case
SQL Queries are case insensitive.
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea
No idea. I am unable to understand your question.
|||
sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that
(a) (b)
1.CASW Casw
2.compare comparing
1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?
|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||
ndinakar wrote:
you should get both records without having to worry about case.
Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||
DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))
INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')
INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')
SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------
(1 row(s) affected)
(1 row(s) affected)
colA colB
---- ------------------------------
Tom FOOTBALL,PIANO
Sam football,piano,volleyball
(2 row(s) affected)
|||Thanks for the followup ndinkar. I always that my query didnt worked.Thursday, February 16, 2012
about messges in microsoft sql express
:eek: :eek: :eek: :eek:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'contains'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
i am getting error messages like this.
I want to know what this message really
mean i.e list of messages corresponding to numbers......The SQL engine uses only the error numbers internally. Those are what the engine know about, and are how it complains about "bad code" that it receives from the SQL client. The error message displayed below the Msg line contains a description of the error in the current locale (in this case, essentially a human language like English, Spanish, French, etc).
The process of getting the text description is simply a table lookup, there's no "magic" associated with it. The table of error messages changes constantly, usually with every service pack.
-PatP|||they are stored in sysmessages:
select * from sysmessages where error in (102,156)
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'contains'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.
i am getting error messages like this.
I want to know what this message really
mean i.e list of messages corresponding to numbers......The SQL engine uses only the error numbers internally. Those are what the engine know about, and are how it complains about "bad code" that it receives from the SQL client. The error message displayed below the Msg line contains a description of the error in the current locale (in this case, essentially a human language like English, Spanish, French, etc).
The process of getting the text description is simply a table lookup, there's no "magic" associated with it. The table of error messages changes constantly, usually with every service pack.
-PatP|||they are stored in sysmessages:
select * from sysmessages where error in (102,156)
Subscribe to:
Posts (Atom)