Autocomplete textbox using jquery in asp net

this is part 72 of jQuery tutorial in this video we'll discuss how to implement autocomplete text box using jQuery and asp.net here is what we want to achieve this database table TBL students is going to contain some student data within an asp.net webforms application we are going to have this student name text box as we start to type the name of the student we want this text box to suggest all these student names that are available in this case notice that we have started to type letter M and this text box is suggesting all the student names that start with letter M now instead of typing the student full name we can actually select the name that we are interested in from these suggestions so let's see how to implement this autocomplete feature in an asp.net webforms application using jQuery UI widget autocomplete so the first step here is to create this database table which I have already done and here we've got the create table script and this in subscript is going to insert some test data I've already executed the script and at the moment within this students table we've got 10 rows the next step is to create a stored procedure which is going to retrieve student name suggestions so let's go and create that stored procedure now I'm going to call this SP get student names and the stored procedure is going to have a parameter I'm going to call this term and the data type of this is going to be an ver care as Big M and so what do we want the stored procedure of it to do we want to select name column from TBL students table their name like whatever is the input parameter that we are passing and to that I'm going to append this wildcard percentage so that's going to execute this true procedure okay now for example if we pass letter m to the stored procedure then this stored procedure should return as all these student names that start with letter M so what's going on here so this em you know whatever we type here will be passed to this parameter and look at this here we are retrieving all the name column values from TBL students table where name like at term so in this case like M percentage so give me all the student names that start with letter M so for example if I pass ma then it should only written as all the names that start with ma so we should get only three now okay so I've got this true procedure now let's go ahead and flip to visual studio here I have an asp.net webforms application within the web config file I have included a connection string to our sample DB database which contain our TBL students table so the next step is to add a generic handler to this project okay and I'm going to call so within this add new item dialog box select the generate canula and I'm going to call this student handler so what is this handler going to do it's going to retrieve the student data for us from the database table TBL students so within this process request function within the student handler class so this enhanced from I HTTP handler class I mean interface and it has got this function process request which we need to implement so basically this function is going to retrieve the data from the database table and return those student suggestions okay so we are going to write some a do dotnet code here let's bring in the a do dotnet namespaces we need system dot configuration we need system dot data we need a system dot data dot seek for client and we also need a system dot web dot script dot serialization because we are going to serialize the data to adjacent format using this JavaScript serializer all right so the first thing that we are going to do here within this process request function is to create a variable of type string and I'm going to call this term and what is this going to it's going to make use of this context object so this context object has got request parameter and I'm going to request for a queries rain that has this name term in a bit you will understand where this query string is coming from now if we have a query string with that name we want to use that value store that value in this variable otherwise if it is null for some reason then store empty string in this variable in a bit you'll understand you know how we are going to use this variable alright so the next step is to create a string variable and I'm going to call the CS connection string and we are going to read the connection string from web config file using the configuration manager class the name of the connection string is DB CS and we want the connection string value okay and using that connection string I'm going to create a sequel connection object let's call this con and we are going to use the connection string that we have just create read from web config file okay the next thing that we are going to do is create a sequel command object and using this sequel command object we are actually going to execute this tool procedure that we have created SP get that student names and we are going to execute this command object using this sequel connection object since this command object is executing a stored procedure we have to tell that to the command object using the command type property and this stored procedure also has got a parameter and input parameter so we need to create that and associate it with the command object so command object dot parameters dot add and what do we want to add we want to add a new sequel parameter object so let me go ahead and create that create a new sequel parameter object and let's populate the two required properties the name of the parameter so parameter name what is the name of the parameter we called it at term so let's copy that and specify it here and where is the value going to come for this this is going to come from this variable okay so let's pass that so we have created the parameter associated it with the command object now let's go ahead and open the connection and let's execute the command as a command dot execute reader and whatever result we get let's do that in sequel data Reader object let's call it RDR while RDR dot read so we are looping through each row what do we want to do we want to create a list of student names so I'm going to create here a list variable so list of string let's call this list student names equals new list of string so while we are looping through each row that this is command dot execute reader okay so we are going to get the reader object as we are looping through each row then what we want to do we want to add to the student names list the name of the student so RDR and this is going to contain name column so retrieve the name column value and add it to the list okay so by the time this code finishes we have all the student names added to that list okay now what if you want to do we want to create an instance of JavaScript serialize it class and this object has gods he realized function and we are going to serialize the list of student names to JSON format and whatever we have serialized we want to write that to the response stream so I'm going to again use this context parameter that's coming into this function so context dot response dot right we won't write the JSON object JSON string okay so we have our handler function which is going to talk to the database and retrieve the data okay so let's go ahead and quickly test our student handler okay so I'm going to run this and let's navigate to student handler dot shx so look at this in this case you know we don't have that term querystring right they are not passing the term query string to this handler right as a result what happens we are passing an empty string so when we pass an empty string that gets passed that empty string gets passed to this stored procedure SP get student names and what happens when we pass an empty string it's going to return us all the student names from the database okay and that's what we are getting here now for example if we pass in our term query string and if I pass letter M is the value for that query string look at that now we only get student names that start with letter M if I pass for example M AR for look at that in our names that start with M AR all right so our handler is working is expected now let's go ahead and add a webform to a project and let's name it wave from one dot aspx and here let's actually include this little text student name and I'm going to include an asp.net text box here let's give it an ID let's call it txt name so this is going to be our name text box into which we type the student name and the important thing here is within our head section we have to the first thing that we need to do is include this script file you know this is our jQuery file jQuery script file now in addition to that we need the jQuery UI and JavaScript files so we want to use the jQuery autocomplete widget so now in order to get that navigate to jQuery UI dot-com website and here on this website you cannot actually find all the jQuery UI widgets so here we have a Cardian autocomplete but on date picker etc okay so we're going to see how this autocomplete widget is going to work we're going to use this in asp.net webforms application so now on this website when you actually clear on this download button it will take you to their download page and at the moment notice this we've got this checkbox toggle all so all the UI widgets all of them are selected okay now for some reason within your production application if you want to just use the autocomplete widget then there is no reason why you want to download the source code CSS and images of all the vidiots right so in that case what you can do is customize this download so you can uncheck this and then check only the widgets and competence that you need so in this case if we just need autocomplete so I can select that and all the dependencies will be automatically checked for us and then once you click download you know only the source code that is the jQuery code CSS and image is required for that component will be downloaded in case if you want all of them you know check this box toggle also it's going to select all of them and once we click this download all it's going to download the required files I've already done this and downloaded this zip file once you extract within the folder you will have several jas CSS and HTML files and images folder as well so within the images folder they've got all the images that the widgets require and here we have the minified and non minified versions of CSS and JavaScript files if it's a production app then probably use your minified versions because they are smaller in size so they're optimized if it's for your development purpose then use the normal minified versions for easy debugging so within my project you know here the asp.net webforms application we are going to implement the autocomplete widget right so all you need here is three things so you need you know the jQuery UI javascript file jQuery UI CSS file and the images okay so I've already done this so I've copied them and paste it into our asp.net webforms application so we have the jQuery UI jQuery I mean CSS file and the jQuery UI J is file and I also have the images folder within our web form one dot aspx at the moment we only have roughly the jQuery file script file in addition to that we also need the jQuery UI J's file we need the jQuery UI CSS file so those are the three things we need and let's include another script section here so script type equals text slash JavaScript and dollar document dot ready okay so we want to use autocomplete feature so we want to use that autocomplete feature with this text box txt name that's the ID so let's find this text box with that ID so dollar find the text box eye by ID and the ID that we are looking for is txt name and let's copy that and look at this on this I can actually call autocomplete so that's a function that we are calling and look at what this function expects it expects some options to be passed okay and if you want to see what those options are you also have the jQuery UI documentation available so if you click on API documentation it will take you to another page where you can find the documentation of all these UI widgets okay so if you want to find for example autocomplete you can search for that so autocomplete widget and look at this here are all the options that you can use and look at the source option so basically you'll have to set the source option so this is going to contain the suggestions that we want to display within that name text box in this case okay so we will talk about the source in detail in a later video session for now we are going to set the source option and we are going to set that using a Jake JavaScript object so I'm going to set source to our handler okay student handler dot shx so the name of the handler in this case is student a sh X so this is all the code that it requires so what is this handler going to do this is going to written as the student name suggestion so what is going to happen now let's go ahead and run this so here look at this as I type letter M look at that we get all these additions that start with letter M similarly for example when ice type letter a look at that and as I continue to type look at that you know whatever suggestions that match the characters you know those are displayed for example if you type letter R look at that we get that list so what's happening here now when I type letter R for example here what's going to happen is this autocomplete this is going to call this student handler dot a sh X and it's going to pass you know that letter that we have typed in this case R - you know this handler using a query string so what's good what it's going to do is call the student Handler and then append a query string so student handler dot a say it checks question mark term equals whatever we type into the text box in the text box we have type letter R so that R will be passed you know using that query string to the handler and what are we doing within that with that query string within this handler we're actually reading that query string value and we are passing that to the store procedure stored procedure is executed and whatever the names that match you know that start with that letter I return back and then those are displayed as as the suggestions within this text box so here is the handler code here is the web form HTML ng query code thank you for listening and have a great day

Channel: kudvenkat

Views: 81,250

Keywords: jquery ajax tutorial, jquery ajax tutorial for beginners, jquery autocomplete in asp.net, jquery autocomplete database, jquery autocomplete asp.net textbox, jquery autocomplete textbox dropdown, jquery autocomplete c# asp net, autocomplete textbox in asp net with database using jquery, jquery tutorial, jquery tutorial for beginners

Id: tCr1p_InOe4

Length: 17min 30sec (1050 seconds)

Published: Sun Jun 21 2015