C# Asp.Net Gridview - Insert Update and Delete With SQL Server

what's up YouTube welcome to God bed mom in this video I will show you how to implement code operations I can set update and delete net p.net grid view before starting this video tutorial I would like to ask you a favor if you found this video helpful please thumbs up this video and for more hours and videos like this please do subscribe to this channel dot pen mob without further adieu let's get started this is my Visual Studio 2013 first of all we have to create s the dotnet web form application for that you can click on this new project here or go to file then new then project or you can use the shortcut ctrl shift and click on that then select web under wishes eShop then select visual studio then select espied or net empty web application then name your project here I will name it as code in big view then select the location where you want to save this application then click on OK so here we have the brand-new HP web form application so for I want to add a web form into this application for that right click on application then add web form I will leave it as default click on OK before adding SP dorneget view into this page we have to create a sphere server database and tab for that let me switch to management studio this is my management studio here we are connected to SQL Server instance inside this SP webform application we need to implement code operations on phone book details like first name last name contact and email for that we want to create a database right click on database then click on your database I will name this VB as phonebook daily then click on OK Yuli created database can be seen here now we want to add a table right click on table then new table first column will be phonebook I D that our type will be integer then first name as work our 50 the last name as work our 50 then contact as walk our 50 and email as walk 50 now I want to set this phone book ID column as a primary key then I want to set this same phone book ID as the identity specification for this table for that go to column properties then expand identity specification then set this as yes so we don't want to insert values into phonebook ID column SK server we take care of that if we start from one and incremented by 1 upon your code in session in order to say this table you can use the shortcut control s or you can click on this Save button here I will name this table as phonebook click on OK newly created tables can be seen here now flip to wishes to you first of all I want to add a good view into this web form for that click on toolbox here if you can't see toolbox here go to you then click on toolbox expand data section then drag this grid view into this form here I want to change this grid view theme for that click on auto format these are the themes available in asp.net grid view or now I will select no be fine then click on OK click on source I want to change this grid view ID here GV phone book these are the properties that comes with it beauty I will put a break here I want to comment it here for that you can do this Steen properties that's it with this grid view we want to design a grid view that looks like this it contains first name last name conduct and email finally we have a column which contains buttons like add new edit delete save and cancel in a saying column we have three types of form elements by default it will be a label when user click on this edit pencil the row will be changed into a sequence of text boxes in further it will be a text box so this is called SP dotnet grid view inline editing with operations like insert update and delete in order to design a gig you like this you can start with column stack here columns inside that we have to start with SP template field template field and here we can specify the column header header text is equal to first space name first of all we will specify the default label using item template you said that you can do this SP level now we want to buy this label text from the binding source for that single course pound symbol eval of eval of inside this course we have to specify the corresponding column from this tablet so it will be first name for snake and we have edit template so inside this edit template we need to specify what we want to show when user click on the edit button so it will be a text box ASP text box you need to specify the ID here txt first name and we want to bind the text like this let me copy and paste here finally a photo for that footer tumbler let me zoom the editor so that you can see what I am typing here and finally inside the photo thumbnail you need to add a text box and we want to change this ID here txt first-name photo footer photo text box will be empty so we don't need this text property here so like this we have three more columns for that let me copy this template field from here and pasting here three times first name after that we have last name last name last name we need to edit these properties here txt last name corresponding binding column will be last name txt last name fotox we want to do the same for remaining template fields you let me switch design so here you can see the four columns that we have this added apart from there you can see three extra columns so in order to avoid these extra columns you can do this set Auto generated columns as force mortos generated columns equals force that's it let me view the design so it looks fine now now we need to add one more column for this action buttons in order to add this final column into the grid view we need to have one two three four five so in total we need these five images so I have downloaded these five images in my desktop for the images now we want to in grow this images folder in our project for that let me copy this folder from here then open visual studio right click on the project then click on open folder in File Explorer then paste the folder here so here it is now click on this show all files button I click on images click on in Road in cottage now click on this so here we have the images folder inside that we have five images in order to design the last gig view quad for that we s star will SP template field in size that we need item templates then we need edit templates and finally we need photo template inside item template we need to add these buttons pencil edit button and trash delete button so that you can do this ASP image button means you are a that click on this pic you are I click on images first of all I am going to add edit button click on OK then set the command name can man named because edits then tooltip equals edits the meaning to give bits for this image button has to be pixel height as 20 pixel like this we need to add one more image button for that let me copy this and paste in here we need to change this image you are click on pic URL now we need to add image button for delete icon so select this TD PNG click on OK change this command name as delete soul tip - delete inside the edit template we need to have these two buttons for that let me copy these two image buttons from here and pacing here first of all we need a button for save or update then click on pic you are then click image folder then select the save image click on OK then set this command name as update tool tip will be updates second button will be cancel click on this pick URL cancel dot PNG click on OK corresponding command name will be cancel same tool tip now we need to photo templates inside the footer or we have a single image button for that we copy this and paste in here now we need to change this image URL select the image folder then select add new PNG click on OK set this command name as adding change the tooltip as add new that's it let me switch to design mode here in order to add new a course into this grid view we need to show footer row inside this grid view like this for that we can set this property show photo here show footer equals true along with that we have to set the data aquiles here that are key names equals phone book ID with this property we can bind the ID of the table inside this grid view it won't be visible for the user after this grid view we need to add two labels for error message and success message let me put a break here BR after that we need a SP level SP level text will be empty ID will be lvl successes for color will be green being like this we need one more level for the let me copy this and paste here lb L error message foreground color will be red let me view the design here so here we have completed with this speed view design part so far we have done a good job guys now we have to women gave you in line equations like add edit and delete before that we have to bind database double into this grid view inside the page load event now let's look how we can do that right click here then click on new code first of all we need to import the namespace using system dot data dot SQL clients now I am going to declare a string variable in order to store connection string for this database phonebook TB for that string connection string is equal to a blue course inside the code we need to write connection strings for the database for now I will place the console string here now in order to bind the database double phone book into the grid view I'm going to create a function here void populates good view starting with using statement a service o Celtic rescue L connection of the tier QL connection has SKU Elkhorn equals new SQL connection inside that we have to pass the connection string first of all we need to open this connection by calling open function now I am going to declare QL that addapt object here as SQL da equals new SQL data adapter inside that we have to pause the query selector from phonebook as a second parameter we have to pause the SQL connection objective now I want to create an object of data table for that we have to import this name space system dot data as the TBL s is equal to new data table now we are going to fill this at a table with for the SQL da dot fill D TBL I want to declare this data double object outside this using statement here and I'm going to buy this data double into the grid view here DV phone book door data source equals T TBL finally we need to call the function GV phonebook dot data binds now inside this page lo t when we need to call this function if it is not a post back is post back then we may call this function in populate guild you currently this page is empty since we don't have any row inside this table phonebook if there is no row inside the table we need to show the get view like this for that first of all I want to change the kid view design here so Hebda when MT is equal to true and we want to find a row that contains this text no data for for that go to the function populate grid view if data table that is visible dot gross dot count is greater than 0 then we will do this in the else part there is no wrong inside this table in that case we need to add a dummy row inside this double row store ad D TBL new law now we can bind this data table into the grid view like this let me copy this and paste here in order to add no data found string into this data table you can do this DV phone book dot rows first row index will B 0 dot cells dot clear so we will play all the cells inside the first row now we need to add a new cell into t4 so for that you can do this add new sample cell and we want we want to change the cell call span for that you can do this cells first column for span is equal to DT Beatles dot columns dot count now we can set the first row for cell text as no data found text equals you know that that found and I want to change the horizontal alignment for this cell let me copy this and paste in Europe horizontal alignment equals horizontal align dot Center in order to see these updates inside this application just build this solution build solution or you can use the shortcut ctrl shift B now will be successful they load this page that's it here we have the empty data grid view now we want to implement add operation with this photo row for that we have to add an event into this grid view on row command equal to click on this create new event it will create c-sharp code for this event then go to the C sharp code file so here we have the even for on go command this event will be triggered if you click on update add new buttons so we want to identify this is an event from add new button for there you can do this P dot dot command D equals command name for the button add new that we have given here add new button commanding is add you let me copy this and paste in here inside this epoch we are going to insert values from these text box into form book double for that I am going to start it using statement here SQL correction as this q elkhorn equals new SQL connection inside that we have to pass the connection string here for support I will open the connection here click on load open then I will write the query here string query equals insert into phone book first name comma last name contact and email values let me copy these columns from here and paste in here we want to put the symbol like the weight symbol here so this is a parametrized query in order to avoid ql injection so i want to create an object of SQL commands as SQL see emily equals new SQL commands miss Kubelik commands inside that force we will pause this query to the mystical connection object scale corn now we need to pass these parameters values for that you can do this ql c md dot parameters dot add this value first parameter name in the first name fishing here now you want to pass this first name parameter value from this text box txt first name photo and you can see here txt first name footer but you can't pass the value by just entering the text box ID txt first name rotor dot txt you have to do this DV phone book dot photo Rho dot fine control and inside that we have to pass this text box ID first name footer copy and paste it here and we want to cut this Kangol as a text box here has text box dot text nor trim trim function is used to remove space from both ends like this we have to pass three more parameters value for that let me copy this and pasting below that three times first name after that last name then contact then email then corresponding text box name txt last name photo and txt contact photo then txt email photo now in order to execute this query you can do this students e and d dot execute non carry execute non carry after that we will refresh to give you four then we can call the function operator kitview after that we have to set the success message lost x equals fewer cold addict let me clear the error level no text equals empty string build this solution for that you can use the shortcut ctrl shift be after surface will reload this page now I am going to add some test data inside this footer row you click on this Add button as it guys here we have added new record for James Smith now I want to add one more row right now we have added to a course into this grid view now let me check my SQL server table quite click on the table then click on top thousand rows so here you can see the two records for James and Maria in the sequel query here we don't have phonebook ID we don't want to insert values into phonebook ID column SQL Server will take care of that since it is a identity specification for this table phone book now I want to enclose this if block inside a try-catch block for that select all of them then right click and click on surround with then type try here click enter so this is a shortcut to enclose a code inside a try/catch pokeo inside the catch block can't exception ethics and we want to show the error inside the error level success message will be empty ever message will be e X dot message now if you so click on this edit button we want to show this row in edit mode so that you can do this on draw editing equals click on this now go to the code behind in order to show the selected row in edit mode you can do this TV edit index equals e dot new edit English after that we have to populate this if you like this run this application again for that hold ctrl then press f5 now click on this edit pencil so here you can see the row in edit mode and this action buttons are changed save or update and cancer if you click on this cancel button it should go back to the normal mode for that you can do this pawn grow canceling edit equals and click on this create new event then go to the code-behind just copy this and paste in here instead of this set this edit index as minus 1 now back to the application and user click on this save button or update button we have to update these details into the get view with given row phonebook ID for that you can do this go to the design phone draw updating equals and click on this create new event and go to the c-sharp code file now let me copy these lines from this o command this copied these lines and pacing here we don't want this if block here you want to take this query for update update phonebook sets let me remove this first name equals at the weight first name then lastly equals add the weight last name then contact equals at the weight conduct then email equals at the rate email ver phone book ID equals at the rail ID for these parameters we have to pass values from this text box not from photo so we have to edit this control name here just remove this photo from here finally we have to pass value for the ID parameter for that you can do this skew CMD load parameters dot add bits laddu then I D converter in 32gb for book dot data keys inside that we have to pass row index a dot draw index dot value to stream that's it update query will be executed here and then we will be first to give you and maybe set the success message as selected flow updated sorry we have made a mistake here instead of footer what we have to use the corresponding row rows inside that we have to pass the index e dot row index let me copy this and replacing this footer row here before populating the good view we want to set the edit index as minus 1 DB for book dot KD index equals minus 1 in order to run this application hold ctrl then press f5 click on this edit pencil selector draw is in edit mode if you want to cancel this edit mode just click on this cancel now I want to edit this row James 1 Smith 1 click on this button so here you can see the updated grow James 1 Smith 1 I am going to implement this delete operation for that you can do this switch to design on draw deleting equals then click on the screen new event and go to the C sharp file now let me copy these lines from this row updating events and tasting here now we need to change this query to delete from phonebook the phonebook ID is equal to ID for this query we have only one parameter so let me get rid of these extra parameters from here and we don't want this edit index and we will execute this query here and we can refresh the get view and we have to set the success message as selected Oh deleted let me run this application for that hold ctrl then press f5 now I want to delete this call for James 1 for that click on this trash delete icon acid guys role for James one is Deaton that's all for today if you found this video helpful please be subscribe to this channel dot and mob in this video we have discussed how to implement SP dotnet grid view insert update and delete operation or you can say get view inline editing you can download this project source code along with VB script from the link given below in video description please like and share this video with your friends and colleagues so that they can benefit from these have a nice day bye

Channel: CodAffection

Views: 329,959

Keywords: insert update and delete in c# asp.net gridview, addnew edit delete in gridview, crud operation in asp.net gridview, Show Footer When no data found in gridview, asp.net tutorials for beginners, Using SQL Server, Using Sample Project, Using Database, c# asp.net gridview inline editing, gridview in asp.net, Using TemplateField, Using Row Command, asp.net gridview Save edit and delete, gridview asp.net c#, insert update delete in gridview in asp.net with c#, CodAffection

Id: vuoJeQ4L3WI

Length: 34min 6sec (2046 seconds)

Published: Tue Jul 18 2017