Performing ADO.Net CRUD operations in MySQL using C#

[Music] hi in this video i'm going to discuss the ado.net crude implementation code means create read update and delete records using your csap application that is connected to a database to start let us identify the objectives of our discussion number one we need to use the mysql data classes to manage database records using c-sharp display subform for data entry write c-sharp method that will execute the insert into update and delete mysql commands and number four we are going to create a method or we write statements that will validate user input so that we can identify if the user filled up properly the required fields for our program but before we start let me give you an overview of what particular implementation that we will be doing in this particular video so in this example i will have we will be using our template the previous activity that we developed so in this application notice that we have this data grid in our program and we have here uh two extra columns and has two buttons these buttons allows us to edit a particular record and delete a selected record and also i have added a button that is called new paddock so that we can add a new product so let's try implementing uh this functionality so in this example when you click the add new button it will pop up a new form that is for new data so let's try demonstrating what this action is all about for this form is all about so for example i will try to add a new product including the product code and then description for example item to quantity for example this is the quantity that is for the stock of the product inside the data agency for example 1000 and the order level this is the minimum products record which means if the product is the minimum you need to reorder you are going to be alerted if the product is in minimum stock so that the owner will be notified if he or she needs to reorder this particular product let's say 10 items then the price for example i will be putting the price of diatom let's say 100 or yeah 100 and discount percentage let's say three percent for example if you are going to implement for example in purchase the user as per discount you are going to use this discount rate as the discount and then notice in this form i have a selection combo box that allows me to identify which vendor offers this product so let's say this one and save and then a pop-up will open to allow the user uh notify if the product is successfully added click okay okay so after adding the product notice that this product is now added in our database try to search the product is in description okay item okay now for example you want to delete or edit this product okay so let's say item number three a4 save okay now we have now the item number for here let's say we want to delete it okay so this will message a pop-up will allows you to confirm if you really want to delete the product now since this product is a newly added product it's easier to delete this one because it's not yet connected to a particular transaction so if you're going to delete a record make sure that this is not yet related to another table because uh it will ask permission to delete okay so for now let's focus on how to implement food simple cloud implementation we will go to that particular advanced implementation later in our class okay then delete so as you can see up and delete the form automatically refresh the records coming from the database so that's what we are trying to aim in this activity so in order to start uh let me give you the files for this activity so these are the tools and materials needed for different activity make sure you have the amp installed the upcoming edition of the screen for the portable version in some case the portable version is not working uh in windows 10 so if you uh want to acquire the scalio community edition just search this in your uh google uh search engine and look for sql community edition it's free and you need to install it on your computer of course the pdf because i have uploaded an instruction uh our requirements for this uh program is visual studio 2010 or higher the minimum version is 2010 so that it will be easier for you to develop and it requires minimal specification on your computer and of course our classes you don't need to install the mysql connector anymore all you have to do is to download the mysql data and also the database that we will be working if you have all if you already have these uh items or files in your computer no need to download them and of course our template for this task the task five files so you need to download this from this link okay so make sure you download it and extract into a specific location for example desktop so that you can easily locate it when you extract the file okay before we start make sure you downloaded the files and store them in your desktop for example i have already acquired the file so in this case i stored my file or my downloaded task file start file into my desktop so make sure uh it is complete extracted so let us open the project so let me give you a tour of the contents of the file okay let's wait for our application to load the file so here it is now in this particular document excuse me okay in this particular document you will be looking for the project files or the components of this project so you have these two forms so i already made this template so that will be uh easier for you to follow the instruction that will i will be discussing later so i have this uh product list form so it will allows us to display the list of the products so as you can see i have now the form and this will allows us to display the contents of the product table i already have the bottoms and of course the subform so if you want to add some form all you have to do is to right click your project and add a new form something like that make sure you rename your form depending on the purpose of that particular form and as you can see i have now uh the bottom cancel and save so when you save the document it must identify if the user successfully inputted the required data now let's go back to our form okay so when you run this form it will be like this okay so as you can see when you run the form it already contains the bottom needed okay so how do we develop this one so to add buttons to your database all you have to do is to right click your data grid okay edit columns and add to notepads we have here two buttons so to add that button you have to list to add a new column and choose a button column so that's how you implement that so as you can see in my button once you add a button you need to edit its keys for example the text of my button is edit and the other properties of that is of course you need to personally modify the size and each other so that's how you add button so when you add a button well let's try to add a new one let's say uh whether it's okay i will be using a blank okay so for example i want to make this uh a new button so let's look for the capture so let's say hello then because you can see i have now a header there so let's start adding the i'm not going to put up the header text instead i will be putting okay ah sorry this button is a text box sorry i will add another so choose button okay okay and then your text header text will be let's remove the header text instead we type the text let's say hello okay now let's click the ok button okay so when you run your program it will be like this one uh you notice that the button is not actually appearing in in design mode but in running mode so button is now appeared at the last portion of your data grid okay so now let's modify our button so that it will display a caption that we needed so upon clicking your button so in your properties always choose true so that what you type here in your text will appear in your bottle so let's start there okay so let's see okay so this is now your bottom so it's credit for your implementation but for now uh let us remove this and i just demonstrated how you add for a button to your form or data grid so i just remove this one okay okay now let us try to add functionality for our ad let's start with create or insert into a new required tool okay so in order to allow your form to display a subform to allow the user to input their new data or new products for a new record let us try to implement the subform functionality so in this particular line we have not about the new so let's try to resize it so that it will be visible in your part okay so in this case we have a new button so how do we implement uh displaying a new form after the user click the new button so first let us create or call the form name of our form so we have here the form name so the form name of our subform is frm product ae that is for add edit and let's say create an instance of that object new product equals to new frm e okay so in order to display the subform you need to call the new object that show dialog the purpose of show dialog is to make our form so the purpose of this show dialog method is to allow our object the new form to display in a modal form in the same model form uh the priority of our application is focused on this particular object so let's try to run this one and examine the difference okay so this is normal form so as you can see it's already displayed in our screen so when you click the new product button so the secondary form will pop up so notice our first form is inactive in this inactive you are not allowed to use the first form instead our focus is the secondary board so the secondary form will be usable if you close down this form so that's the purpose of modal instead of showing only the uh instead of show method we use show dialogue to make sure that the focus of the user is on the secondary form so if they need or he or she needs to finish processing the transaction or adding or editing a record before it can proceed through the second part so that's the purpose of show dialogue so sometimes the user doesn't use show dialogue instead the show method only so it depends upon your uh program or implementation of uh your program so let's proceed to the net okay let's close this first so our focus right now is moves to the add form okay so how do we add a new record let's double click our save button okay so first the user requires to input the needed data for the form so after uh inputting the new data he will then or he or she will be using the save button to save the inputted data so first uh let us try to identify uh what particular vendor uh the new record will be connected so to do that let us first load the records coming from the vendor table so to do that i have already created uh from your team plate a method to display or fill the contents of the homo box it is now the uh code that i develop or method that i develop so this is called fill combo box so it requires uh the sql command to retrieve a specific record from a table so the value member allows us to identify when a combo box is selected what particular record to be identified and when the user runs or displays your form what particular record will be displayed in your combo box okay so this is the value and this is the display so this will now the contents of this one a specific column that will display records or loads into a combo box while this one when you select a record from a combo box what particular identifier that will be used in order to identify selected item in your pocketbox so now this particular method is the same with our implementation of our previous method the display query or the query to run the query so what's the difference instead of returning a data table in this case we use the data table to ah by the way this one we use the data table to load the record into your combo box so now as you can see i have four arguments the sql command the value member the display member and the name of the homo box so when you call this method you will be calling the combo box to insert the record coming from the database and you are going to set the data that is being uh inserted to the humble box or the data source for the combo box the value member this will be the identifier if the record is selected or one of the content of the combo box is selected what will be the value that is representing the particular selected data in the display member what particular record that will be displayed when the combo box is clicked so let's try to display our record coming from the combo box okay to do that okay to load the record you need to hold the class so cls database [Music] okay cls database that fill combo box okay so let us try to select the vendor select so as you notice we have this record here so when you click down the vendor so we need to display the record or the name of the vendor this will become the display member and this will become the value members which means in your combo box when you select a particular name of the vendor you will it will be represented by the vendor code as a display uh value uh yeah that is value member so we need to be uh display the name and the vendor code okay so let's try let's go back okay to select masteries from for this will now the query that will be executed so for value member when uh your record from a homo box selected what will be the representation of that record so we code okay why we code because when you add a new product it is required that the product is being connected using the vendor code as you can see in our table okay as you can see in our table this product is connected to a particular vendor so this will now be the identifier which vendor is connected to this product so that's why in our program we will be using vendor code as our link to that particular product so render code next display member what partial column that we need to use to display the record so this will be the vendor name and our fourth uh parameter that is required for the method is the comboback name so cdo vendor okay so when you run this application it will now automatically populate your form with a particular record coming from the vendor so just click start new and as you can see i have done the records of all the vendors coming from the vendor table okay so let's continue close them down okay now so let's go to save okay so to add allow your program to add a new record all you have to do is to write statements so when you use statement uh you will be uh executing the command insert into insert into command in database management allows you to add new records so let's start by adding new records later we will try to validate the text box but for now let's try to implement insert impossible sql okay let's start declaring a variable first string there's no mysql command equals okay sql equals let's put that sign so that will be multi-line okay so this is the command or the syntax for insert into so to write insert into command all you have to do is to insert those commands into strings insert into the vendor the table is paradox okay now you need to specify a particular column so in this case i will be identifying the columns for the product so in our info we have this list of uh columns so let's copy paste copy copy description copy oops for in date we will try to create a function that uh retrieves the current date of your computer so that we no longer allows the user to add since it's obvious that when you add a product automatically we need to retrieve the date already so no need for the user to input the current tape so quality quantity on hand here is all about the stats minimum beast price paste and of course the discount okay last but not the least the vendor code okay so as you can see in our table the product is reference to the vendor code so that's why it's required it's not required that we need to identify which vendor is connected to this product so that's the purpose of our combo box okay now uh what's next so you just identify the column for that particular record so now let us identify the value that we will be inserting to that particular field so values so this one okay so let me copy paste this one so that they will be it will not be confusing for me too much column to a particular record so which means this one is for when you insert a value you are guided with this particular column when you insert a record to a database if the data is a string so just identify the column of the database let's say this one is barcart which means we need to enclose it in a single code okay something like this okay so this is for vendor code okay and then the record is came from the text box so you need to perform the following action so first you need to write the text box so you need to add two double quotes so that this string will be closed and this is the opening for the second string second to plus sign for concatenation then we add the name of the text box to the center or the blank line which means in this particular single codes we'll be inserting the contents coming from the text box okay so this will replace the defined screen to the contents of the text box so two double quotes since description stream records okay two plus sign and then type the description.txt description so the contents of this column this came from the screen also uh okay so let me use the second line so that it will be not confusing at my part okay that one okay okay next using a file quantity on hand i think this is a numeric so no need for single code so i will be closing this down so that this string will be closed and another opening quotation for this section of string and then i will add two plus sign okay and psd uh quantity [Music] perfect okay by the way i forgot to mention that we need to mention in this particular insert into the date okay so i forgot so let us copy paste the date okay so go which means uh we'll be inserting the date so let's enter and let's try adding another uh date okay so to do that to insert the date we need to get the current date of your computer so we'll be using the date and time function okay so that in time that now to get the current date now that string to stream okay in my sql database the format updates is you need to properly format the date to match the format of your database so in this case our date is it has a corresponding equivalent format in this case we have year month and day so therefore when you insert a date into your program or into insert query you must also format your data according to the database format so in this case uh yyyy two m's per month and two days for day like that okay so now uh let us add the comma by the way dates are enclosed with single code so do not forget to enclose them with single code so i will add in single code here okay now close down that okay so now next is my quantity okay then followed by since uh p mean field is a numeric so i will be closing this one and without codes and of course new line and replace this with ext okay minimum or the order level the text okay [Music] okay so now i think this is properly terminated so this one since this also applies so let me add two double quotes for this string and for the string and two plus sign to use the text box uh twice okay okay now for discount i will perform the same method for two double quotes two plus time and txt discount later we'll be implementing what we call foreign validation so next for hinder code okay let's close this one double code scope plus nine and cdo vendor dot selected value so if ever the user selected an item from our textbox or from our combobox the data that will be inserted in our database is the vendor code instead of the vendor name since in our table products the data that is being required to insert the product is the windowpoor okay so this is now your command okay so let's try to perform debugging to display to display the particular record when we try to execute that particular command to use the debug method you need to add the following line using okay because uh by default the debug method for our csr is not using the uh library so [Music] diagnostics okay so always ensure that when you execute commands like this one preview it first before executing them to your database to minimize confusion or to you know you will not encounter any errors later okay just right now let's try to test it our script or our alexis sql command is complete without any missing commas and etc so let's try so debug print is excuse okay now let's try running this one okay [Music] okay so now we just click new product let's say one one one test quantity 100 level five price let's say 100 and percent is six so let's try to use other select vendors for the and supply so same okay so if you want to examine the output all you have to do is look for the output window okay so now let me close down some other window okay so as you can see i have now the statement here so if you try to examine the code description okay the values that will be inserted to our database are the following the product code the description the date of insertion the price and so on so as you can see this is now the code of the vendor selected so if you try to identify from our database that we uh to check so the d and e okay so i think we successfully identified the selected vendor coming from the selected value coming from the combo box two one two three one okay so now our statement is actually correct so now let's proceed so how do we execute this command into our program so to execute this command so we will be using the same implementation we do but we have a small changes okay so to execute that all you have to do is to run jquery execute okay so we need to develop a new method to execute insert update and delete so in this example we need to create a new one let's say uh method execute this method is used to execute the create for the insert input the update and delete the only different method we use for selecting is uh we have separate method for selecting instead of this one we have the same methodology so okay so let's identify so in order to execute that query let us copy paste our code from [Music] here so i'm going to copy code or copy the my codes for statements coming from the retrieve data so start from using and of course you need to okay let's minimize this one okay so now i'm going to copy paste the entire contents of my method uh the method name is data table peric data so from here to here so this is not the open in the close so copy and paste them down to our new method so i'm going to name our new method as public so that it will be accessible outside this class static i will be using boolean so that it will if the implement is successful if return true so execute sql query by the way let's provide this re preferred okay so query so we need to ask create an argument so that when this one is called your parameter will be the sql command so ql okay so when you develop a method with a return statement so it requires a return so okay so let's try pasting our code okay now so as you can see i have now this particular line so it will open or connect to your database it will open the connection okay so the difference now is instead of this one will be removing this one so what will happen is we'll be executing the command that execute non-query so this one will execute because the difference between execute and query and execute reader is execute reader returns a record while this one it returns true or false so which means if the execution is correct so we will be executing true so return true okay and of course this will become false okay so after while attempting to add squad or attempting to execute to execute commands in your database okay now let's try implementing this one so let's try it okay so test number one a new product one one one okay description test quantity 100 item by price is 100 3 then in the supply same so uh let's see if the product is already added in our database so let us refresh this item here refresh okay so notice we don't have yet ah by the way it's not yet done i forgot to mention that of course we just create develop a method but we haven't used it yet okay so i forgot sorry okay so to do that you need to use that method so uh in order to use that method since it actually requires to return a boolean okay so what you need to do is to write if statement so if we're going to check if the implementation is successful so that's why so let's call the cls class database dot execute query then pass the sql for the sql command and of course so if this one is successful we need to develop the message box so to do that message box that show okay record successfully [Music] save okay let's add description new product okay then of course we must inform the user okay copy the type of button so bottoms that okay okay and of course mrs box icon with the information there okay so that's it okay so let's try i hope it's actually working let's pay for it okay so now i have this form so let's add a new okay let's start one one one test one 100 quantity five price is 100 and this country is three percent and of course the dnd supply and same okay since it displays say successfully saved let us try to display or examine the database so refresh okay i don't see any test but it says it's actually what's happening here let's try to examine okay from this record insert into product okay so let's try okay so let's see what's happening here let me go back okay oh by the way i'm using the vendor that's why so my bad okay so now here's the product okay so i guess because of okay so this is now the product so we successfully implemented so let's go back to our program okay okay but as you can see i think what we need to do after the user successfully inserted the product we must close down this form and of course when we close the form this particular grid should be updated so this will automatically refresh so that the newly added record will be added to our list okay so let's try to do that okay so to do that we need to add line here and of course uh close down our program after successful insertion of the record so this that this post okay expose means for this close okay so now let's go back to our list and go to our uh button new this will pop up the form so after we pop up the form we add the record and after the form new product closes down we need to refresh the record so how do we do that so we're going to recycle a code coming from our forum that we use from displaying the product so to do that you need to go to your form load from your product list and copy paste down this line okay copy paste so this will now present the record coming from data grid it will reload the data coming from your record so when you add a new product it will be refreshed and let's test okay by the way i'm going to use two to two because one one one is existing so our pro our database will not allow to insert record with the same code because it's a primary key so it's we are not allowed to duplicate so let's say test number two quantity 100 the order five third and or let's see now let's use another supplier so then e save okay successfully save examine when i click the message box it will be uh close out and the form mu will be closed down automatically and your form is refreshed so that's how you refresh so this line is very important in order to automatically reload your record coming from the database so that the datagrid will be updated with the new rifle okay so that's the first part the second far part is how do we so let me close this one down okay so how do we implement the edit record so we have a new record oh by the way let's focus first on the new one okay so how do we check if the user is let's say the code is required so we need to insert the form validation to do a a simple form validation all you have to do is to add a condition that checks if the text box is empty or not so to do that let's create a method to check empty text boxes okay so start here so method check empty text boxes rather instead of checkbox text boxes okay so to do that we must create a method public static returns boolean it will return a true if the checkbox empty falls if the checkbox contain a data so check text box okay so xbox objects txt i will also add string the title so let's see if the food is empty so we'll be mentioning the product food is must not be left and so on so this is the title or the caption of the empty field okay so now okay of course it returns true or false so if string that is null or empty of course the pxt that text object so we'll be displaying a message okay that message box show [Music] of course we will add a capsule to our mix box required okay and of course message box uh photons that is ok only and of course the icon is that exclamation so it's a winning symbol okay now focus the text box start focus so that the user will be identified which textbox is empty so return true because the text box is empty if not let us return false okay now let's move to our form here and there so how do we implement this one so you need to create a condition to identify before you save your document you need to check first if let's say cnn base that check text box what is the name of your text box to check that fix the code okay then the name of this text for the name of the record that will be contained here is the product rather code okay so if this one is true we will perform a return statement which means this return statement will stop executing the preceding lines okay so let's try okay new product save okay so as you can see this is now the title of the field that is required that i submitted in it actually check if the text box is empty so that's effective so now all you have to do is to duplicate this one with other text boxes this is for oh this one oh sorry this one so this one this is for extra description description okay this one is for ext discount let's see discount value or discount percentage it's okay if your uh checking of empty textboxes is not sequential so what other things that we need to do quantity quantity 60 quantity or quantity value quantity value must not be empty okay also let us check [Music] if [Music] [Music] the order the order value okay another piece scott price what the price did i mention the price already okay price okay so since by default our combo box is selecting the first record so it will be i think it's not necessary to check since when you run the form it actually displays the first record so try to call the price okay so try to test okay so okay so it's now actually focusing my screen 3d 3 3 okay description s3 save oh by the way it actually saved because our form here contains value so we will just checking if blank but notice we have zero so how do we correct that so to correct that we need to create of course condition and check if the input is numeric okay so let's try to do that so to validate a numeric value so let's start by creating a new method so let's add a new method so method in this method we will be checking if the data is numeric let's say check numeric double or integer is still a numeric value so to do that public static okay so let's remove this one okay okay uh static let me maximize the space static boolean check check number okay check is numeric i think okay so we'll be using string the value to check and it will enter false okay uh in this particular example i will be using the double since i want to recognize if the data is double or three or integers i'll be using double so number okay so if double that bypass so the value string so value and of course and now so if this particular uh condition successfully converted my value to double it would return true while if not if not it will return a false which means the value is not numeric so return true okay else i will be returning false and return false so in this particular method okay so in this particular statement it will check if the data is numeric so let's try to implement that okay so let's check if open it is numerix again here this database that check is numeric so i'll be checking that txt that takes because i'm transferring the content coming from okay so if not if not you marry [Music] okay it's a return we'll be using the return that's why okay oh i forgot all right okay so let's double check okay now by the way if if it is not numeric so let's add a statement that informs the user okay and by the way let us modify this one instead of string value we use the text box so that we can force the text box right okay so that next okay now let's just copy paste this one let's also add some title string like that so that we can mention we can mention what's wrong okay okay there you go title must be a number must be a number okay um number okay so i will replace this one okay so ah let's also make sure the value that select fold so that it will be highlighted something like that okay so let's start ah i forgot to change okay so this one will be using that name of the text box then the name okay so [Music] okay so i have takes one argument okay so let's value okay check esthetic okay what seems to be the problem no overload check check it okay so that's not working right that's right okay so test number four but i'm not mistaken [Music] four four four uh test for quantity let's say we just input our numeric save quantity must be a number okay what if the quantity is zero so let's try to add another so we can just add conditions uh let's see that bars so we will converting the number quantity that text is greater for less than one of course we'll be mentioning that scrappy piece this one so why don't we create another way let's just paste this it depends upon your application if it requires zero order so we will be using this one quantity must be greater than zero zero in value okay so let's make the text box focus return object expression okay now okay okay return sorry okay so let's try what it test for instead numeric okay but instead zero save okay greater than zero so that's how you check the validity of your okay i'm not going to continue this so when you develop the application just duplicate this one so that other type will be validated so to make the our video short so let us continue next is we need to allow the user to edit or modify our existing record so first the user needs to select a record from the list so in this example we have this existing test item so how do we edit it so the user must select an item then click edit so how do we edit an item first you need to identify which record is selected when the user clicks this button so the button is edit so you must identify which record is selected second you need to uh identify from the second form which is this one that you are editing a record so you need to load the selected record coming from the previous form so which means when you select this record for editing the record must be present here so that we can modify the record and when the user is satisfied with the modification so they need to or he or she needs to click the save to update the record but notice this form so we'll be using this form or reusing this form as our window to edit the uh record so uh we must identify how this form would process request first we need to set condition that if this form is for adding new record it must contain blank field otherwise if this form process a edit or updating record we must load the existing record coming from the database so that's the logic about this application so let's start okay so go let's go to our list so let us double click our uh data grid okay so when you double click your data grid okay double click the data key nothing happens okay to make sure okay it doesn't work go to event and uh look for a specific event that triggers uh the selection of record for example in this case since we will try to identify selected content for selected content rather we must identify a method for cell content click so go to the properties of the data preview click the event and choose cell code and click just double click here like that and it will activate a method called the data grid view cell content click because we are trying to identify if the user selected or click the button edit or delete so that's why cell content click in some other case there are few methods that you can also use but in for the mean time let us use this method first okay so let's move so how do we identify if the record is selected first we must check if the user actually select third record so e means this is the grid view cell arguments which means you are trying to identify its pro row index e dot row index is greater than or equal to 0 which means each row in your data grid starts with an array or it starts with zero so it's a it's like an array started zero so is the user uh selected a row coming from your database if yes we must identify what partial record that the user selected so that when he or she clicks the edit that's the point where we identify on our database that we are updating that record so let's start by identifying description so i think it's really important to describe or to announce that the user that you are trying to edit this something like that so let's identify the product description okay so to identify the selected row from the data grid you must type the data review name followed by rows that rows of metal rows index which means you are trying to identify the index selected then address the cell meaning the whole loan so since our data grid view displays description on the second column so it starts with ra so zero this one is zero one so we try to identify that the cell uh cell one or column number two value that is big so we are receiving the column number two from the data group view so get addressed as one because i just what i mentioned so it's actually uh start with zero okay next we try to identify the product code that we are trying to edit let's copy paste this one in the database name addressing the row which index come from the row is selected and cell 0 this is the food okay okay now once the user uh identified the particular column let us now identify what particular button is click so in this case we have rows one we have this one two three four five six and seven so the six and seven column is dedicated to edit and uh delete so the column number uh six or uh in index it is addressed at zero ah five is for edit so we must condition so let's say we switch okay instead of so e that row index is greater than okay e dot column rather either column column index okay so which particular column is click so let's try to identify if the selected uh column is number five for index five which means we are processing the break we are processing and edit product okay like that because the button is addressed in the column number 6 or index 5. in order to grade so we must display the form product update product name the object because update plata equals the new frm okay and then [Music] model okay so let's try to display if we identify correctly but record to edit so let's just the purpose of this line is for me to identify if i properly selected or when the user select a record i properly identify it so run and let's wait for it okay so let's say select test number two edit okay so now i now be able to identify which part of the record is selected for editing now it's before i show the dialog box working now so next our next task right now is for us to be able to load the record to be modified but in our case our initial form allows us to input a new record so how do we uh make our ad and edit form determine if we are going to add or edit the record so let's go to the form event and look for form load okay like this one okay so in this case we must modify this load event for load method so that when the user clicks an edit button from previous form we can easily identify if the record is actually for edit so first we must send in this form that the record that we are trying to edit is this particular id so let's declare our variable first i'm using private so that the variable is actually used in this particular class for firmware screen uh pico e code class and i'm going to add an argument string picot here so that if this variable is blank which means our form is in add method for mode add mode something like that just if this is just an identifier if our form is in add or update processes so okay so let's try okay so by the way since we already add this one so let's modify our code here in our product list because we added an argument to show the form so therefore it expects that we must pass an argument so here we go so now uh in your edit i'm going to remove this text box and i'm going to send the retrieve or selected project code to the arcade form and of course in our new button i'm going to send blank which means this is for uh adding a new okay so let's go back to the right edit and go back to our method sorry let's go back to our [Music] third okay so where is my formload form load okay so again please if you miss the disc start the explanation of why we need to add this one go back to the previous time okay now our focus right now is in the firm load so we must now by the way after receiving this so we must check if so i forgot to write statement that if if p code is not equal to null meaning it has a value therefore e code be contains the value transfer coming from the argument okay so something like that so which means if this value has a contains for when this form is open it has a value of p code so it will be transferred to this particular variable so that when we go to the firm load we will try to ask is this form is for editing or adding a new record so that's the purpose of that okay so let's uh check okay so let us try if this p code that you try to use okay this is actually the variable from here you are asking if this is not null or not not equal to null which means your form is used for editing for possessed so otherwise uh that is used for adding a record so that's what it's all about now since my code is not equal to black because i'm going to edit the record so we will be creating a particular recording again then select uh asterisk from product okay where e food i'm preparing to this particular column here so since we transferred from previous form to the edit form so we try to identify direct order frequencies for the particular tool so let's [Music] see fire car is the data type of code okay close and now we need to use the variable here and the underscore code okay so now we are now trying to retrieve the record okay and now let's reuse the query data here with this one just use that so let us keep the record okay so data table we use uh let's say product equals database that so now this record will now contains a specific record coming from your database so if your product that rows that count which means we have retrieved a data we need to display it one at a time in a textbox therefore x equals so to display the product pre-column so you need to call in the data table product that grows okay and zero which means the first index of the table and followed by the following so in this case our column for the product is this one so let us copy paste that okay okay start to screen okay then let's do the same thing with other text boxes text [Music] we call that foreign next discount next is to change course [Music] for the number and discard of course we need to allow to identify which vendor is owned this particular product so we must okay so let's proceed okay now let's test run let's see what will happen okay okay so now this is now your form let's try to test or select test number three then uh did something might be wrong okay so now people okay so i have an invalid column which means okay i forgot to put the underscore here all oh i think it's working now okay so there uh notice that it's zero because it previously right for the field so let's try another effort say this one [Music] yeah uh minimum okay so let's add that particular txt the order that text so let's copy paste this line here e paste look for the reference minimum so this one is the field okay now in modifying record we need to make sure that the id is cannot be modified because that's the primary key that we need to disallow the user to modify so txt code that only was okay so let's try okay so that's the entire code to load selected record so this is now your so let me adjust okay so there you go okay so let me try to select the record this one is for art file incorporated edit and now we can successfully say that we can modify this one so this one is unable to edit because it does now we can only place one this one etc we can also modify that this one okay so now let us save any changes that we perform coming from the record so let's go back to our edit form and choose okay okay so now it's okay to have this one since even if you modify still but data is required okay now uh we have a separate command to modify a particular record so how do we identify if the command is fully added and the command is for editing so let us reuse our variable code this one is the variable we declared privately from above so if the variable from above or p code is not equal to long meaning this particular statements inside this condition will execute a update or else if the variable p code is equals to blank we will performing this one so this one is for inserting two this will be executed if the picot is blah meaning it's for adding a new record but if the record or the the form contains p code which means we are going to have a record this one is okay now how do we perform update so in order to do that you must again perform three okay so we have in sql command we have what we call update command to do that let me resize my screen so that it can be okay you again okay so to do that we must type the word update what what particular tablet we are going to modify product update okay we need to set particular value so what particular value are you are going to say so the first thing that we need to identify is the of course the prescription so here is [Music] discount and decode so that's identified at the time equals since quantity is a numeric so need for semicolon or a single code we are going to replace that with the value followed by uh parada the order level so the minimum equals it's also a number for no nico okay next is discount discount the purpose of doing this is for me to be able to identify what particular color that i'm going to modify then of course the recoil will be since we call this numeric so and of course we need to make a condition that we are trying to edit specific product copy understood was okay something like that now let us add the contents of it's the same with this one so let us you know or just do this two double quotes the first code is for this screen and the second code is for this thing to be open okay then two plus sign and the text box that contains the data description okay okay for quantity on hand notice we have an enclosed stream so we need to upload it okay of course open string game or you can just ignore this one something like that okay and then put two equal sign at follow trouble codes and last time something like that okay i guess this is not working so let us close this first okay i guess it's working now okay okay so now let us replace or add the context down from text box and so this would be the quantity that okay next let's open the string then of course we need to close down the screen first okay now let us people okay plus cbo this door that selected but we need to use this one two double quotes close the screen and open the screen to plus sign for concatenating and put the center the variable okay so now we are going to run the update command to into our database game how do we do that this one will do the execution so just reuse this one then successfully okay so how do we determine if we're trying to save new or update a product so of course you can just create another uh string so let's try message [Music] this case we this one okay now let's replace this with this okay so before we continue running the program let's go back to our list and let us copy this one from you so that we can also reorder or refresh the list if the user click the uh what's called that edit okay so let's try okay so let's try modifying this one with it okay so as you can see i have now the record for t33 test three so this is safe guard 100 this is just an example so we ordered 1 000 for stocks every other level 20 and the price of this is to say 100 discount nut so let's try to connect this product to thomas brothers so in this case this one will be replaced with safeguard one of the l the stocks will be 1000 the price would be 100 and the new vendor would be domestic products see okay i have an error so i have here by the whole so let's examine what's wrong okay so let's go to our form now i see what the problem is so the problem is it's actually near here so please make sure when you do this one it actually connects this string to that string here so make sure you need to put some space okay so let's do that again okay so let's test it edit okay we need to use gomez brothers save ah i forgot to sorry plenty of bearers it's actually products not product ah yeah i forgot to use to add you so it's uh anyway that's your remark okay let us proceed sorry for this delay okay make sure you type the correct save 100 no discount from his brother save okay so now let us try if this 33 is now safeguard okay three two three where are you okay so now oh what about the price let us see what happened with the price it's not actually modified okay i forgot to mention the price so that's why what's the price of p underscore price i think we just now will be working for that xd it's okay to uh make this query not in order just like what we do for based on the list of the table field so okay okay so let's try again i forgot to put drama which will correct the stick for the field okay so again let's try to modify the tweet this time i'm going to modify either right oh what's wrong ah i forgot to put the equal sign sorry okay so that's all about now let's go to our product list and when you press the delete button the record will be deleted okay so let's try go back to the method for data now instead of case 5 we use a6 x6 because the user click the delete button click ok now what will happen here so in this example i will be writing the statements to delete our record but of course we make sure that the user that will perform the action will is actually sure that they need to delete the record so let's put some uh ask the user a question if the action is actually or he or she is sure uh let's say message box let's show let's see let's put submitted this action will permanently delete the description plus let me make the screen work okay so this one will be try to identify the product name that the user will be okay [Music] also watch all this ask a question are you pure are you sure you want to continue performing this action wait a week on this one already have customers okay so let's put the hole here okay next is uh right up and then let's provide mesh box photons okay let's say okay so the user will pop a mesh box will pop up asking for a user to delete he or she will be our who's copy or cancel water then with blocks icon questor icon like that okay since this uh mess box is located in my fifth condition so i will be checking if the chosen bottle is equals to okay buttons all that now something like that all right so let's see what will happen okay let's edit this one first one did it okay so this option will permanently be the best one that's the project are you sure you want to discontinue performing this after ok handset products determine is the user select okay so if the user selected okay we must perform an action okay so how do we do that we need to create a query okay [Music] from rada where so we're trying to delete the product code so the code that we are trying to delete is the selected code from the list so you are going to replace block with [Music] okay so we're going to execute this one and to do this let us perform the query so let's do some trying cash so the purpose of trying cards is to uh capture any error while executing the record or but anyway we already have this string in our class so this identifies that okay so if the execute non-query encounters an error this will display this one by the way okay so now let's go back to our list okay so here is a base that executes freely so let's go so if this is working we'll be notifying the user that show the record successfully deleted of course iphone would be information that information something like that so that's the purpose of writing the photo or choices icon you know presentable or appropriate message okay for now after doing that of course since the record is deleted inside the database but not in your data grid so we need also to update or reload our existing record okay so i guess you already done that here it will automatically refresh so let's try it okay so now let's try to delete test one if you need are you sure transit oh nothing happens this one you believe are you sure yes record successfully deleted okay so now your record is now refreshed and your data grid is now refreshed so that's what you call a crude but anyway uh code means create we already perform the insert read already perform the select statement from previous activities uh update so we already performed that and delete so that's actually the for [Music] what we call that elementary commands for processes for record management so that's the actual processes involved in system development that allows us to manage records so i hope you learned something and i hope uh if you don't understand the things that i discuss you can also go back to okay always go back to the video and watch it again for uh particular things that you don't understand or just post your comments in my youtube channel and also in our lms page or quarter so i hope you actually understand and follow the sequence of my instructions so for review so we have already performed the needed task so that's all so thank you and uh have a nice day [Music] [Applause] [Music] you

Channel: lmsTV

Views: 453

Keywords:

Id: vnKFUpLPO_8

Length: 111min 23sec (6683 seconds)

Published: Sat Dec 19 2020