I got a SQL DB with three tables, where two of the collums of the main table gets its data from two other tables, ok? Now, on one page, called "search" the user should be able to search in the DB, and on another page the user should be able to register some keywords, my question is how can the user register several keywords in the same textbox, using perhaps a ; as a separator between the words? I hope you understand what I mean..
How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN
I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Hi, I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.
here is what i have so far but it always returns the same thing. any ideas?
$searchArray=explode(" ",$search); $query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";
foreach($searchArray as $key){ $query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";
}
$query = substr($query,0,strlen($query)-3); $query = $query . ") order by keywords";
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50)) CREATE TABLE [Company] (Id int, Name varchar(50)) CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee] AS SELECT * FROM [Employee] INNER JOIN [EmployeeCompany] ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate] ON [dbo].[dvEmployee] INSTEAD OF UPDATE AS BEGIN UPDATE EmployeeCompany SET Status = INSERTED.Status FROM EmployeeCompany, INSERTED WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?
Hi! I dont know if i will explain this correctly, but my problem is with reporting service. I'm supposed to Sum value in one textbox and than that sum use it in sum in other textbox.
Something like this: Sum(Fields!Abc.Value/(Fields!dfg.Value+Sum(Fields!abc.Value)),"matrix1_RowGroup1")*100 I get error msg 'The Value expression for the textbox 'textbox49' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.'
Something like this wont work either Sum(Fields!abc.Value/ReportItems("textbox56").Value)*100 Error The Value expression for the textbox 'textbox55' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
So, pls help if you know how to reference textbox in other in body of report. Thx.
I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search ( @eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3) ) AS SELECT eName, ID, cID, eCC FROM iTrns WHERE (ID LIKE '%' + @ID + '%') and (eName LIKE '%' + @eName + '%') and (cID LIKE '%' + @cID + '%') and (eCC = @eCC) ======== My code behind========protected void Button1_Click(object sender, EventArgs e) { GridView1.DataSource = GetTable(); GridView1.DataBind(); }public DataSet GetTable() {SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString); SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon); cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text); cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text); cmd.Parameters.AddWithValue("@eCC", TextBox4.Text); Adptr.SelectCommand = cmd; DataSet ds = new DataSet();return ds; SqlCon.Close(); } ======== Sometimes it retrieves the whole data !! and sometimes retrieve nothing !! Any hint ?? Thank you
CREATE TABLE #KeyWord ( [QueryId] int IDENTITY (1, 1) NOT NULL , [QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND) [SKey] nvarchar (200) NULL ) ON [PRIMARY]
--target table:
CREATE TABLE Test ( [PId] int IDENTITY (1, 1) NOT NULL , [PTitle] nvarchar (200) NULL ) ON [PRIMARY]
================ I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
I am looking for a store procedure which validates certain keywords like delete,truncate,update,insert etc. and restricts them to be used by users in all of my store procs which takes strings as inputs.
Hi, I'm trying to move an asp site from an Access DB to SQL Server. The site features a search system which allows multiple keywords to be used in up to three fields.
The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701):
"So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."
The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?
If it isn't possible, how can I go about this?
Cheers all. __________________________________________________ ___ Code: ASP: item=request(item) '****item = Replace(item, " ","%' AND item like '%")**** - needs attention rs.Open "Exec getlist "& item &"" objConn, 3
SP: CREATE PROCEDURE getlist @item nvarchar(255) SELECT item FROM publications WHERE item LIKE '%' + @item + '%' GO
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
SELECT acct.USERNAME, SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits], MAX(trans.ENDPERIOD) AS [Billed Through], acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLAN FROM TRANS trans, ACCTS acct, BILLING bill WHERE trans.ACCTNUM = acct.ACCTNUM and bill.ACCTNUM = acct.ACCTNUM and bill.ACCTNUM = trans.ACCTNUM AND acct.CLOSED = 0 AND acct.SUSPENDED = 0 GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLAN HAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0 ORDER BY [Billed Through] DESC
SELECT acct.USERNAME, SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits], MAX(trans.ENDPERIOD) AS [Billed Through], acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANFROM TRANS trans, ACCTS acct, BILLING billWHERE trans.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = trans.ACCTNUM AND acct.CLOSED = 0 AND acct.SUSPENDED = 0GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANHAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0ORDER BY [Billed Through] DESC
Incorrect syntax near the keyword 'PLAN'.
If i take out SELECT & GROUP BY acct.plan, it works fine.
I've googled a bit and found 'EXPLAIN PLAN' command, I assume it's parsing the 'PLAN' as a command and screwing stuff up. I don't get why it'd take it for a command instead of a column. How does one select a keyword as a column name? Brackets & single quotes didn't do the trick.
Hi, I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried. An example: User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%') As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures? Kind regards, ThaYoung1!
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below. However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help? Thanks, Andy.private bool searchProducts(string keywords) { ....... command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' "; command.Parameters.Add("@keywords", SqlDbType.VarChar, 100); command.Parameters["@keywords"].Value = keywords; ............. }
Hi All, i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view. For example ,just like functionality of sp_search_code 'Keyword'. like this i want to search in views . or else please let me know sp_search_code 'Keyword'. was used for views also. Thanks and Regards, G.JaganMohanrao.
I am greener in using SQL Server 7.0. I created a stored procedure named usp_Test (please see the following codes for reference). It worked fine when passed through a single keyword into it (for example usp_Test 'Satellite'), but it did not return the query results what I wanted when passed through multiple keywords into it (for example usp_Test 'Satellite Remote'). In fact, it returned nothing but the column names specified in SELECT statement. Below is the sample of procedure how it looks like. Where am I wrong? Any idea?
CREATE PROCEDURE usp_Test @strKeywords varchar(50) AS
SET @DelimiterPos = 0 SET @Delimiter = CHAR(32) SET @strWHERE ="" SET @strKeywords = RTRIM(LTRIM(@strKeywords))
SET @DelimiterPos = CHARINDEX(@Delimiter,@strKeywords,1)
IF @DelimiterPos > 0 BEGIN WHILE (@DelimiterPos > 0) BEGIN IF LEN(@strWHERE) > 0 BEGIN SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + LEFT(@strKeywords,@DelimiterPos - 1)+ "%" + CHAR(13) END ELSE BEGIN SET @strWHERE = @strWHERE + "%" + LEFT(@strKeywords,@DelimiterPos - 1) + "%" + CHAR(13) END SET @strKeywords = LTRIM(RIGHT(@strKeywords, LEN(@strKeywords) - @DelimiterPos)) SET @DelimiterPos = CHARINDEX(@Delimiter, @strKeywords, 1) IF @DelimiterPos > 0 BEGIN CONTINUE END ELSE IF LEN(@strKeywords) > 0 BEGIN SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + @strKeywords + "%" + CHAR(13) BREAK END END END ELSE BEGIN SET @strWHERE = @strWHERE + "%" + @strKeywords + "%" END PRINT 'WHERE tblClips.Title Like ' + @strWHERE SELECT tblClips.Title,tblTapes.ClassificationNo,tblTapes. Inventory FROM tblClips INNER JOIN (tblClipTape INNER JOIN tblTapes ON tblClipTape.fkTapeID = tblTapes.TapeID) ON tblClips.ClipID = tblClipTape.fkClipID WHERE tblClips.Title LIKE @strWHERE
The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.
I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?
I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
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?
This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
I have several textbox on my report and I would like to remove line if blank.
texbox1 textbox2 <--- use Expression in order to display it. textbox3
------------------------------------------------------------------------------ Current Display :
sergio myers
<-------- insert a blank line donna summer
_____________________________________________ The Desire Result that I would like to have : sergio myers donna summer _____________________________________________ I would like to remove the middle line if blank so it can be display like this : sergio myers donna summer
Does anybody knows how to accomplish this in Reporting Services. Foxpro uses "Remove line if blank" and 'Print only when expression is true'
Hello How do u update the datafield of a row using buttonClick in vb? I have a details view whereby it will display the username and timeleft once log in. currently the datafield, timeleft on one user in the database is null. I'm using sqldatasource. Once log in, it will direct to this page where the user enters a code on the textbox and click the enter button. This will cause an update on the user's timeleft. But the code must not be entered to the database. For eg. If code = 1111 then 'update timeleft = 60 The timeleft is in minutes. 60 will be stored as double in the user's row in the database. How do i do this? Please help asap. Thanks! :)
