Search Engine based on the user input into textbox, dropdownlist, and gridview with checkboxlist

Hello all,

I have two tables with sample data as following:

tblStudent
ID  | School | Name | Age | Year
====================================
1   | A      | Jane |  5  | 2006
2   | B      | Jo   |  4  | 2008
3   | C      | Al   |  1  | 2007
4   | A      | Ron  |  5  | 2008
5   | B      | Tom  |  5  | 2006
6   | A      | Tina |  2  | 2008
7   | C      | Alvin|  3  | 2007
8   | D      | Bill |  5  | 2008

tblGoodSchool 
SchoolID | SchoolName
======================
1        |  A
2        |  B
3        |  C
        

My AJAX ASP.NET with front end layout consists of a textbox, dropdownlist and gridview with checkboxlist as detailed following:

1. txtAge for user input age information
2. ddlYear for user select a year from ddl
3. gvSchool that dynamic populate list of SchoolNames via chklSchool from above tblGoodSchool table (all checkboxs mark true as default)

I would like to add a button for search data in tblStudent based on the user inputs into textbox, dropdownlist and select options in checkboxlist.
I need to build SQLQuery base on Where clause in order to get the desired data output.

In VB.NET code behind:

Dim SqlQuery As String
Dim SqlSelect As String
Dim SqlAnd As String
Dim SqlWhere As string

SqlSelect = ""
SqlAnd = " AND "
SqlWhere = ""

SqlSelect = SqlSelect & "SELECT * FROM tblStudent"

If txtAge.Text <> "" Then
  SqlWhere = SqlWhere & SqlAnd & "Age" & " >= " & txtAge.Text
End If

If ddlYear.text <> "" Then
  SqlWhere = SqlWhere & SqlAnd & "Year" & " >= " & ddlYear.Text
End If

 ...

???  

I am not sure how to add SqlWhere to query the data based on checkboxlist options in gridview. Any help to solve my issue is appreciated. Thanks in advance.

0
avt2k7
4/25/2009 5:55:44 AM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

8 Replies
706 Views

Similar Articles

[PageSpeed] 36
Get it on Google Play
Get it on Apple App Store

You need to use Optional Parameters: http://steve.emxsoftware.com/Optional+Parameters+in+SQL+Server+Search+Queries.  You should not concatenate the contents of fields that users have filled in like that.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
4/25/2009 6:45:30 AM

Hi, you can try this

where studentID_field = txtstudentID.text and ddl1_field = ddl1.selecteditem.text and chk1_field = chk1_value


Hope This One Can Help.
Thanks

To Be Happy Is To Be YourSelf

Please remember to mark my reply as answers if it's the answer to your question.
0
Sayre
4/25/2009 6:52:52 AM

You can try this logic.

String SchoolNames = ""

For Idx as Integer = 0 to gvSchool.Rows.Count-1

If (gvSchool.Rows(Idx).Cells(2).Value = True)

SchoolNames = "'" + SchoolNames +  "'" + "," (SchoolName enclosed in a Single quote)

End If

Next

SchoolNames = SchoolNames.SubString(0, SchoolNames.Length-1)

SqlWhere = SqlWhere & SqlAnd & "School IN (SchoolNames)"

- I have not checked for the correctness of the syntax, but feel this logic should work.

Happy Coding!

0
Sangeeth
4/25/2009 6:55:59 AM

Hi,

Thank for your suggestion. I knew how to create SP to do it before. Unfortunately, I don't have any permission to touch on SQL Server database for creating SP with my current job.

0
avt2k7
4/25/2009 7:16:41 AM

You son't have to create a stored proc to use parameters.  You could use SqlParameters instead: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.aspx

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
4/25/2009 5:19:53 PM

 Hello Mike,

Thank for your response. I have read your example in your link, but still don't know how to apply with my problem? Could you give me some sample code to work on my checkboxlist in the gridview to use parameter. Thanks

0
avt2k7
4/26/2009 4:03:06 AM

 Hello Sangeeth,

 Thanks for your code. Your logic is what I expect to have. I have not tested on it yet. All checkbox mark  default option. I did not see your code that relate to checkbox at all. Here is my gridview with checkbox in ASP.NET front end code as following:

=============================================================================

<asp:GridView ID="gvSelections" runat="server"
                            style="color:black;font-family:Arial Narrow;font-size:small;"
                            AutoGenerateColumns="False" Width="513px" DataKeyNames="SchID"                  
                            OnSelectedIndexChanging="gvSelections_SelectedIndexChanging">
      <Columns>
          <asp:TemplateField>
               <HeaderTemplate>
                   <asp:CheckBox ID="cbSelectAll" runat="server" Text="Select / Clear All" checked="true"     AutoPostback="true" OnCheckedChanged="cbSelectAll_CheckedChanged"  />
               </HeaderTemplate>
               <ItemTemplate>
                   <asp:CheckBox ID="ChkbSel" Checked="true" runat="server" visible="true" />
               </ItemTemplate>
               <HeaderStyle HorizontalAlign="Left" />
               <ItemStyle HorizontalAlign="Left" />
          </asp:TemplateField>
          <asp:BoundField DataField="SchoolName" HeaderText="School Name">
            <HeaderStyle HorizontalAlign="Left" />
            </asp:BoundField>
        </Columns>
 </asp:GridView> 

============================================================================ 

Any further help how to solve my issue in code behind??? Thanks in advance.

0
avt2k7
4/26/2009 4:16:06 AM

Hi,

Try this code. 

        string selectedSchools = "";

        for (int idx = 0; idx < gvSelections.Rows.Count; idx++)
        {
            CheckBox chkSelect = (CheckBox)gvSelections.Rows[idx].Cells[0].FindControl("ChkbSel");

            if (chkSelect.Checked)
            {
                selectedSchools = selectedSchools + "'" + gvSelections.Rows[idx].Cells[1].Text + "',";
            }
        }

        selectedSchools = selectedSchools.Substring(0, selectedSchools.Length - 1);

 Here, I've assumed that the SchoolId is present in the second column (Cells[1] - Change it according to ur code). Finally the selectedSchools will contain a comma separated values of all schools selected, so that u can check it up in ur Query like,

SqlWhere + SqlAnd + "SchoolID IN (" + selectedSchools + ")"

Happy Coding!

 

0
Sangeeth
4/27/2009 6:53:12 AM
Reply:

Similar Artilces:

Gridview Search: use DropDownList and TextBox to search / filter gridview?
Let's say I have a gridview with three columns: First Name, Last Name, ID Number.  Here's an example of what I want to do:I want to display all the records in the gridview to start.  There would be a dropdownlist that lets the user select whether to search by first name, last name, or ID number and a text box that lets them type in what they want to search for (and a button to start the search).  After searching, the gridview would only display the records that matched the criteria.The gridview needs to be editable, sortable, and pageable even after searching/filtering...

Controlling User Input into a TextBox control
I want to control user input to a textbox control with a string mask. e.g. "99.9.99" where the 9s represent any numeric and the dots are automatically provided from the mask. Is it possible in an ASP.net page - it is standard in dBase type languages! Would it require Javascript? Where could I find help on this? Try using a RegularExpression Validator Control, which is part of the Asp.Net toolbox. Check out RegExLib.com for patterns and testing.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-...

Gridview Search using dropdownlist, textbox, search button.
 I can only search in gridview using a textbox and a search button. What i want to do is. I have a dropdownlist where the items are the columns of the employee table, a textbox where a user input a value. And when the user click the search button it will be displayed on the gridview. something like this. Select * from "Employee" where "dropdownlist_sample" is LIKE "textbox_sample" Here are the columns of my Employee Table EmployeeID, LastName, FirstName, Address, ContactNo, Birthdate, DateHired,  and Empoyee Status. and bt...

Search query using search term from user input into TextBox
Hi everyone, thank you for the help you provide on this forum, I've learned a lot so far.Right now I'm making a simple Access-Visual Basic 2005 .NET database. I want to make a Search Query that will search the Database for text similar to what the user inputs into TextBox1.Text on the form where the datagrid is displayed.I've created a query that works if:SELECT...FROM...WHERE mycolumn = 'oxygen'for example. It will retrieve and display all matches for oxygen, so the query itself works. But it does not work with the variations I have tried on:SELECT...FROM...WHERE mycol...

Search query using search term from user input into TextBox
Hi everyone, thank you for the help you provide on this forum, I've learned a lot so far.Right now I'm making a simple Access-Visual Basic 2005 .NET database. I want to make a Search Query that will search the Database for text similar to what the user inputs into TextBox1.Text on the form where the datagrid is displayed.I've created a query that works if:SELECT...FROM...WHERE mycolumn = 'oxygen'for example. It will retrieve and display all matches for oxygen, so the query itself works. But it does not work with the variations I have tried on:SELECT...FROM...WHERE mycol...

How Can I Search Using DropDownList, TextBox, Gridview and a Search Button?
I have a DropDownList with two items:  "Customer" and "Vendor". I like user to select, for example, "Customer", from the DropDownList then in a TextBox, enter the customer name. Clicking the Search button, would return the appropriate data to the GridView-- depending on what has been selected in the DropDownList and what was entered in the TextBox. Is this possible?  Your help would be very much appreciated. Jim   I am trying the same thing, it’s surprising you haven’t received any reply’s by now. Seems like a common task. I am going to try a cross p...

How to rebuild input controls without negating user input in the RowUpdating event of a GridView
I've scoured the web and banged my head against a wall over this one for days.I have a GridView in an ASP.NET 2.0 application whose datasource is an enumerable collection of objects that I cannot know until runtime. Therefore I cannot use the declarative (aspx) representation of the GridView to write out template fields. I instead create them manually in the code behind. For example (this is oversimplified), if a given property on the object a row is binding to is a boolean, I may want the EditItemTemplate to display a DropDownList with two items, 'True' and 'False' the a...

How to search a gridview with sqldatasource dynamically using multiple search terms from a single textbox control
Could someone please let me know how I can let people search a gridview (with paging and sorting) using a single textbox that accepts multiple search terms? I also want to be able to edit and delete a row from the filtered search results. I want to be able to apply the search criteria to each column in my gridview. For example, a user might enter a search string: "jack jones" Say I have a gridview that has three columns: id, firstname, lastname with the following data: 1 fred flintstone2 wilma flintstone3 jack beanstalk4 jones jack5 jack jones6 hungry jack I want the gridview ...

Search based on user input
I have a search page that allows users to type/select values contined within the entry they're looking for. My SELECT statement returns columns in a table that get compared to the user input. So if someone selected Status (Open) then all of the 'Open' Request entries should populate the search page. How do I phrase the SELECT statement to compare values if the user gives them, but ignore the fields where no data was input when it's searching? So a search where no values were entered would return every Request Entry instead of an error; no entry. Thanks! Please search thes...

using javascript getting value from textbox when edit button is clicked and both controls are present in gridview control
Hi All using javascript I want to get value  from textbox when edit button is clicked and both controls that is texbox and button are present in gridview control .Alok Hi aloksinha83, Please see this post: http://forums.asp.net/t/1069245.aspx Thanks,Qin Dian TangMicrosoft Online Community SupportPlease remember to mark the replies as answers if they help and unmark them if they provide no help....

Search Engine Friendly User Controls?
Hi Just starting to dip my feet into the world of ASP.NET. Do pages ending with the extension .aspx get picked up by search engines such as Google? If I use a 'User Control' to put together a navigation system for all pages, will search engines actually read these as text on the page? Any advise would be appreciated. K kefi2927-------------------------------------------Think big thoughts but, relish small pleasures. Absolutely! Just search for "aspx" on Google and you'll get millions of results with .aspx pages. As far as UserControls go, you also have abso...

Populating Textbox based on user input
I have a form with 3 text boxes on, username.text, password.text & Reference.Text, these text boxes relate to information in a table in an Access Database, with the same name.I want to be able to enter a username and password into the first two text boxes, then when a button is clicked, retrieve the refernce for that record (assuming the username and password match a record in the database)I am fine with the query for linking the username and password text boxes to data in database, the problem i have is populating the Reference.text based on the input.Thanks In advanceJohn Well, ...

Searching a database based on user input
Hi, I am creating a web app that takes user input to search a database.  It is like a library search.  I want to have a page where the user types in either the title, author, keyword and so on.  Then I want to take that information and search the database for records matching their parameters and output them on a new page.  I am really confused on how to get started, if some could give me some direction I would really appreciate it.  Thanks, Kristin You will need to read articles about gridviews or datagrids, and using Like comparison in sql. To get the most pe...

filtered search with textbox and dropdownlist
 hi,just wondering how i would go about making a search facility?1. textbox1 = search term2. dropdownlist = column name (firstname, surname, etc) 3. button = submit4. display in gridview my sql isnt that good and im totally new to asp.net. any help would be great   in above design.. user would be able to search on just ONE field only.. as you can select only one field from dropdownlist and can write search term for that field in textbox1...what i would prefer is.. make a search page with all possible search  parameters selection with DropDownList / ListBo...

Web resources about - Search Engine based on the user input into textbox, dropdownlist, and gridview with checkboxlist - asp.net.presentation-controls

Tag Synonyms - Stack Overflow
Q&A for professional and enthusiast programmers

Accidental entities - what about the UI?
This post is a follow-up to my previous blog post " Accidental entities - you don't need that identity ". In that post, we followed a consultant ...

Flash Builder Plug-in for SAP Netweaver Gateway - Adobe Labs
Search Navigation Labs Home Wiki Home Technologies Alchemy Blueprint Camera Profiles and DNG Profile Editor Cirrus ColdFusion 10 DNG Codec Fireworks ...

Air Astana - Join Now
Thank you for considering membership of Nomad Club – Kazakhstan’s first Frequent Flyer Program. You could soon be on your way to earning free ...

Languages -> C# Articles, Tutorials, Examples: ASP Alliance
.NET,ASP,ASP.NET,SQL,XML,HTML,ADO,ADO.NET,JavaScript,JScript,CSharp,VBScript and VB tutorials from AspAlliance.

Blog - Avisra
Avisra Web Development Home Blog Services Contact The Blog Latest Module Builder: How can we make it better? Sitefinity by Chris on July 2, 2013 ...

Online Booking Tool Support
This Online Booking Tool Support page might help you as you navigate through the process of booking travel through your OBT provided by CWT. ...

@Html and beyond
... ); } return string .Format( “{0}{1}” , me.Member.Name, id); } The Html building methods are quite simple. Building a dropdownlist for a ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Unit Testing DateTime.Now Without Isolation
public virtual void SeedWithYearsSince(DropDownList list, int year){ for (int index = year; index

Resources last updated: 11/29/2015 9:44:19 AM