MySQL VB.NET Tutorial 12 : Show database values in Table or DataGridView

hello guys welcome once again in this video I'm going to show you how to load your database table values in a datag grid view or a table so let me show you what I'm going to teach you today so I have this uh data grid view control in my form and when I press this load table button it should load all the table values from E data which is present in my database this is the table and I want to show all the table values with the column names in this table so whatever table values are present in this e data table in my MyQ database I want to show in this data grid view in Visual Basic so how can I do that or how can we do this uh using visual studio I'm going to show you so let's get started what I'm going to what you need to do first is you need to take a data grid view from here so you will find this datag grid view control from here and just drag and drop into your form it will look like this okay so I have already done that and just take a button from your T tool box and I have changed the name of this or text of this button as load table and I have changed the name of this button as button load table okay now what I need to do next is I will select this button and I will double click this button and in here I have written a code to load the table in the data grid view right so let me explain what I have done these two lines you are already familiar with I think because with uh every in every video I use them these two lines are used to connect uh my Visual Basic project to the mySQL database so just declare MySQL con is equal to new mysq connection and then just copy this mysq connection do connection string and give your uh credentials of your my skill server and if you don't understand this you need to watch the first few videos of my playlist of this uh MyQ plus Visual Basic so just write these two lines and these this is the new thing to um connect the do grid view with the uh with your uh database table so you need to declare three three uh variables or instances of these classes first declare dim SDA as an new MySQL data adopter second declare dim data DBS data set as new data table and third variable declare them B source as new binding Source okay so this will help us to get the data from the database this this one and these two uh variables will help us to bind our data which we get from uh the mycle data adapter to the data grid view okay and then give this try catch block this also I have already shown you in the last couple of videos so if you want you can just copy this skeleton of the the TR catch block to catch any exception when the query occurs okay now in the try Tri block I have just opened the connection with the mySQL database so just copy this MySQL connection. open this will open the connection with the database right and in here I have declared a string variable called query this query uh will contain the query which you want to uh perform in the database right so just write this select star from your database name dot your table name my database name is database and my table name is e data so that's what here I have written select star from database. E data and then just copy this command variable which I have declared above I have already shown shown you in the last video that you need to declare these Global variables first is dim MyQ con as MyQ connection and them command as MySQL Command right so just declare these in the global variable I think you have already have them if you are following these videos and then write that command variable is equal to new MyQ command command and just pass your query which is this query and just pass the my connection which is this one so but just pass two argument in this my MySQL command function one is this query and second is MySQL connection right then take this SDA which is my data adopter variable dot select command so just write sd. select command is equal to command so this will perform your query right and then in the second line what we are doing is we are writing SDA do fill and in the bracket we are passing this data set variable so this line will do is it will first line is performed which is this query and just taking all the values and then this this line will fill all the all the uh values which are coming from this query to this data set right and then just write B source which is your this uh binding Source variable do data source is equal to DB data set so now we are filling our data set to uh The Binding source so that this binding Source can bind the data set and and your datag grid view right so just write B source. datasource is equal to DB data set and in the next line just write the name of your DOA grid view which is the name of this DOA grid view which is DOA grid view one dot data source is equal to B source and then the last line is SDA do update and in the bracket just pass this DB data set here so this is the code you need to write in order to fill your datag grid view with the myql table values if you want just pause the video and just copy all this code right now we are going to run this program and username and password and now when you just click this load table button this loads all the columns from your e data table which is this table and it loads all the values in this datag grid view now suppose you don't want all the all the values you just want some of the columns for example you want the column let's say Eid name surname and age these four columns you need only and you don't want to show all this username and password and other columns in the datag grid view this is very simple you just need to change the query just write instead of Select star you just write select Eid which is the name of my first column do name comma soame comma age so what are these four things these four things or these four um uh values are the column names of my first for column Eid name surname and age right so just write the name of the column separated by comma which you want to show in the datag grid view now once again when I run the program and when I click this load table button Now it only shows only four columns right now the next thing is to load the table for example what you want is as soon as you log into the second form or as soon as you load the second form you want to load all the table without pressing this button okay so what you can do is you can define a new sub so I'm defining a new sub just just above my end class code I can define a new sub or I can define a new sub above this uh button load click button load table click event anywhere you can define a new sub just write private load table and I forgot sub so I write here sub and just press enter here so this will create a new sub for you and in this sub or sub routine just copy all the code from your this button click event load table button click event and just copy all the code from here from start to end and paste it in this load table sub right and next what we are doing going to do is just copy this load table sub from here and just click your form anywhere not any control but the form as a whole just double click it and at the top just paste this function load table function and oh okay I don't need this semicolon so just paste this uh sub here here okay and when now when I run this program and when I log in and as soon as my second form opens the values are loaded automatically and I don't need this load table button anymore okay so this also you can do right now the third thing is suppose you add a new data to the database so let's see when we add a new data right now we have 14 entries in our database right I want to enter employee ID 15 some name some Sur name and age and I save it into the database I save the data data data is saved but you see here this table is not updated when I uh enter the 15 15th employee ID and this will or this employee entry will come here when you click this load table button so what this load table is doing is just refreshing your uh query or refreshing your this load table code will send a new query which will uh uh give you all the values from the database so this load table is also working as a refreshing refresh button for your for your code so when you click this now you can see the 15th entry in the datag grid view okay and in here in the database also you will be able to see when you refresh the database you can see the 15th entry so this also is Handy uh function this load table whenever you save the data and automatically you want to refresh your data grid view what you need to do is just copy this load table function or sub once again and go to this U Save button click event and this code I have written in the last videos when I have shown you how to save your data using your Visual Basic Windows Form application in your myc database so just go at the bottom botom of this code and paste this load table sub what this load table sub will uh do is after you save your data in the database it will perform this load table sub and what this load table sub will do is it will refresh the query it or it will send the new query to the database and it will refresh your data grid view as soon as you uh save the data in the database this uh function you can also paste in update button click event at the last and in the delete button click event at the last of your sub routine so now every time you perform any action your data grid view is refreshed automatically so for example I want to uh delete this employee ID 15 what I will do here simply I will write employee ID 15 I will delete it and as soon as I delete the the I press this delete button this will be refreshed automatically and you will be able to see only 14 employee ID so let's see I delete it and your data grid view is refreshed automatically and you can see here you have only 14 employee ID and once again if you want to add one more employee some name and some name and age and save it and as soon as you save your data it's there in the data grid view so in this way you can load your data grid View using your Visual Basic Windows Form application from the table in the MySQL table I hope you have enjoyed this this video please wait And subscribe and bye for now

Channel: ProgrammingKnowledge

Views: 215,020

Keywords: Combo Box, Text Box, Visual Basic .NET (Programming Language), Visual Basic (Programming Language), Database (File Format Genre), MySQL (Software), Tutorial (Industry), Software (Musical Album), Deleting, VB.NET, .NET Framework (Programming Language), Microsoft Visual Studio (Software)

Id: AOLb-nfYdIw

Length: 16min 2sec (962 seconds)

Published: Sun Mar 23 2014