I have a search page, containing 3 drop down lists. Theses are used to match data in 3 seperate collumns of a table.I can get the search to work when all boxes are completed, but I need to be able to leave some blank.A similar question was asked in http://forums.asp.net/626941/ShowPost.aspxAnd a very good solution was give: http://www.sqlteam.com/item.asp?ItemID=2077But I don't seem to be able to get any of these examples working!If I use the SET @SQL method, I get an error message saying that my input parameter has not been declared, when it has.When I use the COALESCE method, there is no data returned.(I am passing the data drop down list data fin the search page through the query string to a results page).Does anyone know of any other examples, or sample coding??Thanks...
I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like, select * from A where id in (@abc)
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is: I don't want only the records with columns that contains both searchterms. I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns. Jobdescr_________________________|Jobtext Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1 INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Hi, I am trying to build search engin with 11 parameters in 4 different tables in the database. For example: In search.aspx I have 11 textboxes namely nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc. And in the result.aspx page I have gridview which post data from the database if the search match. I wrote this stored procedure. P.S please ignore the syntax. @name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote 1 @name var(30), 2 3 @nationality (30), 4 5 @phone int, 6 7 etc 8 9 as 10 11 12 13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c 14 15 WHERE 16 17 name LIKE '%' ISNULL(@name, '') '%' 18 19 OR nationality LIKE '%' ISNULL(@nationality,'') '%' 20 21 OR phone LIKE '%' ISNULL(@phone,'') '%' 22 23 etc 24 25
Also the error still exist. What is the best way to search for multiple parameters in multiple tables ?
I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.
Can someone clue me in to what the commandtext of my query should look like?
I've done some simple sql's for searching a field using Like,But this one is different. I am adding a param named @searchText I would like to bring back all records in all the fields listedbelow that has that string in the field... WHERE a.manufacturer = b.manufacturerIDAND a.location = c.locationIDAND a.Status = d.statusIDAND a.EquipmentType = e.IDAND a.calLab = f.ID AND a.testTechnology = g.id AND (c.locationID = @location OR @location = 0) So, each line/field above I want to search for the string and includein the dataset. Anyone can point me in the right direction? Thanks, Zath
I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?
I want to search through 3 tables (TableB, TableC, TableD) to find which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff
Select hdr_ctl_nbr, count(*) from TableA c where c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB) group by c.hdr_ctl_nbr
Hi, I want to search multiple words that is present in the database, e.g if i am putting "porperty in south delhi" but south word is in the data base, but result does not comes. if I use like operator like this select * from MASTERSEARCH where companyname like '" + txt_Company.Text + "%' or dealsin like '%" + txt_keywords + "%'";Here I put only south word, then result comes. but I want search criteria should be any word that is present in the database.I am using this with my web site. http://www.b2bindialinks.com
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
I am building job search engine. I got one listbox and one dropdownlist of place and region. If I select East Anglia from places the dropdownlist of regions it will display Any region, Cambridgeshire, Norfolk, Suffolk and if I Select Greater London it will display Any region, London, London-Central, London-East etc.. etc..I am searching for jobs with respect to regions, Is it possible that if i select say East Anglia in the listbox and Any region in the dropdownlist and i can search with all the regions in that section selected.At the moment i'm using this stored procedure which doesn't include the above idea. Is there any way to modify this Thanks in AdvanceSELECT DISTINCT J.*FROM JobDetails AS J JOIN(SELECT DISTINCT J1.JobIDFROM JobDetails AS J1inner join dbo.Split1(@list, ' ') AS SON J1.JobPosition LIKE '%' + S.Data + '%'AND (@loc='0' OR J1.Location LIKE '%'+@loc+'%')AND (@region='0' OR J1.Location LIKE '%'+@region+'%')AND (@type='0' OR J1.JobType=@type)AND (@a IS NULL OR (J1.JobDate BETWEEN DATEADD(dd,-@a-1,getdate()) AND GETDATE()))) J1 ON J1.JobID = J.JobIDsavvy
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
where content like '%EditLiveJava%' or content like '% Sys__%' ESCAPE '_' or content like '%<div class="row"/>%' or content like '%<a href="" title=""%' or content like '%cmsprod%' or content like '%Error processing inline link%' or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
I have been working on a database and I have imported a number of lists into multiple tables.
I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).
********************alt.php.sql,compdatabases.ms-sqlservermicrosoft.public.sqlserver.programming***********************************Why doesn't this work:SELECT *FROM 'Events'WHERE dayofweekREGEXP 'monday' OR description REGEXP 'monday'When this does work:SELECT *FROM `Events`WHERE dayofweekREGEXP 'monday'
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+ N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+ N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%'''
EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren
Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.
I have been informed that all my keyword search solutions are susceptible to SQL injection attacks. Does anyone have links discussing basic ' multiple ' keyword search solutions? I would think this is a very common routine (perhaps so much so than only newbies like myself do not know it). I have read the posts about escaping ', doing replace " ' ", " '' ", using parameters and yet every multiple keyword solution I come up with is said to be injection prone. Example: visitor enters: Tom's antiquesinto a TextBox control and the C# code behind securely generates the below call to the database. SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'Tom's' + '%' AND L_Kwords LIKE '%' + 'antiques' + '%' AND L_Display = 1 RETURN I understand that concantenting string parts using an array and then passing the sewn together string to a stored procedure exposes it to injection. I hope that my single keyword routine below is secure, if it is not then I am not understanding how parameterized SP are supposed to be constructed to protect against injection.string CompanyName;CompanyName = TextBox1.Text;PROCEDURE CoNameSearch @CompanyName varchar(100)AS SELECT DISTINCT L_Name, L_Phone, L_City, L_State, L_Zip, L_Enabled, L_Display FROM tblLinksWHERE (L_Name LIKE @CompanyName + '%') AND L_Enabled = 1 AND L_Display = 1 ORDER BY L_NameRETURN
My scenario is I have a web form with a textbox and a button.Once I enter a string and hit submit button, my stored procedure will have to return the result set.So if my search string is "text book title", then I have to execute the query like :select * from tab1 where col1 like '%text%" or col1 like '%book%" or col1 like '%title%"The problem here is I will never know how many words will be entered to search. So I have to make the statement dynamic.How can I do this in a stored procedure? Any help will be appreciated.Thanks.
Hello,We are developing an application against an MS SQL Server 2000 databasewhich requires that we implement full-text searching across columns inmultiple tables. The research that we have conducted seems to indicatethat this is not directly possible within SQL Server 2000. And we canfind no way to implement this as the catalogs that are generated aretable specific.As a work-around, we are planning to create a secondary table usedstrictly for searching which is going to denormalize and combine thesearchable data into one catalog. We just want to confirm that this isthe recommended approach or if there is an alternate solution anyonehas used. Please note that upgrading to SQL Server 2005 is not anoption.Thanks.John FlemingJoin Bytes!
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
How can I run through an array of ID's and delete the fields associated to it in a single table? I have a lot of bad data that needs to be removed. BTW, I'm using the SQL Server Management Studio to do this.
I'm currently doing by hand by using the following.
USE database; DECLARE @id integer SET @id = XXXXX DELETE FROM table WHERE id = @id;
I would like to use the same code but with a slight variation to support an array.
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection Dim Query As String Dim SqlComm As SqlCommand Dim myDataReader As SqlDataReader
' Define connection object Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory" ' Define command object SqlComm = New SqlCommand(Query, Conn)
' Iterate through records and add to array list While myDataReader.Read() IDList.Add(myDataReader("productMainCategory")) End While
' Close DataReader and connection objects myDataReader.Close() myDataReader = Nothing Conn.Close() Conn = Nothing
' If page has not been posted back, retrieve first page of records If Not Page.IsPostBack Then Paging() End If
End Sub The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
I guess a pretty basic sql question here. I am using tableadapters. And some of the queries have multiple parameters. But often a parameter is at a default or null - wherein I'd like to be able to pass it in and have it not filter at all - ie., return everything. Simple example: Select * from ReportsWhere ReportID = @ReportID If I have a dropdownlist of reports where the values are ReportIDs - but the topmost unselected value is say "", then the results will be generated by Select * from ReportsWhere ReportID = '' Whis not what I want. What I would want is simply Select * from Reports or Select * from ReportsWhere ReportID = ReportID So how can one setup say an ObjectDataSource or somehow handle this where I don't need to use dynamic sql to expand/contract my where clause? Really need hlpe on this and hope I've made my question reasonably clear. Thanks.
Hi all In my application i have to upload a excel file.After that it will retrive tha data from database depending upon the data in excel sheet.So now the problem is that i am using a stored procedure for this and in that procedurei have taken output parameter to catch any type of error.For example suppose in my excel sheet i have 3 columns.let it Group Code,Employee No and Employee Code.So incase it will not find the employee number so using the output parameter i am throwing the error message.So here we are getting two return types.one is the selected data and another is the error message.So how to handle these two thing in asp.net.Usong a dataset i can only retrive the selected data.But how to get the errror message as well as the data.So please help me.
