GridView insert update delete in asp.net - Part 23

hello welcome to presume technologies I am venket this is part 23 of asp.net grid-view tutorial in this video we'll discuss about inserting updating and deleting data from grid view using sequel data source control basically we want to do all the crud operations you know we want to be able to select data from a table and display that within the grid view control we want to edit delete and then we also want to insert records into a database table using this grid view control and if you notice we are actually using the footer of the grid view control to perform insert operation okay and also notice that we want to validate data while we insert and edit data so when I am inserting you know we don't want to allow users to be submitting you know blank name gender or City the same is the case when we are editing data for this demo we'll be using TBL employee table so let's flip to visual studio drag and drop a grid view control onto the waveform let's sort of format that let's choose brown sugar theme let's drag and drop sequel data source control onto the webform let's configure this let's choose our connection string from web config file click Next select TBL employee table we want to select all the rows so select star from TBL employee click on the Advanced tab click on generate insert update and delete check box because we want to enable editing updating and deleting on the grid view control click OK click Next test your query click finish now let's associate this could be controlled with signal data source control since we have you know edit and delete statements you know we have these options enable you know enable editing enable deleting so we have edit and delete buttons okay so that should enable editing and deleting now if you look at the slide we are using the footer control of the grid view to actually perform insert operation so we need to display the footer control and to do that by default you know the footer is not visible so go to the properties of the grid view control and there is a property called show footer simply set that to true and we will have the footer visible okay now look at this you know we have this link button insert link button within the employee ID column whereas text box control within the name column you know a drop-down list within gender so basically if I want to include these controls you know in these columns of the grid view control now I need to convert these columns into template fields by default you know the grid view control has generated bound fields you know this employee ID name gender and City by default these are bound fields now if I have to include a footer template so that I can include these link buttons and other controls then I need to convert these bound cells into template files and there are two ways to do that we can change the HTML directly here or we can use the designer you know that's an easier option so let me select on the a no click on grid view task and then click on the Edit columns link and then from the selected fields you know click on employee ID column I want to convert this into bones I mean a template fear so click on this link and with this filled into a template fear which should automatically convert that bound field into a template fear and do the same thing with name gender and city as well so that should automatically convert this for you know bound fields into template field so if I flip the webform now to the design you know source mode look at that everything is now converted into a template real okay but then you know obviously if you look at this gender bound field you know we don't want to display text box control as the editing interface we want a drop-down list control as the editing interface so what we need to do is replace the text box control with a drop-down list control so I'm going to get rid of that go to the tool box drag and drop a drop-down list control and then obviously we need three items within this drop-down list what are they select the gender male and female those are the three things that we need to display so let's in a format that properly plus control K and D all right and then obviously when I put this row in edit mode I want the gender of that person to be selected within the drop-down list by default and to do that we need to set the selected value property so selected value is equal to whatever is being returned by this bind method okay so let's actually copy that and paste it right there okay let's copy this and paste it okay all right so at this time we should have editing functionality working as expected now what else we need to do we need to include you know the link button within the footer of every car you know link button within the footer of employee ID column you know along the same lines text box gender and city within the respective footers of those columns so now when we click Edit here look at that I have drop-down list here and look at the values they get properly selected as well okay so within the footer control let's include the link button so obviously we want the link button in employee ID column of the grid view control so employee ID column is this one so here within that template field let's include a footer template let's drag and drop a link button there and let's give it a meaningful name link button insert and then we want the text on that link button as insert okay so now if I flip this waveform look at that I get the link button next we need a text box control a drop-down list and a text box control so let's flip this again to the source mode let's actually get to you know the name bound field so this is the name template field and then within here we need to have a footer template and we need a text box control so let's drag and drop a text box control and then let's give it a meaningful name txt name and we want the same thing for CT as well so let's keep gender for now because we need a drop-down list within the gender so we will come back to that so let's go to City and then paste it there and let's give this a meaningful name let's call this txt City and within the footer template of gender we need a drop-down list so let's drag and drop a drop-down list there actually we can copy and paste this okay but we don't want any you know value to be selected within the drop-down list when we insert a row because everything will be blank if you look at the slide you know it's a new row so select gender will be selected by default and then the user needs to select the gender of the person when he is inserting so we don't want anything to be selected so I'm going to get rid of that property there okay so at this point we should get the interface you know the insert employee interface so we get that so gender we have them you know all we need to do is when I click insert we need to insert that into the database table but before that let's take care of validations look at this we want to validate data of and inserting and editing so we need some validation controls required field validator controls and so obviously when editing you know obviously employee ID is not editable so we need we don't need any validation control there but name is editable so we need a validation control there so let's I mean just to speed things up I have you know a required field value data here configured with all the properties I just explained those properties let's format this properly control kd2 format that alright so required field value data so I've given it a meaningful name rfv required field value data edit name is the ID then it is equal to server error message name is a required fear control to validate we want to validate this text box one okay and text this asterik for color I want a red color you know so that's why we have set it to red so this is when editing you know an employee's record but when I am inserting an employee's record then we have a text box here so we need to validate this as well so I'm just going to paste that there so required field value data and I'm going to call this you know give it a different ID you cannot have two controls with the same ID so since this is for validating when we are inserting a new employee so I'm going to call this rfv insert name okay so done it is equal to server name is a required field control to validate it's going to be txt name okay so let's put it there and text for color alright next we need to validate you know the gender when editing we want to make sure that effort you know the user has selected gender from the drop-down list except select gender you need to select either male or female okay so let's put that there so RFA this is going to be edit gender okay because this is within the Edit item template this template will be used when we are editing a row okay and obviously this is gender so the error message is going to be gender is a required field and we are validating drop-down list one so that's the control to validate text is equal to star for color is equal to red there's one more property that we have to set when we are validating you know selections within the drop-down list that is initial value so what's the initial value going to be within the drop-down list its select gender if somebody selects select gender from the drop-down then we know for sure that's not a valid gender so that's the initial value and we want to do the same thing when we are inserting data so obviously we need to have a footer template so within the footer template we need to have this validation control so let's paste it right there so we are actually again here there's a problem because look at this drop-down list the ID is drop-down list one here it's drop-down list one so let's give this a meaningful name DDL gender because we cannot have two drop-down list controls with this is you know two controls with the same ID alright so here let's change the ID of the required field validator let's actually call this RF a required field validator insert gender okay done it is equal zero server error message gender is a required field and the control to validate is DDL gender so let's put it there texts a strike for color red initial-value select gender finally we have to do that for City so let's copy and paste that there let's copy the required field value data from name and then use it for city but we have to change the IDs so let's paste it right here and required field value data this is going to be edit City so let's give that meaningful name there edit City when it is equal to server error message city is a required field control to validate we want to validate text box three so let's set that as the control to validate text Asterix for color red and then let's copy and paste this finally in the footer template so this one is going to be rfv required field value data insert city when it is equal to server error message cities that required field control to validate the XD city so most of it is actually copy paste you know text a strict for color red so we are done and finally we also need a validation summary control because look at this you know those asterisks you know symbols are displayed next to the controls where the validations have failed but detailed error messages are displayed in the validation summary control so I'm going to drag and drop a validation summary control next to the grid view control so let's put a validation summary control and again within validation summary control we want the error messages to be in red color so let's set for color to red so with those changes let's run this now so at this time we should have validations in place while editing as well as while inserting so I click at it look at that i blank out name and then I click update I get this name required which is as expected but look at this you know the validation controls that are present with an insert are also for head we didn't expect that to happen because I am editing a row no I'm dating a row only these validation control should be fired if I select gender and try to update look at that I get that again I remove that so total a you know when I am clicking update only these set of validation control should fire whereas when I click insert the set of validation control should fire so basically whether I click insert or update look at that all of them are fired which is not working as expected so basically we need to group this insert button with these set of validation controls that I have within the footer okay and the Update button should be linked with these set of controls that we have within that row when I am editing that okay so let's actually group this insert button with these validation controls and to do that we can make use of validation groups we discussed about validation groups in detail in asp.net video tutorial so please check those videos right so let's use validation group and then group you know the link button that's there here and then these validation controls and then we also need to associate that to a validation summary control so let's do that now so within the footer template I have this link button so link but every you know this link button has got a property called validation group I am going to set that to insert and I'm going to use the same group name for all the validation controls that are present within the footer control okay so for the validation control here I'm going to set the same group so that's for name and this is going to be for gender required field validator and finally for City okay and then we need to set that for validation summary control as well okay but then we will have another problem now but it's easy to fix okay so now I click insert look at that I get the error messages as expected I click Edit and then let's blank that row and then I try to click update look at that I don't get the validation summary information here but I get those little asterisk symbols indicating that there is a problem on the other hand other hand look at this when I click insert look at this only these three validation controls are fired and and the respect to detail their messages are displayed here but then that doesn't happen when I do you know when I click this Update button why because we don't have a validation summary control to display those validation error messages because remember we have changed the validation group name of this validation summary control so now this validation summary control will only be used with validation controls that are present in the footer okay so let's have another validation summary control and knock off the group so it's going to work with the you know rest of the validation controls while we update the data so when now when I run this you know the validation should work as expected so I click Edit let's blank this out and let's try to click update look at that it's working and look at that it's working as expected so I cancel that I click insert you know everything working as expected now all that is left out is when I click this insert button we need to insert you know whatever row information that I enter here into the database table using this grid view interface so let's see how to do that obviously that should happen when I click this link button insert which is present in the footer template of employee ID template field so let's go to the footer template so this is the link button and if you look at the ID this elbe insert and let's generate the even handler the click event handler for that one since this is a link button I'm going to say link button lb insert is equal to new link button so lb insert click plus equals so that's the event handler so let's copy okay now instead of hooking it dynamically since we already have a link button there I'm going to use this even handler method and specify that within put a template you know using the on click attribute so on click what we want to do we want to call that method lb insert underscore click within the code-behind file so obviously what we have to do here are write code to insert row into the database table okay so obviously what we need to do when we click this insert is basically retrieve the name gender and city values and then give it to you know insert parameters of sequel data source control remember when we were configuring sequel data source control we generated you know we check that check box generate insert update and delete statements so that has generated these statements I mean delete command select command update command and insert commands and along with that we also have insert parameters for example so if you look at this insert command insert into TBL employee name gender city for these three columns we want to supply values to these three parameters because this a parameterised query and so obviously if you look at the parameter names they are named gender and city we need to supply those values to these parameters okay and then we can use that sequel data source one control to do the insert for us okay so let's see how to retrieve the value so where these values for those parameters are coming from from these text boxes and a drop-down lists that are present within the footer control okay so these are the parent insert parameters of sequel datasource one control so I'm going to say a sequel datasource one dot insert parameters and this is a collection so you can you know retrieve a parameter by ID or by name okay so I can use the name one of the you know one of the parameters is name so where do I get the value for this parameter so the value for this is going to come from the grid view control you know within the footer of this grid view control we have this text box control so we need to retrieve it from there so to do that we have to use you know little bit of code here so grid v1 dot footer control I mean footer row so with in the footer row we need to find a control so if you remember with in the footer control we have given you no meaningful IDs for example to retrieve the name we need to use so within the footer control there is a textbox with name txt name that's going to store the value that we type in so we need to retrieve it from that textbox txt name is the ID of the textbox control so we have to say find control with name txt text box so let's copy that and paste that within the find control method and notice this find control method this is actually going to return an object you know a control back but what are we expecting out of it a textbox so let's type guess that to be of type text box and then use the text property to retrieve whatever value that is present within that so it's as simple as that so let me get this in two lines so we can see it so all we are doing here is we are populating we are setting a value for the insert and name parameter and we have to do the same thing for gender and city so sequel datasource one insert parameters you know the second parameter is going to be gender where is gender going to coming from gender is coming from DDL gender drop-down list okay and that's a drop-down list so I need to typecast that to be of type drop-down list and drop-down list has the selected value property to retrieve you know whatever value that the user has selected and finally what we need to do we need to set the parameter value for City so let's do the same thing here the name of the parameter is city and it's going to be a text box oh and it's called txt city is the name of the control and that's a text box control so that's it so we have set the values for parameters finally there's one more thing that we need to do the sequel data source control has got a method called insert so when you call this method that's when the sequel data source control take these parameters and then execute the insert command so the sequel data source control has got an insert command so that will be executed when we call that insert method on the sequel datasource one control this is the insert statement all right so that's all there to it so let's run this now we should see it working as expected so we should be able to you know edit data so I want to change Mary to maybe Mary three and her gender from male to female okay from New York to NY and update that so that works as expected now if I want to insert a new employee let's call Jane female and let's say Sidney let's insert look at that that gets inserted and just to make sure it's inserted within the database table you see that it's right there okay and obviously validations are working as expected as well okay so in this video we have basically discussed about all the database credits using a grid view selection insertion updation and deletion okay in our next video we'll discuss about doing exactly the same thing but using object data source control on the slide you can find resources for asp.net c-sharp and sequence of interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 474,860

Keywords: gridview, insert, update, delete, asp.net, edit, sqldatasource

Id: vZIOI136IKY

Length: 23min 47sec (1427 seconds)

Published: Tue Mar 12 2013