Easily Connect Your VB.NET Project to MySQL Database with this Simple Tutorial

hello and welcome to my channel in this tutorial we are going to learn a much simpler way to connect our vba project to a database so for the sake of this tutorial i'll be using xampp for the database functionalities so if you have for instance one map or even sql server running on your machine that is okay for this tutorial as well because the approach that you are going to look at is practically the same across the board okay all right so let me just minimize it so that we get to know what exactly we are supposed to do so i have already launched my visual studio ide i'm using the version 2019 so my interface is a bit different so depending on your version you might be seeing something different so here i'm just going to create a new project and then i'll search for visual basic here and then the list of options that are available i'll make sure that i look for windows forms app okay into bracket.net framework and then we need that you can see visual basic indicator down there right that's what we're interested in so i'll just hit on next and then for the project name let me say database database connection all right now the project location should just be the default the solution name as well should just be as it is because it assumes the name you've given to the project now with this once here i'll just make sure that i select the highest framework version here and i will just say create okay so let's just wait a little for our project to finish creating so we can continue okay now we have our project created successfully right now with this form we're just going to place few controls on it to be able to test our connectivity so let me just resize this from a little and then before then let me change some few properties pertaining to this form the windows the start position of this form should be the center of the screen um so that whenever this form loads it to be at the center of the screen okay now the formula style to let me set it to none okay and as for the font size for this form should be 14. so what it will do is that whenever i place any control on this form it assume the font size 14 okay so if it is a button the font size for that particular button will be 14 if it is a label likewise okay so i have increased the font size which has distorted the design somehow so let me re-adjust it okay so i think i'm kind of okay with this now let me place some few controls i need a label all right so just double click well you can do drag and drop and that should be okay i need a button as well so button i'll just drag and then drop it on the form and then i'll resize this to what i want and the text label the text property of the label let me just change it to database connection i think i'm missing the spelling so this is it okay now let me increase this font size to um you see it is 14 but let me just increase it to perhaps 18. okay now this okay now the test property of this button to let me just set it to connect and then i want the back color should to be something bluish okay the flat cell should be flat and then the back color also i want it to be something um bluish okay and the four color for this particular button i think should be white okay so let me see all right can i see why it's in there yes good so i'm i'm okay with this right good now let me duplicate this label once and then just paste it on the form and then change the text property of this label to x and that's will be like that and i'll change the for color to red for color to red you can see right in here i'm just going to custom and then select red from here now let me rename this label to lbl exits because we will implement the close functionality using this label here so let me change the kaiser property to hand so whenever you hover over it the casa will change to a hand okay all right with the button two let me just say btn mining button button btn connect okay all right so let's just start or debug this application to see what we've done so far okay so that's it that's what we've done so far okay let me implement the functionality for this close button and then continue to look at the rest so i'm stopping it and then i'll just double click on this label here and it will assign the click events to the label right click event here to assign it to the logos i'll just say that application dot exit so what this will do is that whenever i click on this particular label here is to close the entire application right good now let's box on to look at how and we can create our database connectivity the much much simpler way okay all right so we are going to make use of an item that is available and within the vb framework i mean that does net framework and that is specific to the vb.net programming language which is called a model so it is called a model okay a model now this model behaves like a class right where classes you can see that a class can can be instantiated right so a class can be instantiated so you can instantiate and can have a lot of functions within it like that or specifically methods okay so let me say methods all right now when it comes to that of the model it's kind of have similarities with the class but what it does is that you don't have to instantiate this model the instance is really one which is the model name and the scope of this model is public the scope of any model you create by default and vb.net project is public so it means that anywhere in your application you can assess whatever definitions or implementations you have within that particular model okay all right so let's go and then look at how we can add one so that we can use it to create our database um connections and functionality so i'll just go to my solution explorer right click on this project name and then go to add and i'll go to add new item then i'll search for a model so it right here you can see a model all right so if you can see us just search and then type model okay it will just bring up i mean the sorted ones like that so we select this and i'll rename this model to maybe database okay then i'll just hit on add here okay now we have what a model created successfully now before we work with the model let's add a reference of my sql database to our project by default it is not available so before we able to write mysql queries before we will be able to implement mysql commands we need to add the reference to our project so how do we do that there is a dll file that we need to include and include in our project okay all right so i'll just have to go back to um the solution explorer once again and now right click on it i mean the project name i right click on it and then select add and i'll go to um reference here okay now you can see that i've already worked with this file already so yeah you can see it's right here in the reference manager section here but then if you don't have us here like that it means that you need to browse to where it is and then add it to the project okay so i'll be including a link that you can download this particular dll file and then get it on your desktop and then include it in your project okay so i'll just have to select this and then so let me just say that if you have it for instance on our desktop what you just have to do is that browse click on the browser and then go to desktop and then select it okay so mine i have mine seated in this directory here okay so you can see it's just here i'll just have to select it like that and i'll click on ok so you see that there are some changes going on here within this reference manager thing within the this references should i see a model or yes within this references you can see that mysql library has been included in our project so we can therefore create sql command all mysql commands and then queries okay all right now that we've included it to be part of our references what we have to do next is that on top of this model on top of the model we need to import it so that it will be part of this model import mysql dot data dot my sql client this is what we are supposed to do so that it becomes part of this model here all right now there are some few variables we need to define so that we can be able to establish our connection to the database okay all right so first of all we need a connection instance of my sql so how do we do that i'll make the scope of this connection to be public so i'll say that public con as my sql connection okay now i'll say also that dim status as boolean all right and then let me just set it to false by default okay and then i also need a connection string so dim con string so meaning connection string okay is equal to then here i would have to specify the connection string to the particular database that we are using so i'll say server equal to localhost because the server that we are currently going to run is a localhost now the database name should be equal to let me say youtube underscore db then i'll terminate this now see the user of this particular database is equal to the root user and then the password is equal to empty so since the password is empty i'll just bring equal to and then nothing follows like that okay then i'm true with that now with this what is what it means is that okay this is our connection string whenever we implement this the system will be able to pass all this information to be able to establish a connection between our application and the database management system that we are having okay all right now that we have we have defined these variables let me just add one more that's um public um i as integer okay all right now let's go and then say that let's test or let's say establish connection okay so before we do that we are going to say that public db connect us so this should be actually a function so public function did we connect as boolean as boolean and i'll just hit enter okay now we are going to say that if the coin here okay if the coin here the connection here so if con dot stay equal to connection state dot closed all right if it is closed then okay so i think there is a problem here let me just get rid of this parenthesis and see okay good now this corn state is not okay so let me just say coin dot state let me say as new and then see something you understate okay all right so you're supposed to say that public con as new mysql connection okay all right now with this if it is closed then we can say that con dot connection string okay should be equal to this connection string that we defined at the top day all right so this connection instance the connection string should be this one right here are you okay good right now i can proceed that now the results or the status the status should now be reported true all right the statue should be true and then i can finally return the status so what it means is that it will run from here like that by default it is false when it gets here it has changed to true so it will return true so that's practically what is happening all right okay now this is what we've done right let's go and then test this on our phone so when we double click on this form okay what we are just going to do is that i just going to implement a simple if condition so if so if db connects so that's the name of our function that we defined in the model right so if db connects it means that if the result that or the status is returning is true then we can say that con dot open okay then i can just print a simple message box and say that database connected okay i'll save vb information else i can say that msg box not connected then i'll say vb vb critical all right and then when i come here whenever you open a connection you need to close the connection so the code will execute from the top to the down here and then i can say that corn does close all right now let's test this and see what happens okay so i'm just going to hit on the connect button here and see the message you are going to get okay so we are having a problem that is saying that authentication to host localhost um filled with unknown database good good good so yes we need to go and then what creates the database so i said i'm going to use xampp to manage or to implement the database functionality so i'll launch my browser and then i'll just go to localhost here and i'll say p php php my admin okay so you should know how to work with xampp by now because i have done tutorials already and then i showed how to actually um work with example okay so you can check out my other videos and you know how to go about it so i'll just go to the sql tab okay and then i'll say create database youtube underscore db and i'll just navigate set it down and then hit on go okay now that we finished creating this database let's go back and test our application so i'll debug it again and then hit on connect so we can see a positive message now it means that we've been able to connect to our database now to make it more interesting i'm just going to insert only one record okay and then let's see what happens so let me go back to the um back end of our database and say that let me show the query box let me get rid of this and say use use youtube underscore db now terminated i said create table and say users underscore tv users underscore tv meaning table right now open parenthesis now terminates this now say that laser is supposed to have an id so id which would be of the type integer by default 11 shouldn't be announced or not now should be the primary key and then auto increment all right and then let me say that either i have a username username username which of the type backer a length of 50 not now not now and then should be a password so you password user password okay i want to be a worker length of 50 and then not now okay now let's execute this query i just hit on go here good we've created what a table inside the database so i'll just expand this database here and you can see the user's table in there let's go back to our application now when i let me before that let me just expand this table so that you see that there isn't any record so username and password you can see there isn't any record right so let me minimize it and then say that when we check the status of the connection and is close then we open this right good so what i can do here is that let me get rid of this messages and then the else statement so i can say that using using using con using con so using con using this connection i can say that mysql command even this one let me just go and do it inside the model so um yes so let me say public cmd as new mysql command okay all right so let me just go back to this place and then say that cmd equals to new mysql command cmd is equal to new mysql command okay at some time turn this up let me just see what is up okay and then let me get rid of this and see something so new mysql command okay so we are not getting what we want all right so let me just get rid of this for now but it should work anyway so we can say that mysql um okay okay okay so it is actually like so so let me go back what i did here let me undo it and then have it like that okay then inside this place i'll say that using my sql command using mysql command wow so this is not really working so using con okay using con you can see that dm cmd is equal to new mysql command okay so um what is actually happening is that we need to import mysql to be part of this class as also import mysql.data that's my sql client okay now we can say that you using using cone okay using this connection you can see that dim cmd is equal to new mysql command good so you can see that now with this command it takes two parameters two arguments okay so the first one is the query which is inserts into users underscore tv and then we need a username username and then you password okay now the values will be so i'm using a parameterized query so at you name comma at u pass and then i'll pass in the connection as we also can okay all right now what i'll do is that i'll say that with cmd dot parameters okay that's parameters now hit enter now say let's add what value i'll open this i'll say you name and then we'll come back to this okay let's add with value and then that's you pass and let's go back to the form and then place a text box a test box which would be for the username and password so i also need a label so let me just go back copy this label and then just paste it now reduce the font size to um 12 or 10 then we'll do okay all right so this one will be username username okay and then let me just copy and then paste tests uh well this is not coming so let me just drop so test box and then i'll add it so this one will be password all right now let me just say use system password character for this um textbox here so what you do it it will do is that whatever password we enter will not be visible for us to see so it is maxed in a way let me just change this one down here to register okay okay so let me rename this textbox to txt username txt username and then this one is txt exe password okay now let me just double click on this once again so here i'll just say comma txt username dot text the trim so that i'll remove all the white spaces using the trim and function then this one to be txt password dot text the string like that then outside the with statement i can say that i equals cmd.execute non-query so i believe you know where this i is coming from we defined it inside the model so when you come here you can see this i here so anywhere in the application i can make reference to this i okay so i can say that i so i'll say if i is greater than zero okay if i greater than zero i can say msgbox registered successfully then i'll say vb information right here now say else msgbox could not register i say vb critical okay then finally i'll make sure i'll close the connection all right so let's test this one out okay so let me punch in grabs my name and nests that'll be my username and then my password will be one two three four and then i'll add e to it and hit on register so you can see that we had a positive or we have a positive message here let's go to the database and check whether it is true or not so go back here and then i'll just hit on browse once again and then you can see the records are in here like that so yes we've been able to achieve our purpose for this tutorial so you see that with few i mean i mean logics here you've been able to implement a database functionality unlike the um other approach i showed in my videos it would have to be i mean creating an instance of the connection every now and then but this one i just have to test this boolean function based on the result that is written can just continue and then implemented the database um functionalities here okay so you can do the same for select statement this one is an instead statement you can do likewise for select statement okay so this is the end of this um one here we are going to continue to look at more interesting stuff in the next context to be uploaded so if you haven't subscribed kindly do that like this video like i said if it is wet watching okay and i wish you happy learning until next time be good and peace bye

Channel: CodeLeap Academy

Views: 4,052

Keywords: VB.Net, Visualbasics, devvlab

Id: fYUQxIxu4Eg

Length: 32min 41sec (1961 seconds)

Published: Sat Sep 10 2022