Advanced Search Box????
Aug 15, 2006
Hi, Using Visual Web Developer and SQL Express, (coding in Csharp) I want to add a
advanced search facility to my site. Problem is I do not know how to begin.
The website is a business directory and displays company names and services that they provide.
Here is where I am at....
So far I have made one query which allows users to search the database by "town" such as if they enter "London" in the text box, it returns the relevant records into a gridview.
Rather than have a seperate text box for each query, I want to provide an advanced search box option so users can define their search and query the database by various criterias, such as geography, sector, name, number of employees in companies etc.
I have seen compact advanced search boxes on many sites!.
Replies greatly appreciated.
prontonet
View 8 Replies
ADVERTISEMENT
Oct 9, 2006
HIi want to create a procedure, basically i'll have 4 input parametres (Title, Category, ReleaseClass and BuyPrice)title will come from a textbox, the rest from dropdownlistsif a user enters a title, selects a ReleaseClass and BuyPrice, But doesnt select a category, all categories should be returned - You know what i meanhow do i go about this - Any Ideas??Cheers!!!
View 7 Replies
View Related
Jul 25, 2006
Hi query-experts,
I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:
Table has the fields: id, fruit, color
User enters: apple pear green
The query should return all records that contain apple AND pear AND green in any of the fields.
Now I do it like this:
SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')
this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.
Question: is there an easier way to achieve this?
(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)
SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;
View 3 Replies
View Related
Jan 15, 2013
KEYIDGROUP
1 1 a
2 1 b
3 2 a
4 2 b
5 3 a
6 3 b
7 4 a
8 5 a
This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.
query should reveal answer should be missing the group b
KEYID
7 4
8 5
I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B
View 3 Replies
View Related
Apr 8, 2008
Have searched a good bit for a KB or other article but haven't been able to find one.
I have a 2003 server with SQL Server 2005 Express installed, but not the Advanced Services version. Recently installed an application that needs the Full Text search capability. Would prefer not to remove SQL Server Express and reinstall the Advanced version because I'm also using Symantec Backup Exec 11d which has its own instance on the SSExpress installation.
Is it possible to upgrade an instance to Advanced Services? If not, is it possible to upgrade the entire install?
There's got to be a how-to article somewhere on how to do this. Just can't seem to locate it at Microsoft or with Google.
Thanks for any help.
View 1 Replies
View Related
Aug 10, 2006
I have upgraded a SBS 2003 MSDE database (instance MSSQL$SHAREPOINT) to SQL Server 2005 Express Advanced Edition. This worked without a problem even when I enabled all the options for the upgrade including Full-text search.
I now want to have Full-text search on this instance of SQL 2005 with database name of STS_EVEREST_1.
I first tried to use the T-SQL command of "CREATE FULLTEXT CATALOG BBVisionCatalog AS DEFAULT;"
I now know that the original database was created under SQL 2000 and therefore I needed to use SQL 2000 commands. So I used the following script:
USE STS_EVEREST_1
EXEC sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'BBVisionCatalog', 'create';
It produced the following ERROR messages:
(1 row(s) affected)
Msg 7609, Level 17, State 2, Procedure sp_fulltext_database, Line 46
Full-Text Search is not installed, or a full-text component cannot be loaded.
I checked to see if the Microsoft Search Service was running. It was running.
Anyone know how I can fix this?
Regards
Rolf Pfotenhauer
email: rolfpf@yahoo.com.au
View 9 Replies
View Related
Mar 1, 2007
Hi. I'm trying to get full-text search working on my SQL 2005 Express with Advanced Services and am having problems. I thought that I installed it correctly, and when I look in the services running, I see it SQL Server Fulltext Search(MSSQLSERVER). Also note that I have SQL Server 2005 Standard installed on this same laptop. I don't know if that Full text Search applies to the Standard, Express or both.
What I do see is that if I connect to the database engine named <mylaptop>, I can see the version is 9.0.1399 and I can see the check box to enable full-text indexing in a particular databases properties. When I run SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'), I get a 1.
However, when I connect to the engine name <mylaptop>SQLExpress, I se the version is 9.0.3042, and I don't see the same check-box to enable full-text indexing. When I run SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'), I get a 0.
I've never tried this before so I know I'm probably missing something basic. But, I have searched quite a bit and not found my answer. So, I'm looking to you for help.
Thanks in advance.
John.
View 3 Replies
View Related
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Jul 21, 2015
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
View 4 Replies
View Related
Nov 6, 2014
I have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
View 0 Replies
View Related
Jul 24, 2007
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think?
View 1 Replies
View Related
Mar 30, 2013
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
View 14 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related
Sep 14, 2004
Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.
Example:
Say I want to find "Terminator" (1984).
I want to be able to use "Term" or "ator" as search results and still return the proper record.
Here is my Stored Procedure creation sql:
CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@searchTerm*"')
GO
--- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms.
I am trying to accomplish what is similarly done with LIKE '%term%'.
thanks, YM
View 1 Replies
View Related
Mar 25, 2015
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
View 0 Replies
View Related
Jul 6, 2007
I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;
But it works if i do
select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.
View 7 Replies
View Related
Nov 1, 2015
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999
I am trying this query:
select * from Mytable where
contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where
MyCOlumn like '123.345.%';
View 12 Replies
View Related
Mar 1, 2007
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies
View Related
Nov 22, 2007
hi there,
i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out
can anyone help?
thanks
View 6 Replies
View Related
Apr 2, 2004
for example:
SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')
would always slower than
SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'
Why? and how can it be solved? can you help me?
:)
View 6 Replies
View Related
Dec 6, 2011
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g.
megger
meg
mft megger (proper order in the database is megger mft)
mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code:
<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme") <> "") Then
RSResults__param5 = Request("searchme")
End If
[Code] ....
View 1 Replies
View Related
Jun 24, 2015
I'm experiencing an anomaly with my Full text index.
Setup : SQL Server 2005
Indexed Table:
Assets
ID - int (PK, auto increment)
Ref - varchar(50)
[code]....
I have re-built the full-text index, no change.
View 2 Replies
View Related
Jan 24, 2006
Are there any big differences between the two search techniques? It seems like they are both very similar.
SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%'
SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')
View 2 Replies
View Related
Oct 19, 2005
Here's one for one of you SQL genuises out there. I really appreciate any help that I can get. There are several steps to this.Step 1:I have a database that holds timesheet records for users. These records are by week. I need a query that will return the username of the people that have entered 0 time for all of their timesheets in the database.Step 2:I need a query that will delete and timesheets that have 0 time entered after the last timesheet with time entered. If they entered time in week 3 but have 0 time for week 4 and 5 and then are withdrawn at week 5 I need to remove timesheets for week 4 and 5. Note, they could have entered 0 for week 2 as long as week three has time entered.Step 3: - THIS IS THE MONSTER-Since the timesheets are by week, I need to find the date that the last time was entered. The timesheet has a start-date then suntime, montime, tuetime, wedtime etc. Sunday would equal the week start-date. So, if time was last entered on Friday, the date that I'm looking for is the start-date + 5. My problem is, How do I go about doing this? I've been trying to do it all in a query to no avail.I really appreciate any assistance that anyone can offer.Thank you,Still learning........-Scott
View 4 Replies
View Related
Oct 18, 2007
Hi
I'm new in Reporting Services and I have a task to solve. I wonder how to do it in Reporting Services.
For example I have few records:
ID
Year
Q1
Q2
Q3
Q4
1
2005
0
0
10
20
2
2006
20
20
35
35
3
2007
40
45
45
10 Now I create two parameters:
Q and Year
When I run this report I want to choose for example Year = 2006, and Q = 3
When I choose these values I need to calculate a field in report like this:
needed_sum = 0(Q1,year=2005) + 0(Q2,year=2005) + 10(Q3,year=2005) + 20(Q4,year=2005) + 20(Q1,year=2006) + 20(Q2,year=2006) + 35(Q3,year=2006)
I need to declare variables, like it is calculating on a section in other reports systems. Here I found only code for report where I can write some functions.
Anyway..
Did someone knows how to solve this problematic task??
Regards
Bashay
View 4 Replies
View Related
Jun 19, 2008
I want to query a database through a ASP.NET page and I want to set the page up like an advanced search page would look. My question is, how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values. For instance, if someone wants to query by TRIP id and not vehicle ID then how do I write the where claue?
View 10 Replies
View Related
Feb 7, 2006
I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName
so in my select I want to add something like ev.approved which then that brings back either Null or 0. Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'. I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.
View 14 Replies
View Related
Mar 5, 2001
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
View 1 Replies
View Related
Nov 29, 2005
Dear All,
Can you suggest a good book or set of websites which have advanced SQL tutorials for SQL server? Got the basics but looking to build on my knowledge.
...cause knowledge is power...
View 3 Replies
View Related
Mar 22, 2006
Keri writes "I am still a SQL newbe, but have learned a lot in the past year. I am not creating databases, nor maintaining our SQL Server 2000. I am a report writer and use Query Analyzer for the base of my reports. I use Actuate to develop the reports. My question is, i am interested in getting a book that covers more advanced querying. I have the book The Guru's Guide to Transact-SQL, but am intested in other books to further my growth and education in SQL. Can you suggest some books that deal with queries-advanced primarily since i really am not designing or maintaining the DB. Thanks for any information you can give.
Sincerely,
Keri Taylor"
View 4 Replies
View Related
May 11, 2006
Here's a challenge for one of those database experts out there...
I want to join two tables in a Left Join format, with the first table being the one referencing the second table. Easy enough right? Well, I also want to be able to reference this second table from 3 different fields.
For example:
My tables
#1: video
id, description ...etc... award1, award2, award3
#2: award
id, description, image, etc...
As you can see, I want to be able to award more than 1 award from the "award" table to each video in the "video" table. I then want to be able to assign a variable to each field from the result of the SQL query so I can display all the information on one page for every video with every combination of possible awards.
I also, in my SQL Query, want a WHERE clause:
WHERE video.id = $vidid (a variable from a previous page so this page only displays the video I want)
My mind says surely this must be possible, but I have no idea how to go about it. Can anyone give me any hints/help/pointers/advice on this? Even if this requires two SQL queries, I may be able to work with that, but I need to know. If I'm posting at the wrong place, or nobody here knows how to do this, then I would also really appreciate being pointed in the right direction.
Thanks in advance to anyone who even reads this and thinks over it. This is for a free online fan site http://www.lostvideo.net/ that I'm helping with some redesigns.
View 2 Replies
View Related
Jul 20, 2005
I have been able to get several basic databases to function both in playingaround and functional ones on the web but they have all been pretty simple.I am now trying to develop a database for the web using Access. What I amreally needing help with is how to actually reduce the number of record setsthat I think i am going to need. Here is what I am trying to do.I am having people sign up and create teams. So you come to the site and yousee Team 1, Team 2... Team 8. (These numbers will later be replaced by realnames given by the team captains and replaced in the database, this will bedone by the captain through the web)You pick a team you want to be on, enterthe info and you are placed on that team (in the database) I have two tablesin the database TeamRoster (everyone on all of the teams) and Teams (Listthe captains and the names of their team)So when I query the database to fill the tables I am confused as to how Ishould go about the query. As I see it I need to build a RS (Record Set) foreach team to get the name of the team for the appropriate table, thenpopulate it with the players for that team (repeating region) if there areeight teams, that means 16 RS. This does not seem right.Am I making any sense?Thanks for trying to understand all of this.Houston
View 1 Replies
View Related
