How to Connect MySQL Database in Visual Basic. Net - Full Tutorial

hi and welcome to my sql database connection developed in visual basic.net now let me show you guys how this works we can add new data let's assume we want to add new data let's just click on reset and right there let's enter as follows so i'm going to make that up and the name happens to be grace standby address of stamina 83 stand more road there we go postcode telephone number there all we they need to do is add graystone straight onto the system as you can see there's nothing in there so let's click on our name there we go and that is graystone details straight onto the database itself all right the other thing we can do is we can print that i don't have a printer on my system but we can look at it all they need to do is click on print and you print it right away exit that what else can we do as you want to update the details of any of these let's select danny and in here we just want to maybe change the telephone number let's come in here and just change that to one for eight and uh that's the details of danny jonathan so let's take a good look at the telephone here all we don't need to do is click on update there as you can see the details of danny telephone details has changed to the last three digits one for eight okay you can exit out if you want and we can even search let's assume we want to search for maybe tony let's come in here and just press enter and there we go those are tony's information and click on reset and the other thing is we can even select all these details here there you see that okay but if you click in here let's assume you click in here you will end up with an error asking you to click on the sound idea where the arrow is okay uh what i'm going to do now is i'm going to take you guys straight into visual basic development environment i will put one of these brilliant tutorial together let's start a new tutorial i'm going to click on create new and right here i'm going to select visual basic and let's come in here and select next i'm gonna give it a name let's call it bb underscore mysql connector sql connect to there let's click on create there we go okay my development environment is ready i'm going to change the size of the form to 1000 let's say 1014 by 788 press enter and that's it now let's come in here go straight to the tools i'm going to select plane let's come right here there we go oh panel it's called here for this very first panel i'm going to change the size to about 996 by 97 and let's come straight to the back color here let's change that to cadet blue there now let's drag it right here that's fine i'm going to copy another panel i might just just copy this hold on to your control click and drag and this very one that i've just copied i'm going to change the color of that one to the back color back to control there we go and this one that the that is now change to control let's reduce it that much in that much and i'm going to embed it straight in here just move it up here i'm now going to copy both of them again let's hold on to your control just click and drag let's copy this one as well there and i'm going to combine both together and just drag it that much bring this one back in a little bit yeah just like that and just let's try that again yeah that's what i want okay i'll repeat the same thing for this now combine them and bring it down exactly and reduce this there i will now copy this very one again this is this motion okay let's copy it first before increasing it right now grab control hold on to your control draw click and drag i'm going to paste this place this right at the edge here okay now select both of those just drag it down okay let's bring it in okay just in like that okay so let's add let's add a label up there so come straight to the toolbox here grab a label there's that in there and i'm just going to copy that same label click drag drop it i also need text box come right down here grab some text box this one will do and i will also need one text box here or maybe reduce this little one yeah about that much and this other node another panel here yeah change the color to control there and i'm gonna add a text box there that would be for the search let's drop this down just make it multi-line drag it down okay now this very one i'm going to increase the size of those to about 24 the font size is 24 make that ball 24 there we go and move that up all right that's looking good now let's copy this hold on to your control click and drag this we have two now we have four and six there so right underneath here i'm going to add data grid view where is it right here click and just drag it here data review there we go now that is in place okay this is very one let's change that to about 48 [Music] and make that bold come up here and change the font so i'm going to change to and do that again right now okay might as well type control come in here for color change for color to get in blue just type it in so if you're looking for it there drag that up somewhere there and let's change the font we have on it so i'm going to change that to my sql let's come right down here okay my sqo connection say in vb.net there so that is fine so here let's add some buttons here we're gonna need maybe about six we have one two four and six we'll move all of this up so that can sneak in two more right there okay let me fix that anyway all right now the next thing is i'm going to change the data that i have in here so let's change this to id for information this is just about how to connect to database it's not about student id or whatever it's just about how to connect okay the next one is going to be sony and so on so let's speed that up okay that's taking care of now and that's how it's looking let's come straight to the property here i'm going to just select the form you see where the form i want to get it centered so let's go to start position click on that and get it centered center screen now back in here you see the toolbox i'm going to add tooltip and i will also add print documents and print dialog so i've added those three objects let's select this spring dialog and come straight to the property here you see where we have documents in the property let's change that to print document one okay that's taken care of okay all of these i'm just going to select them individually and come right down here see where we have where we have the search i'm just going to type in there search and this is going to be i'm talking about the tooltip that's going to be odd add new change that add here update delete reset print and the last one that is going to be exit okay and i've i've also changed the variable names for each of the component that's bt and exit bt and delete and so on you guys know how to do that that's no big deal that is it right there the names have checked that changed all right and the same thing applies to the text box they all get names that i have on my labels here there okay that's fine now the most important part of this project is how to connect to the xql mysql database okay i'm going to go straight to the tools right here click on the tools let's come right down here you see where we have nuget package manager let's come down select manager nuget package for solution click on that and this dialog box should pop up now select the browse click on browse now right in there on the search enter my sql there we go that is it so i'm going to select this very first one click on that and this dialog pops up now this check button here just make sure it's checked and then click on install there we go now my sql data has been installed to the system so all you just need to do in here is to click on ok there we go and this line sensor agreement pops up just click on i accept there now mysql is now been installed onto your system and we are almost there now all right mysql is officially installed straight onto my system so i am going to close the new solution i don't need that anymore okay so the next thing i want to do now okay let's start work with the data connection so i'm going to double click on the form itself and the first thing i want to do now is i'm going to make sure right above the public class from one let's import the i'm going to import my sql imports imports that's where an s my sql there we go that's the right there because i've already installed it that's why i see i saw that dot data and it's going to be dot mysql client there and the other library that i'm going to import let me import the following that is going to be microsoft visual basic application let's say dot baby application application services then what about windows services let me grab that of windows services as well copy that and just paste that in here dot now that's going to be microsoft that win 32 okay that's fine okay now right underneath the public class from one i'm going to declare the following theme my variable i'm going to call it sql con as name and that is going to be my sql my sql this let's let's just enter it myself my sql connection where is it there we go that's one down i also need another one for the command so let me copy all of these let me change that to command instead of typing paste and this is going to be my sql command let's change this one to command and here that will be my sql command command let's see there we go now what else do i need i need to read as well sql reader so i'll call these sql rd the else sql rd is going to be mysql reader data reader actually data reader there we go all right let's see it's nothing it's not an object so i'm going to get rid of the name all right what else do i need let me grab a table so i'm going to call that sql dt data table so that is going to be new okay it's an object data table and finally i'm going to go for dim sql data adapter so let's change this one first to sql or i'm just going to call it d c uppercase a data adapter as new mysql data adapter okay yeah that's fine okay now underneath let me connect all of these so the first one dim server so that is for my sql server as string equals that to be local it's a local host there and the next one is going to be for the username game username as as well and the username is just root okay now the next one is going to be for password let's just copy paste that underneath here password and this is going to let me change this to password and the password let's make that up you can call it admin that'll be my password and in here and change that the database name database name let's make this up we just call it mysql connector or just call it yeah okay my my connector all right that's going to be the name of my database my connector that's all but the thing is i don't have a database yet i'm going to create that together but we i also need this private bitmap as bitmap that i'm going to be using that for my printing okay that is all done so let me copy this very name my connector and i'll then open up my database that's my database here but i don't have my connector there i'll click on the schema there click on the schema let's click my connector place the name i've just copied and i'll click on apply so i'm now creating my connector click on apply again and finish so let's look for my s connector that's it right there okay now right click on the table inside my connector and select create give it a name the name i'm calling it exactly the same name my connector and you see this arrow will drop it down now the very first field i will have in there let me just make that up i'll call that let's use uppercase student it's an id now i'm going to make that the data type is integer and the primary is the parameters key there's no non value in there it's got to be unique i'm going to leave this out now because i'll be entering my own details in there so i wanted to make it auto increment now i don't want that so in here the next one i'm going to call that first name and right underneath here let's call that soul name now what else do we have we have surname i'm going to make this address and the next one is going to be of course good yeah okay and followed by telephone number there we go that is it all done okay in total i have six fields i believe yeah okay let's click on apply there we go click on apply again and then that is it it's successfully created click on finish so i finished with my sql whatever table so if i go into my table here okay select my sql that is your idea click on the table there's nothing there okay so let's minimize that i'm back into our visual basic development environment all right so i'm going to go back to the form of the visual basic development environment let's start to work with some coding the very first one i'm going to take care of is going to be my exit so for my exit i'm going to declare the following dim i exit ask dialog result there and this is my dialog result i'm going to be using that to validate whatever selection the user made when it comes to exiting the program so i exit equals my no message message box dot show and what do i want you to show i want you to show confirm if you want to exit okay that's the very first one i'm going to put a comma the next one is going to be the title so i'm just going to call that my sql connector okay let's come out of it enter another comma and that is going to be message box buttons dot yes or no comma the next one will be message box icon dot question there okay now that we have all of those in place let's come right here and press enter and i will now use an if statement if i exit equals dialog result dot yes then application dot exit there guys so what i've just done in here i declare a variable called i exit and this i exit has the following message box which you guys know how to create right now then i have one parameter here the second parameter my third parameter on my third argument my fourth argument so if the user select yes then we exit out of the application else we just stay within the application and this the message box icon that is the question mark that will appear on my message box and that's all there is to that so i'm going to run it let's run it so that you guys see what i'm talking about run there we go guys so i'm going to click on exit no i don't want to exit yes i do want to exit you see that guys so that is how the exit button works so let me exit out and the next thing i like to do now maybe i should put that right underneath here underneath all my variables and so on i'm going to create a private it's a private function here and this function i'm going to use it to to manipulate the connection of all these strings my database so i'm going to call it update table okay and inside is my function the first thing i like to do is you see this very object that i created in there i'm going to call that same object in here enter that object indeed and i'm going to say dot connection string this connection string is going to be is going to be used to manipulate these strings within this system so connection string let's say server then plus server plus i'm going to enter let's enter a semicolon there then the next one is going to be the user id so let's say user id close that and let's enter another let me just enter plus india let's say user id equals plus and that is going to be username okay the server equals server it should be plus here okay and there's a comma there this equals user and this user should then add comma here as well then let's enter another speech back then the next one is going to be for my password let's come come down here and just enter password you can see before i come in down before i actually come down to the next row i sort of like enter a space there okay so if you want to do that you must entire space and i'm gonna just enter plus and so on just more or less repeating the same thing over and over again so in fact i'll copy all of these paste it right here and just change the text content now this one is going to be password and in here this will be password as well okay there should be a plus here though okay now we have that done the next one here that is going to be the name of the database itself so let's enter database database equals this base okay grab that and just paste that in here equals database and all of this we have to go there now that is taken care of now let's come back okay now asking let's ask this guy to open up so come back here just dot open and i will also add the command here sql command that i have in the sql command dot connect the connector that i have up here so that connect and what are you connecting equals sql connect this one now let's grab the command again so i'm going to add the command to select from my database so let's say command sql command dot command text and that is going to be equals let's say select select from now we need the name of my database if i come up here that is my database right there even copy all of that copy it if you guys can still recall my connector is called so i'm going to come in here and just paste the name of my database there there we go enter that in there and you see all of those errors are gone okay that's taken care of now let's do one more thing we now need the reader this very guy here grab the reader okay now underneath here is a reader equals sql command dot execute reader okay that's fine now the next one we want to go for is the data table this very one this right there that's the data table okay i'm gonna grab that and i'm going to place that right underneath here underneath the reader so the data table itself dot when i want you to load so load whatever the reader has got so that would be sql reader okay now that that is loaded once we load whatever we have inside sql reader we can close that sql reader dot close and we can also close the connector sql connect that goes there enter parenthesis and finally we now need to assign all that we have straight into our data grid view here this data review i think is called disagreement one copy that come right down here and just paste that underneath here disagree we want that data source and where is that data source from it's going to be from the sql data table there that's that taken care of just like that okay as you guys can see if you notice the function kind of like grayed out because i haven't used it so i'm going to copy that very function and i'll call that function inside my phone load so let's come in here double click on the phone and just paste that in there the form we actually initialize the database itself okay just one thing let me show you guys something that is the name of my table data that's the name of my database and that is the name of my table as well you can just call your database and your table but i just i did the same thing but for the information i can delete i can just delete that okay because they both have the same name anyway so there won't be any problem and that is it right there all right so you can do it that way or you can just leave it the way it is so now the next thing we want to do we need to now our data let's go to add because the database is empty there's nothing in there so even if i run the system there'll be nothing you won't be able to see anything because i haven't entered any data yet okay so even if i run it now nothing will happen you guys will just see a plain data review so i can run it let's run it and you see what i'm talking about just waiting for you to pop up there we go look at that it's working that's fine but i need to be able to add data once i add data we will be able to see whatever data entered inside my data review so that's fine so i'm going to exit out let's double click on our data now the first thing first thing i'd like to do is i'm going to come up here and just grab the following let's just grab all of these and just that for now let's grab that copy and i'm gonna bring it right down inside my add new data paste that in there okay i have my server i have the user id use the name okay i have the password and the database that's good okay now let's use try car share just in case if i run into any error try and okay right in here we have a cache i'm going to grab all of this and let's put that in here okay now that error is gone with the cache message i can just enter let's come in here i'm gonna grab all of this and just paste that in there that would be my message if there's any error drop that in there and let's say that that's the name that's correct we can just put a message in here or i can get the system to give me a message i'm gonna call that xe dot message okay and you give me a message so that's fine and in here i'm going to change this one to information dot info all right that's fine and let's put change this one to okay yeah that's fine so the try conscious taking care of now in here open up the data okay open up the database and here let's use a query here let's get rid of these ones i'm going to get rid of this okay not to confuse you guys let's get rid of all of this i'm still gonna need it so there i'm gonna call that mysql query equals insert let's put that in there here instead and why do i have an error here did i not declare that okay let's go up here i think i did not i did not decline so i'm going to come down here and just again query as a string yeah so that arrow should be gone now where is it yeah that error is gone okay so i wanted to insert into my database this is my database here it's called my connector so i'm going to grab that and just paste that right here my connector.connect that's the name of my database table and the database database and the table okay and indeed the following i have the following data so let's see let's go in there have all of this student id first name and so on so let's enter those in here so the first one is going to be 390. student id okay that is my very first one then add an i'm going to enter accommodating followed by first name surname now surname let's see how did i write the postcode in here let's see okay with uppercase c postcode comma and finally i have a telephone there so that's taking care of now let's say the value value that is going to be say and what is that going to be the name of my very first text box let's see the name that is txt yeah dxe student id so india issue has to student id in there dot text and the next one is going to be let's say and the next one that will be this comma here and we also need and the next one that is my txt first name dot text yeah and so on okay so i'm going to repeat that for for the others so i can just grab this and just copy and paste then change it around okay this is sworn in okay and grab all of these and paste here get rid of this so name the next one is going to be address changes to address and so on guys i'm going to speed that up and there guys so what i'm going to do next is let's call my let's say the update data yeah it does it updates table i need to call that as well and one other thing is supposing we have an error let's get this one to finally dispose of any error i'm going to say finally okay and that will be the connector sql connect dot dispose all right there and that is all there is to that so i'm going to try this out so far i don't think there's any error here so i'm going to try it out let's run it and see and take it from there save run there we go false achievement there's no error nothing so i'm going to add some data in here let's say the very first one is going to be that as the student id and the name is let's say james james dean an address of james dean number 19 let's call it wallet avenue and okay telephone number right so let's click on add yeah it's working look at that that is fine let's see if i can duplicate this very student id no no way no way jose that's good i like that supposing i enter to india and just that works okay and now james is there let's change this to sammy sammy let's see south oh yes sunny salting and change all of that change telephone number as well there we go oh i need to change the primary key naughty boy i shouldn't have forgetting that yeah that's it i shouldn't have forgotten that there we go that's fine okay so we can now take care of the rest of them nothing is happening now so this is working and this is working as well all right let's take care of one two three four yeah now let's take care of one very easy one double click on this guy here i'll click on that and i'm going to enter the following lines of code for the reset so those are the lines of code that will take care of my reset button right there okay okay so if i run it now let's run it and you guys see how those lines of code works so i come in here just enter whatever value in here and try this out as well reset that is it taking care of so let's take care of the update so let's come in here double click on update and let's speed that up in fact there we go those are the lines of code for my updates right here from here take it down here okay those are the lines of code for the update so i'm going to show you guys how the update works but before we can get the whole update to work we need to right click on this okay i'll select that and let's come straight to the property here and we click on cell click so double click on that or press enter i would then enter the lines the following lines of code for the data preview one cell click that is it those are the lines of code for that as well okay have a good look at it i just want to speed this up this is for update let's move that up and here that is my reset lines of code for reset okay so let's run it and you guys see how that works okay i'm going to select one of these there we go unless say we want to change any of these okay let's change the avenue to road there and take a good look at this so i'm going to click on update there we go let's just change the avenue to road so that is working hours that works and this works so we need to just take your delete and print so we're gonna have to take care speed that up as well double click on delete so for the delete i'm just going to use a very simple line of code let's use a follow for each for each row as data grid let up review row there we go and data grid v1 v1 dot selected rows and last selected draw we want the data grid view copy that press enter here let agree view dot row rows dot remove just to remove whatever we have on our um there that's it so i'm now going to also call my update function there we go there so i'm going to save that does that simple line of code i'm going to use for that data grid yourself so run let's see okay i'm going to select one of these and let's change this name to hello helen dean no helen moon and changes as well let's change this change this there okay that's a new data so [Music] oh primary key is the same yeah okay update we have l in there okay let's change this around again and there so i'm going to delete that so let's say delete yeah it does it done okay that is fine all we need to do now is to take care of print the exit so now we need to take care of this let's come in here double click on the print button and there those are the lines of code for the print button there so it's more or less like you're taking a a print screen of the data group yourself so let's do one more thing and the print document they double click on the frame document referring the print document to the data green v itself and that is it right there okay guys okay that's the variable that i declare right up there now you make use of it all right okay and let's take care of the search as well so i'm going to select the search here let's go to the properties yes make sure you select event and we want key press right in here let's double click on that and right in here that's the key press the event so in here let's enter the following lines of code for the search there so those are the lines of code for search so i will now run it so let's save it all then just run it and see there we go so i'm going to click on print first let's see how the print works there we go that's the print right there okay so if you want to print just click on this i don't have a printer on my system so there's no point doing that and that is that is it for the print let's take care of or let's have a look at the print function at the search function as well i'm just going to enter maybe helen there let's enter helen and press enter there we go the two elements data that appears in there okay so that is it everything works exit works so you can see there okay let's run it one more time so just before that that is my search and we have the print functions search print and the print button we have delete working from the data review when you click on that update then we have reset and that is the art data so let's run it and see now and we can just call it the end of this tutorial there so i can select let's select james there we go and reset and we can add another data in there and the name let's say sunny [Music] jonathan and the address number four rolls down [Music] telephone number [Music] okay all they need to do is click on add there we go and the details of sun is officially added onto the spreadsheet or data review so with that guys i'm going to call it the end of this beautiful tutorial i suppose you guys enjoyed and please do subscribe to my channel and you can also join to become a member of the channel that helps a great deal if you guys are a member of the channel it only costs one pound 99p per month so thanks for watching and you all have a nice day now bye for now

Channel: DJ Oamen

Views: 89,467

Keywords: Connect MySQL Database in Visual Basic. Net, Create an interactive MySQL Database Connections, How to Connect MySQL database in Visual Basic. Net, How to Connect to MySQL Database, MySQL Insert in Visual Basic. Net, Tutorial of MySQL in Visual Basic. Net, MySQL database tutorial in Visual Basic. Net, Using MySQL in Visual Basic. Net

Id: JmZv-kUIoVw

Length: 56min 17sec (3377 seconds)

Published: Sat Oct 31 2020