SQL Server :: Search Criteria For Multiple Columns?
Nov 18, 2010
im trying to write a query. for my ui ,i have 3 text boxes for firstname,flightno,sequencenumber.
i input search value on particular textbox it has to be searched on particular column of my db in a single stored procedure.
im using this procedure but it retrives only one column at atime. so like this i need to write three procedures.
but i have only one GO button.
ALTER PROCEDURE [dbo].[spp_searchUsers3]
@lastname nvarchar(50),
@Value varchar(50)
AS
DECLARE @SQL nvarchar(500)
SET @SQL = 'SELECT firstname,flightnumber,lastname,status FROM tbl_pregister WHERE '
+ @lastname + ' LIKE ''%' + @Value + '%'''
The gist of the problem is that we have an alumni table (one record per person) and also a couple of other tables (one to many) that have degree info and interest info. In a search screen in our app you can search for criteria that spans all three tables (there are actually more fields and tables than shown in the example below but I am trying to keep it simple).
The code below works (properly returns people without degrees for example) but still feels a little clunky or over-engineered to me. Are there easier ways to do this? NOTE: I have been through quite a few iterations/approaches to making the correct data be returned.
search form!Have no idea how to do it.Hope the example says much about how it should operate!Profile search where the result will come out in a list view.theusers should be able to filter between firstname, lastname, age, gender.right now I have this!Search page
We have 2 tables that i need to search thru..we are trying to be as flexible as possible with this search page.. a very stripped down version of how search engines are setup..
You have 1 textbox and are free to enter what ever you want..
Well in our case.. we want to setup a textbox on the page.. and pass that into our procedure and have it look thru 5 differnet columns ( id, date, name, time, number )
So if i enter say 1974 into the field, i want to go find any record that contains that in any of the 5 columns..
Is this possible without using SQL Full text indexing??
It is about ASP.NET MVC3. I have a page with at the top some search criteria and at the bottom the resulting data. The user can type some criteria and use a submit button to retieve data. In my controller, I have an ActionResult function for managing these criteria and return to the same page with a ViewModel class filled.
The problem: the user can click on a line in the resulting table for viewing a detail page. On the detail page, he can navigate to an edit page for editing data. On this page (edit data) I would like to able the user to go back to the search result page (in fact: navigate back two times). What is the best way to proceed? If I "simply" use an ActionLink (without posting data) to my search result page, it will simply display an empty result page with empty search criteria. Maybe I need to keep my search criteria in a session variable? I don't like this kind of thing...
I am working on a project for a market research company that needs to select and have it displayed to my gridview. I need a select statement that will randomly put on my gridview the following:
Recruitment Goal 21 people and out of those people 10 people have to be male, 11 people female, 9 of them white, 9 of them black, 2 of them hispanic. Is there a way I could do that? I was thinking of doing a stored procedure and doing something like
Select TOP 10 * from Canidates where sex = 'Male' order by newid() Into #tmp Select TOP 11 * from Canidates where sex = 'Female' order by newid() Into #tmp Etc... and then just selecting from the Temp Table.
I want to search record based on Name, ID, Date, Report Type. from same table with one textbox. I can do search with one column how can do it with multiple column ...
protected void Button2_Click(object sender, EventArgs e) { DateTime dt = Convert.ToDateTime(TextBox1.Text); SqlCommand cmd = new SqlCommand("SELECT * FROM Transactions where report_type = @report_type "); cmd.Connection = con;
I have this webform with few fieldsin the search criteria,I added a new dropdownlist named "Company",I populated it with the data from the database, now when I click search choosing this criteria it doent work.
public partial class List : PrintablePage { private string CNUM; private string CNAM; private string CEL; private string SD; private string SDE;........
Requirements call for the ability to save search values so that they can select them at later times to return the results without having to re-enter the values. They have the ability to search on 4 different types of data (Date, Time and Lat/Long), so they would like to capture what was entered and ONLY if they chose to save those values, it will be saved and next time they login, if they are searching, they can pick an old search and submit it again.
Im thinking that i add a checkbox next to each button, if they check it, it saves the data into a table with there values and username. Next time they login and go tothe search page, if there are any records for them in the table, i present them with a link that displays all the values stored and if they select one, it will run the search again and present them with the results. does that sound like the right way to handle this request?
I have gridview that I am filling with records from SQL database. However, I want to populate the gridview with records that match text search criteria. In other words, I want to be able to key in a word like "network" in a text box, pick up the text, read records and pick up on the records that match that criteria. I am doing fine with building the gridview but I am a bit new to this and I don't know what I do to do text search.
I have a gridview with two textboxes (first name & last name) and a linkbutton to search results inside the gridview.
I have made select parameters as well and the search works if i write in both textboxes. However, if I leave one textbox blank, i don't get any results.
[code]...
How can I get results from searching only one textbox?
I am developing the Home page of a Client. Apart from various things on this home page I have a text box and search button. Having said that, I have a database in which I have almost 12 tables with varying number of columns. Now my question is,Is there anyway to search a word typed by a user in the textbox to search it in all the tables(all columns) of the database.
I want to select data from a table where the user can search by 3 options; date, keyword, and type. The date will always be either today or a user chosen date. The keyword can be null or something the user inputs. The type will be chosen from a drop down and can be all or a specific category. I know how to search the date, but the other two create complications. I have created a stored procedure trying to figure this out:
[Code]....
Is there a way to say if the keyword is null then don't use it as a search criteria. If that is possible then type can use that same logic I suppose.
Can any one let me know about the below issue. I have a page which has three labels, say startdate,enddate and username. My issue is if the user doesnot select any of the above three then a message gets displayed as "Please select one search criteria" or if the user doesnot enter the date in correct format then i dispaly "Please enter date in mm/dd/yy" format. But what is happening is first time if the user doesnot select any then "Please select one search criteria" is getting displayed and after this message gets displayed if the user enters wrong format of date then both the messages are displayed.
I'm a novice in .NET programming. I was trying to figure a way to make a MULTI TABLE search via a stored procedure for my website. I've written a SP to select records from one table, which isn't working for some unknown reasons:
[Code]....
Whatever I search for, the datagrid is displaying all records from my table.
I have a gridview that shows, for example, a baseball team's statistics. It's a standard sports stats grid - the rows show statistics for each player and the columns show the specific stat for the each player.
Easy enough so far. But what I would then like to do is style (highlight or bold) the Max or Min (the team leader) of each stat column. For example, Player A may only lead in one or two categories, so we cannot style the entire row. If Player A just leads the team in strikeouts, I just want to style the number of strikeouts he had (THAT cell only).
What's the best way to handle this? Make SQL Server do all the work and in effect, rank EVERY stat of each player, effectively doubling the number of columns (e.g. col: AB, col: ABRank). Or do I let the rowdatabound event of the gridview handle this?
If I chose the latter, I think I would get the Max of every statistical category from the datatable before binding (e.g. store them in a local variable) then on rowdatabound, if they match the value, apply the style.
I am getting a "Data type mismatch in criteria expression" error when trying the following SELECT statement. strdt and spdate are Date types in my vb codebehind and the Contract field name is a DateTime field.
Dim nmxSQL As String = "SELECT * FROM Nymex WHERE (NymexID='" & nymID & "' And Contract>='" & strdt & "' And Contract<='" & spdate & "')"
