Part 145 Implementing autocomplete textbox in asp net web forms

this is part 145 asp.net tutorial in this video well disk is implementing autocomplete text box in an asp.net webforms application using jQuery autocomplete widget and the web servers will be using this table TBL students within this demo it is the sequence clip to create this table and populate it with some sample data I'll have the script available on my blog in case if you need it we want a wave form to look like this when it is rendered we basically want to retrieve all the students that are present within this table TBL students and then display them within the grid we control as you can see here we also want to provide a search interface for example if I type letter M within the search text box we want to list all the student names that start with letter M and then if we select a student name and then once we click the search button we want that selected student to be displayed within the grid we control so let's see how to achieve this using the equity autocomplete feature and the web service first let's set up our application to use jQuery autocomplete widget and to do that navigate to jQuery UI web.com click download link so this should list all the jQuery UI widgets by default all of the pages are selected click this checkbox to deselect and then we are interested in autocomplete widget so let's select that and notice that all the dependencies are also automatically selected and now scroll all the way to the bottom click this download button the required jQuery files and the CSS files will be downloaded their will be downloaded in a zip format we need to extract them so I have already extracted them and then once you navigate to the folder you should see you know folder structure like this we are interested in these two folders j/s and CSS folders within the J's folder we have the require that jQuery files so we are interested in this jQuery file and the minified jQuery UI file so let's copy these two and move them to the scripts folder so let's paste in there and we also need this hisses files so let's go back to this jQuery folder and then let's expand open CSS folder within that UI lateness and we are interested in images and this minified custom CSS so let's copy them and move them to the Styles folder okay at this point we have set up our application to use jQuery autocomplete widget the next step is to write a web service so this web service is going to be responsible for retrieving the matching student names ok so let's go ahead and add a web service so right click on the project name within the solution Explorer add a new item and we want to add a web service and let's call this student service click Add so this should add a web service and we're going to write some ad or dotnet code so let's go and import the required namespaces so we need a system data we need a system dot data dot sequel client and we need system dot configuration and we want this web service to be called though from JavaScript and if that is the case we need to uncomment this line so let me uncomment this and look at this so if you want your service to be callable from JavaScript then you need to decorate that with the script service attribute ok now this method is going to return matching student names ok so I'm going to call this method get student names and what are we going to return student names list of matching student names so basically it's the return type is going to be list of strings all right so now let's write the area not net code to retrieve matching student names but before we do that we need a stored procedure and then for example you know you type letter M what should happen all the student names that match that start with letter M should be retrieved so we're going to have a stored procedure it's going to have one parameter and that parameter is going to contain the letters that we have typed here and that stored procedure is going to return us the matching links so let's go ahead and implement that store procedure so create procedure let's call it SP get matching student names and let's call the parameter at student name this is going to be of type in we're care as begin end and within the body we want to select just the name of the student from TBL students table fare name like whatever we are passing into the stop seizure has a parameter and we need to append the wildcard person date symbol because we are using like here okay so let's go and create the stored procedure f5 and let's quickly test this to make sure it works as expected so for example if I pass letter M let's see if this works so plus f5 look at that I get the employees whose name start with letter M all right so let's go and implement our web service here so first of all we need to retrieve the connection string from web config file and if you look at the web config file here I have already a connection string with name DBC s which is pointing to you know local installation of sequel server and we are using windows authentication okay all right so let's go ahead and read the connection string and to do that we are going to make use of configuration manager class dot connection strings of what is the name of a connection string DBC s and retrieve the connection string with that name and let's use a using statement and then create a sequel connection object the advantage of using using block here is that we don't have to explicitly call a closed method on the connection object it will be automatically closed for us so sequel connection equals new sequel connection and we want to create a single connection object using the connection string that we have just retrieved from web config file the next step is to create the sequel command object so sequel command CMD equals the new sequel command and our sequel command is actually a Stewart procedure so this is the name of the stood procedure so let's copy that and move it right here and the next parameter is the connection object so on using which connection object do you want to execute the stored procedure disconnection object we are going to pass that as the second parameter and since the command object is executing its tool procedure we need to tell that to the command object using command type a property and this stored procedure expects a parameter and what is the name of the parameter at student name so we need to associate that parameter with the stored procedure so let's create a parameter object so signal parameter let's call it parameter equals new sequel parameter and we're going to use this overloaded version of the constructor where it expects the name of the parameter and the value okay so what's the name of the parameter at student name so let's pass that there and we are we going to get the value from the value for this parameter is going to come into this function as a parameter so let's call this string student name so let's pass that as the value for this sequel parameter and the next step is to associate this parameter object with this command object and to do that we are going to use parameters property of the command object and pass our sequel parameter object to this function okay and all that is left out now is to open the connection and execute the command so sequel data reader Rd R equals command dot execute reader so what are we going to get back we're going to get list of students back so we need to return list of those student names back okay so first of all let's go ahead and create a list of string let's call it student names equals new list of string and to this list let's add the name of the student so we need to loop through the result set and to do that we're going to make use of while loop so loop through the sequel data reader and while you are reading from the reader retrieve name convert that to string and then add that to the list so student names dot add so once we are done looping through and adding the students to this list what we need to do we need to return that student list back so return student names so we have just implemented our student service and let's quickly check if it works as expected so let me run this by pressing ctrl f5 so this student service should have one method get student names so once we click that it expects a parameter that's a for example I pass letter J as the parameter invoke and notice that we get to students whose name start with letter J okay so our web service is working is expected the next step is to invoke this web service from our web form one dot aspx first of all we need to design over form like this ok so we need at this word name and then a textbox and then a button control and then a grid view control so let's go in and design over web form so name we need a textbox control so let's go to toolbox and let's drag and drop a textbox control and let's give it a meaningful name let's call it txt student name and we need a button control so let's drag and drop a button control and let's give this a name let's call it BTN I mean an ID let's call it BTN submit and the text on the button to submit okay and then let's include a gridview control let's include an HTML break and then a grid we control grid we control should be on the data tab so let's expand that drag and drop a grid view control and let's give this it could be control a meaningful ID let's call this gb students ok so now we need to write some JavaScript here so if we run this now we should have the interface that we see here a text box button and a grid we control okay there we go so now we need to write some JavaScript basically to implement autocomplete feature okay so we need the script and CSS files so we need this CSS file jQuery custom CSS file so let's drag and drop that there and we also need these jQuery files so let's drag and drop them in that order okay so we're going to write some JavaScript here so let's use script tag set the type to text slash JavaScript and the language is going to be JavaScript okay so let's write some jQuery code here so dollar function okay so we want to implement autocomplete for this text box control right so we need to first find the text box control and to find the text box we're going to make use of ID selector so dollar ID selector in jQuery is pound and then you know to get the client ID of this text box control I'm going to use client ID property and to use that I'm going to use angle brackets percentage equals that's the name of the control I'm going to use a client ID property to get the client ID basically okay so for this text box we want to implement autocomplete feature so we are going to invoke this autocomplete function but this function expects several options and the most important option is the source option the source option is going to tell you know what data it has to display okay so basically where are we going to retrieve the source data from we are going to retrieve that using this web service this web service has got this method which is going to return us the matching names okay so the source for this autocomplete feature is going to be that web service okay so and we are going to invoke that using Ajax and to speed things up I have already typed that code so let me copy that and paste it here and it's straightforward code we'll go over that in just a bit so here we are specifying the source option for this autocomplete feature and you know basically the source is going to be a function which has two parameters request and response and we are going to invoke the get student names method within student service toward SMX web service using Ajax okay so this is the URL of this web service that we need to invoke get student service toward ASM x4 slash get student names so let's actually make sure we don't have any errors in the name so the name of the method is get student names and the web service is going to be student service dot ASM X let's piece that there okay and if you look at this get student names method it expects a parameter so obviously we need to supply a value for that parameter so we are we going to get the value for that parameter farm so we need to specify that we're going to get that from the request object so this request object has got this term property you know from that term property we are going to retrieve that okay so we are going to pass that as a value for this parameter so what's the name of the parameter here student name so the second line here is the parameter value okay and we're going to invoke the sketch student names function using post request and the data type has to be jason and the content type has to be jason and then obviously when we execute this function there could be two things happening you know either it has successfully executed and return the jason result otherwise they could be an error okay so if jason data is successfully returned then what do you want to do with that json data you know so this is the JavaScript function that I want to execute in case if the JSON data is successfully returned okay so what are we doing here we're basically retrieving that data and passing it to the response okay so basically here the confusing thing is why are we using this a dot d now one thing to keep in mind is that asp.net Ajax library uses a wrapper around JSON formatted data to prevent jason hijacking if this is too confusing at the moment don't worry we're going to discuss jason hijacking in a later video session in great detail for now understand that to retrieve data from jason formatted data we use a dot d okay now you know to this object you can give any meaningful name you want for example you can call it result so within this result object to retrieve the json data you have to use you know dot d and that is to prevent a json hijacking that's how a wrapper around json formatted data is implemented within asp.net Ajax which we will discuss in detail in a later video session alright so if at all if there is any error executing that function then we are going to just player you know alert this message there is a problem processing your request ok so at this point everything that should be working as expected so let's go ahead and run this if we have implemented everything then autocomplete should work so for example when I type letter M look at that I get the matching student names so if I plus letter J we get the matching student names now when I set the student click Submit nothing happens we need to implement that search code so that is again straightforward so with the newer web form so within the page load event we're going to write some ad or net code so let me actually copy these namespaces and paste them within the web form okay let's also copy this area dot net code so that we don't have to and we'll keep typing it so here I'm going to have a private function what let's call it get student data and obviously this is going to have a parameter string a name maybe let's call it student name so when we select a student name and then click Submit we only want to display the matching student name our student within the gridview control okay now another thing is when we first load this waveform you know we want to display all the students with liquid B control so for that to happen so this student name could be now at that point so when the waveform first loads the student name could be now okay in which case we want to load all the employees all these students basically so first of all let's create a stored procedure to get all these students so create a procedure let's call it SP get students again this is going to have a parameter at student name so let's pass that and this parameter can be now so I'm making this optional by specifying a default value as bacon and so what I'm going to do is copy this and paste it within this procedure so we not only want the name we want all the columns so I'm going to say star from dbl students wear name like whatever we have within this parameter or that name I mean at student name parameter could be now in which case we're going to retrieve all the students so let's create the store procedure look at that if I just execute that without passing any value for the parameter I get all the students in case if I pass for example let J and I get only that students whose name starts with letter J all right so let's go ahead and invoke the stored procedure so basically what are we doing here by reading the connection string from web.config file we want to create a list of students or we don't even need a list because we are going to bind the output directly to the grid we control so we have the connection string we are creating the sequal connection object the command is actually the stored procedure as we get of students so we're going to pass that here and we're going to execute this command using this connection object the command type is stored procedure and the stored procedure expects a parameter and the name of the parameter is student name and we're going to get the value for that parameter using this method parameter okay but then one thing to keep in mind is that this parameter can be null okay so if that is the case we first need to check if string dot is null or empty so if this parameter so if this parameter is not null are empty then create a parameter object associated value with that and then add to add it to the command object okay and then open the connection execute the command we don't even need this while loop so let's get rid of that what we're going to do here is what is the name of our grid we control GV students the data source for this is going to be the reader object and we are going to invoke data bind all right so we have the flight private function so when the page first loads if it's not a post back then we want to invoke this get at student data function and then we are going to pass null for the student name because when the waveform first loads you know we want to display all the students within the grid we control in that case student name is going to be now okay now we have a button control on the waveform so when we click that button control you know we want to display the matching students okay so in that case we're going to call again this get a student data function but then we are going to pass the student name so where are we going to get the student name from txt student name a textbox so dot text property should give the give us that all right that's it so let's go ahead and run this now when the page first loads we will get all the students displayed within the grid we control and look at this the moment I type letter J we have autocomplete I select in a student you know student is displayed I type M I get out to complete if I just submit look at that I get all the students whose name starts with letter M alright that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 75,531

Keywords: autocomplete, jquery, textbox, asp.net, c#, auto, complete, web service, web forms, webservice, functionality, how to create autocomplete textbox in c#, c# autocomplete textbox from datatable, c# autocomplete textbox example, c# autocomplete textbox from list, c# autocomplete textbox from database, asp.net c# autocomplete textbox from database, autocomplete c# textbox database, autocomplete textbox in c# with database, asp net ajax autocomplete textbox from database

Id: I3_5gnTIS4E

Length: 23min 40sec (1420 seconds)

Published: Sun Sep 22 2013