Help Me Write A Search Function Please
Jun 8, 2007
Hi all,
I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item...
For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item.
Any help would be greatly appreciated,
Thank you all,
Kenny.
View 15 Replies
ADVERTISEMENT
Jul 26, 2007
Hello all,
I am writing Stored Procedure to perform various kind of search on Database Tables.
I having requirement like Exact Phrase,Any of the word,All Words and Refine search.
I am using Full text Queries for this.
I am stuck with "All Words" search query. Can anyone please guide me how to write query to perform this kind of search. Below is the little elaboration about All words search.
Query should return all the results which contain all the words from search phrase irrespective of order.
e.g. if i search am searhing "Green House Effect" phrase then query should return all the records which having these three words in any order. like Green fds dfasd House fdsd fdsdf Effect or House fds fds Green Fd fds Effect or Effect fsda fdsa Green fd fdsa HOuse.
all the records selected by query must have all words exist in any order.
Suggest me the logic or query or any SQL Server keyword which i can use for this.
Please do the needful ASAP. Thanks.
View 1 Replies
View Related
Apr 30, 2008
Hello ALl, can anyone please tell me how to search for NULLS.
like i know one method....
select * from table
where col = 'NULL'
View 1 Replies
View Related
May 11, 2015
What I really need is to find the entries that have the word LVAD but on either side there should be a space chartacters.In other words I don't want to see the following coming from the query "SILVADENE ([PHI] SULFADIAZINE)"How can we write this query to search only for any entry that has the word " LVAD " ?
Select top 100 * FROM tbl_abc
where
AIMS_Value like '%LVAD%'
View 2 Replies
View Related
Dec 27, 2006
How can I write a function that accepts a data as a parameter and if the date is
Last Monday in May or
First Monday in September or
4th Tuesday in November
returns true otherwise returns false.
View 1 Replies
View Related
Jan 26, 2007
I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234 so that it's only matching the last part
Any Help Would be greatly appreciated Thanks
View 6 Replies
View Related
Jun 3, 2008
I have a datetime column and I set the Default value of the column to: getDate().
How can I set a nvarchar columns Default value to write string data (such as: "test")?
View 5 Replies
View Related
Jul 20, 2005
Recently, I will write a database application to get the median valueaccording to the grouped condition.maybe, just like the following.SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_tableAny suggestion?
View 2 Replies
View Related
Jul 9, 2007
hi all.
please help with my dilemma.
this is my task:
i should write a service that will run every 1 hour.
this service should check a folder.
inside this folder there are some xml files, all of them in the same format exactly.
when the service encounters with this xml file, it should open it,pull some data out of it, and update a table in the db.
NOW - I HAVE 2 OPTIONS, AND THIS IS THE DILEMMA:
should i write a clr function to do this task, or should i just write a standard c# windows service, that will open a connection to the db when it should perform update?
what are the advatages?
View 3 Replies
View Related
Apr 14, 2008
Hi Gurus,
How do I write a T-SQL function for a string before a underscore is found?
Thanks Gurus.
View 2 Replies
View Related
Mar 31, 2004
something like max,min?
help please!!!
View 5 Replies
View Related
Nov 1, 2006
I'd like to select the last unshipped order, or if none is open, the last shipped order, for each customer. I noticed that all the examples on line for OUTER APPLY involve table-valued functions, but I have read (in a book I don't have here) that a subquery can work. The following shows what I want, but it isn't valid syntax:
SELECT c.CustomerName, oa.OrderNumber, oa.Status
FROM Customers c
OUTER APPLY (
SELECT TOP 1 CustomerID, OrderNumber,
CASE WHEN ShipDate IS NULL
THEN 'due ' + CAST(DueDate AS VARCHAR)
ELSE 'shipped ' + CAST(ShipDate AS VARCHAR)
END AS Status,
CASE WHEN ShipDate IS NULL
THEN DueDate + 1000 -- Give open orders priority.
ELSE ShipDate
END AS SortOrder
ORDER BY SortOrder
) AS oa ON oa.CustomerID = c.CustomerID
View 1 Replies
View Related
Jan 19, 2007
I've got a function which inserts into a database, and has arguments for each item being inserted
A couple of the items are integer datatypes (in SQL), but they will accept nulls
When I add my parameters, it asks to explicitly use the SQL datatype (which is integer):.Add("@myParam", SqlDbType.Int).Value = myParam
In the header of the function, I assumed I could make the argument optional - Optional ByVal myParam as Integer=System.DBNull.Value
However, when the function runs, I always get an error:System.InvalidCastException was unhandled by user code Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I make them all Optional (which won't happen, but various arguments may be, at different timesand I get this error, with the last item (which is on a separate line), in red:Constant expression is required.
How can I get around this?
View 3 Replies
View Related
Jul 20, 2005
I have a SQL database with an Access front end. In the database Ihave a read only and a read write role. When a read only user opensthe database I want all the fields on the form to be locked so thatthe user will not try to change data and get an error from the server.Right now I am doing that with a table. But it's a hassle to have tomaintain a table when if I could answer the question is the currentlylogged in user in the read write role?My server is running SQL Server 2000. So I was wondering if I couldwrite a function to do this? The function would take the role beingchecked as a text parameter and return true if the currently logged inuser is in that role or false if he/she isn't.
View 1 Replies
View Related
Jun 25, 2015
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
View 8 Replies
View Related
Apr 4, 2007
I am trying to write a used car search function I want the user to be able to select a MAKE from the 1st drop down list and a MODEL from the second drop down list and click a button and for the data to be shown in a gridview, the code below is what I have so far and works i.e when user selects a make it shows all cars for that make, but now how do I take it further and add the model on there so that when the user selects i.e BMW as make and Z3 for model it only shows all BMW Z3'S
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>">
</asp:SqlDataSource>
<br />
<asp:DropDownList ID="ddmake" runat="server" DataSourceID="SqlDataSource2" DataTextField="make"
DataValueField="make" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT [make] FROM [used]"></asp:SqlDataSource>
</div>
<asp:button id="button1" runat="server" text="search"/>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
</form>
</body>
</html>
Here is the code behind the page
Protected Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
SqlDataSource1.SelectCommand = "select * from [used] where make like '%" & ddmake.Text & "%'"
SqlDataSource1.DataBind()
End Sub
View 26 Replies
View Related
Nov 28, 2007
We are in the process of developing an onlinestore like www.componentsource.com.We need to search for a product within our website.We already created pages using html.I have to take the 1st para of the product description and display. I don't want to save the description in the table how to do it?
View 1 Replies
View Related
Nov 6, 2007
Hi all I have a search function in one of my database's that when you click on the command button it window comes up that ask you for the last name. What I would like to do is alter it so that users can type in SM and get all the names that start with SM or Ma?? Like a Starts with. How can I do that
= N'[@Enter_LastName].[dbo.Revocatiions] ALike [ta%]'
CREATE FUNCTION dbo.Revocations
(@Enter_LastName nvarchar(50))
RETURNS TABLE
AS
RETURN ( SELECT [SSN], [TM#], FirstName, LastName, ReasonofRevocation, Notes, [I/R #], Date
FROM dbo.Revocations_Tbl
WHERE (LastName = @Enter_LastName))
View 14 Replies
View Related
Nov 7, 2006
Hi All,
Here's my problem:
I create titles and submit them for approval. Once approved I can edit them again and either save them or submit them for approval again.
My problem is:
When I am creating a title and saving it in the drafts and searching for the title, I am able to find it in the result page. This is before approval.
When I submit the same title for approval and it's approved and then I change the title and save it as draft and then search the new title I cant find it. But when I search with the old title it shows me the result but with the new title name.
When I check the db and search for the new title no records found.
When I check the db and search for the old title the system returns me the record with the same old name.
So when I search the old title in the db it returns the old title.
So when I search the application for the new title it returns the old title.
Help Appreciated!
Using SQL Server 2005
View 11 Replies
View Related
Feb 1, 2007
I have a table search the record. The user can search by name, id, status, address and other information.I want to make the search function more robust. For example, when the database has a record with name = "Michael Jackson", either typing any subset of the name will show this record.I would like to know if there is any easy way to implement this with VS2005 and SQL2005 Express.Thank you
View 3 Replies
View Related
Mar 6, 2007
HelloIn my website I have a textbox to search some products, but it would like to extend my search-function so that it could find special characters. For example:When I search Mexico, I would like to find México. Is there an easy way to do this?Thanks in AdvanceWesley
View 1 Replies
View Related
Sep 10, 2007
Hi, I am using the following sql for search function:
Select * From TableABC Where condition LIKE '%xyz%'
However, it does not provide good results, some results is not accurate, I would like to ask, is it any better mechanism for searching?
Please advise. Thanks~
James
View 2 Replies
View Related
Feb 11, 2007
Hi,
How to search the last occurance of one String in to another string..Using function PATINDEX() in a column of data type Text..
plz Give some solution i really need it ...
Thanks Yogesh
View 7 Replies
View Related
Jun 2, 2008
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.
For example I am using a webservice in dot net to populate a dropdown list using this sql
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText
In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText
Thanks in advance
View 4 Replies
View Related
May 5, 2008
Hi All
Is there a function to search for characters in the string simlilar to the IndexOf function is C#.net?
I need answers urgently.
View 3 Replies
View Related
May 6, 2008
Anyone have the code that would allow me to see if any of my sprocs contain references to a function? I imagine it would someting like select name from sysobjecst where charindex(whatevertextis, 'ufnName') > 0
Thanks
View 5 Replies
View Related
Jan 23, 2008
Hi all,
I have a search function in my website, which is fully functionable.
But currently, this is the issue that I am having problem with.
For example:
I have only the name 'John' in my database.
When the user search for 'John Tan', no results is shown as there was no such name in the database
But if this happens, I want 'John' to be always returned and shown, no matter what 'John' the user search - E.g "john tan", John Loh", john wee"
So, how do I go about doing this? I have done some research on how to go about doing it, what I found useful was the .Replace() function. However, I can never predict in advance what user is going to key in.
Any help is welcome as this is a rather pressing problem of mine. Thanks for all the help in advance!!!
View 21 Replies
View Related
Jan 14, 2008
Hi All!!!
I was tasked to come up with a search function and the content of the database given to me is in Chinese Characters. This would be my first time dealing with Chinese characters in the database and I need help with the following problem:
The company wants to conduct the search in such a way that, instead of having the system read the entire sentence/phrase which the user keyed in as a SINGLE string, they want the Chinese Characters to be accessed individually, so that as long as any information in the database contains any one of the characters which the user have entered, they will be retrieved and returned.
So how do I go about doing this? Does it have anything to do with Unicode? By the way, everything abt the search tool is working fine, I am just left with this dilemma of having the system recognise the entire sentence as ONE STRING, instead of conducting a search word by word or character by character.
Anyway, the following is the SQL statement of my SQL Data Source which is bound to a Gridview displaying the returned results after a search is done...1 SELECT Name, Trans, Address1, Address1T, Address2, Address2T, City, CityT, CRPLID
2 FROM CRPL
3 WHERE (Trans LIKE '%' + @Trans + '%') OR
4 (Name LIKE '%' + @Name + '%') OR
5 (Address1 LIKE '%' + @Address1 + '%') OR
6 (Address1T LIKE '%' + @Address1T + '%') OR
7 (Address2 LIKE '%' + @Address2 + '%') OR
8 (Address2T LIKE '%' + @Address2T + '%') OR
9 (City LIKE '%' + @City + '%') OR
10 (CityT LIKE '%' + @CityT + '%')
Thanks for all your help in advance!!!
View 6 Replies
View Related
Jan 10, 2008
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
View 3 Replies
View Related
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 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
Jul 12, 2006
Hi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
View 12 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
