VB.NET Database Tutorial - Fill ComboBox From SQL Database & Dynamic Query (Visual Basic .NET)

welcome to the VB toolbox in this tutorial I will be teaching you how to fill a combo box with items from a database in this tutorial I will be using a sequel seee or a compact addition database simply because it's lightweight portable and also comes as part of the visual studio or Visual Basic packages so you should have those on your machine and you nice thing about it is you don't have to install a sequel server to use it so this should work with any other types of databases that you wish to use MySQL or access or anything else that you can establish a nadeo connection to or any other kind of database connection anyway let's go ahead and get started here the first thing we will do is click to create a new project and give it a name I'm just going to call it combo fill and hit OK make sure that's a windows forms application and from here I want to add a couple of controls the first being of course our comma box that we want to fill with database items so I'm going to bring that up and I'm going to shrink my error list down here but maybe we can just hide that more room to play I'm going to change the name of this I'm going to be doing something simple maybe just a list of foods or something like that so I want to select my combo box and change the name of it to something that's a little easier to call on when I call it cbx food and so I'm going to have a list of foods next up I want a text box and in this text box I'm going to display the type of food okay so I'm just going to select that and change the name to txt food type okay so these are pretty much the only controls we'll be using for this tutorial and what I'm going to teach you is how to how to add a database to your project and bring in records from that database into this combo box and then re query the database based on the item that we select in the combo box when we select it it will require it and bring up the type of food and put it over here in this text box kind of a cool trick so next step we want to create a reference to our sequel seee database library so this is how we're going to be able to get access to you know our query features and whatnot so if we come over to our solution Explorer right click and select add reference and then go to the dotnet references and you can organize your component name by clicking that and we want to find system data sequel seee server so go down to the esses and there we are so you should have this 3.51 this one comes with Visual Basic I added this one later on I did an upgrade which you can get for from Microsoft for free but let's just go ahead and use 3.51 that's just fine and select okay so now we have a reference to that library and we can begin creating our sequel control class so to create that class let's right-click on our solution header on our project and select add class okay and we're going to give our class a name I like to call mine SQL control okay you can call yours whatever you like and since we added our reference we can now import the system data sequel server seee library so I'm going to start with imports system data and then I'm going to import system data dot SQL Server seee that gives us access to all of our a do features and the first part of this we're going to need to create a sequel connection and then a sequel command and of course our data adapter and data set it's going to be hard to call on a database here without actually having a database in our project yet so maybe we should jump over and actually add the database to our project let's right click on our solution Explorer again on our project head and select add new item and come down to installed templates data and you should have an option to add a local database and if we click on that we can see it's an empty sequel server compact 3.5 database that's the reference that we added so give your database file a name I'm just going to call this one combo film SDF and click Add and now you should notice if this wizard pops up you can use these and probably a lot of people actually like to use the Wizards I just like to hand code it so you know that's how we're going to learn how it's all working I'm just going to cancel out of that and now we will have the database file in our solution Explorer so with that in there we can now create a sequel connection so I'm just going to add a little notes equal connection and I'm going to create this as a private variable aniseh private SQL con as new sequel c e connection alright and we're going to add one property to this we have to point to the data source and normally in you know most sequel projects or other database types you have a server installed somewhere the beauty of sequel server compact is that our database is a single file that resides in our project directory so it's really easy to point to this so we say data data source equals combo fill dot s oops SDF ok I doubt that's case sensitive but throw in a semicolon and close that string and we're good to go and that should give us a connection to our database now we need to create a variable for our command and our command is what is executed when we want to run a query or an insert or something like that to our database so I'm going to say private SQL CMD as a sequel C command okay and now we need some an adapter and a data set for sequel data so I'm just going to say this is for sequel data and I'm going to make these public because I want to be able to access these from other classes like our main form once we create an instance of this control class because we will be relying on these quite a bit say sequel da as SQL see data adapter and another public variable SQL D s and this is probably the most important one this is where our data is going to be called from in our main application so as a data set and I'm going to be using parameterised queries so just because it's it's a good practice and you know it's more professional and helps you prevent sequel injection attacks against your databases and whatnot probably not a big deal on a tutorial but it's just like I said it's a good practice and it's pretty easy to use them so I'm going to use query parameters and to do this I'm going to create a public list called params as new and make sure you use new here it's important to actually initialize a new instance of the list collection so a new list of SQL seee parameter okay so we've created a list or a collection of sequel c e parameters called params so every time we run a query if we want to use filters which are essentially it's like variables for sequel databases okay it allows you to actually use variables in side of your SQL statements which is nice and finally I'm going to add a couple of variables just for query statistics our record count and any errors or exceptions that we may encounter along the way so I'm going to say query statistics and the first is just going to be a public record count as an integer that will store just a count sometimes it's nice to know how many records you've updated in you know when you run a command against your database you want some you know stats or results can be very handy to make sure that your commands are running properly and exception because I want to capture any error messages I get when I run my queries all right so now that our variables are done here we can begin creating our query execution sub and the nice thing about using this class is this is completely reusable code you can just kind of drag this over and copy it over to other projects if you have other database projects you're making it's really nice and it also you know makes this this sort of dynamic it allows us to control the sequel control class one one difference between you know this project and something that might be a little more professional is that generally you're not going to specify your sequence equal connection parameters right here you're probably going to have a Settings tab or something in your application where you can actually allow the user to define what database they want to come - and things like that so let's go ahead and begin creating our query execution sub I'm going to make this sub public so I can access it from the outside so I'm going to say public sub exec query executes query and I want to make it so I can pass a query string into this sub from the outside so I'm just going to write query as a string and I'm going to perform my queries inside of a try-catch statement and it capture any errors and if there's an error we don't want it to crash our application we want it to return it gracefully so and I just hit try and hit enter and it will generate our try-catch statement and the first thing we need to do is open a connection to our database so I'm going to say SQL con dot open and I like to immediately follow that with SQL con dot close which is actually going to be the final but I'm just going to go ahead and put that in there and we'll just be working in between these two commands so the first thing we need to do is create our SQL command so unless I create this qo + and say SQL CMD referencing our command variable up here SQL CMD equals new sequel seee command and we're going to pass two parameters into this the first being the query string that we provide from the outside up here and the second parameter being a reference to our sequel connection piece of cake nice and easy alright next up if we have any parameters that we've supplied for variables in our query we want to load those in right now or put those parameters in our parameter a mist so I'm going to use a nice clean little lambda expression to do this in a single line and I'll try to explain that as I go so I'm just going to say load parameters into SQL command alright so what I'm going to do is reference my params list here and I'm going to say Rams dot for each and I'm going to create a nameless sub inside of here generating a lambda expression each sub X X will be equal to the object inside of the list so each item ated encounters is X so list item one list item two and so on and then I'm going to tell it what to do for each X that it finds I'm going to say SQL CMD parameters which is part of the sequel command object we're just going to add whatever is in our list to this sequel params and the item that I want to add to our command parameters is X from our list so hope that makes sense it simplifies that we could do it for each you know item in params list then go down and add these you know individually but having a lambda expression reduces that to a single line of code which makes it nice and clean next we will clear our parameter list okay once we've added all of our parameters to our command we don't want to leave them up here because then next time we go to run a query it's going to be finding a whole bunch of parameters from our past queries and trying to add a man it's going to crash our future queries so we're just going to dump that list when we're done adding them to our command so to do that it's very simple we just say params dot clear so that flushes our parameter list now we are ready to capture our data execute our command and fill the data set all right to do this the first thing we're going to do is create a brand new clean data set so let's a sequel the S equals a new very important to use that new data set and once we've created that we can create a new data adapter as well so SQL da equals new SQL seee data adapter and inside of the data adapter parameters we want to tell it what to do this is going to cause it to execute this sequel command that we've loaded up with parameters so we want to reference that sequel command right here and as soon as it creates this new sequel data adapter it automatically executes this and then once we do that we want to set record count this is kind of interesting is going to equal the execution of our or the filling of our data set okay when we use the data adapters fill method say dot fill it returns a numeric value indicating the number of records that were affected so we're going to capture that into this record count variable and what we want to fill using our data adapter is our data set so we're just kind of passing the data around here goes out to the database grabs it through the data adapter and then dumps it into the data set and that's all there is to it now here I like to capture errors there's a problem what I'm going to do is just capture to this exception string which I will be able to reference because it's public I can grab it from the outside I'm just going to say exception equals e^x dot message all right now in the event that there is an error we need to make certain that the database is closed so what happens here is if it's running through the try/catch and it's able to execute part of the query but it encounters an error halfway it will jump out of this try and it will jump out of this code and go straight down to the exceptions okay so it will actually bypass this closure and leave the database open so what we need to do is after it's done with this whole try-catch thing we need to just make absolutely certain that the database is closed otherwise it will hang up future queries so let's say if SQL connection dot state equals connection state open saying if it's still open then please close it SQL con dot close all right let's make sure the connection is closed now we just need to add one final method to this and it will be a completely functional and usable class we need to add a method to actually input parameters into our list here so just to simplify it you know we could access this remotely and just dump them in but it's it's nicer to make things dynamic so your codes not quite so bloated and huge at the end having a simple class to handle that or a simple method to handle that keeps things clean so I'm going to say public stub add Ram and I'm going to give the parameter has to have a name and a parameter also has to have a value okay so value can be different data types integers strings different database data types so rather than actually trying to add it as a string or some other kind of data type we're just going to use an object data type which can essentially be anything so value as object and here we're going to dim a new parameter so we create a new instance of sequel parameter make sure you use new SQL seee parameter and we're just going to dump the name name parameter and the value into this new sequel parameter so it's pretty simple and finally once we've generated a brand new sequel parameter we're going to dump it into our params list so just save Graham's dot add new pram that's all there is to it create a new parameter add it to the list so this makes it really simple to add them from the outside without having a bunch of extra code so we should be ready to go here now what we need to do is actually have some data in our database to draw from that's going to be pretty important so what you can do is you can right-click on this database and go to properties I'm sorry open up properties open and it should bring up your server Explorer with your database combo fill and what we have is a blank database with no tables or anything so all I want to do is just add a table I'm going to right click on my tables folder there and create a table and this table I'm just going to call food and I'm going to add a column name ID I like to add an ID column that's going to auto increment it's important to set this talk to identity increment now because if for some reason if you forget to do that and you come back you made your table all up spend a bunch of time creating your table and you come back and realize you forgot to set an identity increment it won't allow you to do it without dropping the table and rebuilding it all over again so I'm going to set the data type on my ID column to integer int length is 4 allowed nulls it's going to be no unique yes primary key yes okay and here's where I want to make sure this is set to be an identity field all right so next up I am going to add some columns I want food name and I'm just going to leave this on in better care and give it a length of 30 characters allow nulls no I don't want to allow moles unique the food name must be unique because I want one of each kind of food in here doesn't need to be a primary key food type is going to be my next field and the food type can have multiple values of the same of the same type so I'm going to say allowed nulls I don't want any nulls here I want each one to have a food type but it does not need to be unique so I'll just leave that so that's a very simple table structure I'm just going to keep it like that and click OK and that will generate a brand new food table for us now that we've done that we need to add some items to pull into our fields so what I'm going to do is come back over here and right-click on this table and go to show table data and that will actually allow me to edit the table values here so food names um let's do an apple while it's a fruit okay steak that's a meat chicken that's also a meat poultry whatever banana is a fruit and a pie for dessert whatever that's probably good enough for testing so I'm going to go ahead and save my project here as combo fill hit save and now I have some goodies in my database to play with and I click on my tool box here and I'm going to grab a couple of labels so we know we're putting where this one's just going to be food I'm changed clicking on my label and changing the text property here in the properties and you should be able to right click on that copy and paste another one or control V control C and control V to copy and paste those and this one will be food type okay and I did rename that right okay so oh let's go ahead and double click on our formed it into a our form code now we're ready to start coding our main form first thing we need to do is reference our lovely new sequel control class so I'm going to create a private variable called SQL to very easily manage all of my sequel queries and connections so I will do SQL as a new very important SQL control and it should find your class and reference it and that's all there is to it very easy so now what we need is a sub to manage our sorry about that I have a phone call going to jump down and create our method for populating our combo box okay so I'm going to say private sub get foods I don't need any parameters here and I'm going to query the food staple now generally if I wanted any filters on my query I would add some of our SQL parameters but I do not need that since this is sort of a generic I want to grab everything from my foods table into my combo box I want to grab all of the Nik food names so this is going to be very simple I'm just going to say SQL dot execute query and here is where I'm going to put in my query string you do need to be familiar with SQL it's just using sequel compact addition is virtually identical to using sequel server with the exception that you know it's lacking a few of the more advanced commands other than that it's very similar just going to say select food name from food okay that's all there is to it we've generated a sequel query so if we wanted to test this from the get-go we could do message box and grab SQL once we launch that query if you remember back here it sets the record count so if we have any Foods in our table then they should be drawn in here so I'll say message box SQL that record count want to know how many it finds if any and we need to actually fire this so I'm just going to put it in my form load event here just going to say get foods run it and look at that found five fruits in there if you got an error when you did that instead what you could do is capture the exception you know to find to find out what it was or if you got zero records now ours is working fine but what if we just threw in an error for for the fun of it I'm going to try selecting food name from a foods table which we know is incorrect oh look at that it captured an error the specified table does not exist foods so it's a good way to capture your errors and fix that and now that we are successfully running queries of our of our database we can now capture some of those records so what I'm going to do is say if records are found add them to combo box all right so what we want to do is say if SQL dot records record count is greater than zero so if we do find records then for each R as a data row in SQL dot SQL des or data set tables I always grab the first table in the list that it finds which is always zero dot rows okay so to go over this again we are probing our tables here the rows of our tables for each row that it finds in this list of rows this collection of database rows we want to return the value of that to our combo box so I'm going to say cbx food dot items add and the value that we want to add to our combo box is going to be our being the data row that's returned and we have to reference the correct column from that row of data because we have three columns the ID the food name and the food type so we want specifically the one that we pulled in our query so I'm going to say food name in quotes and that tells it which column to grab so we're going to capture all of our food names into our combo box and then once it's done we want to set the first unless you don't want it to automatically select one of the items you know leave it blank as you see it here you don't have to do that if you actually want it once it finds items if you want it to automatically put a value in here you just set the combo box and to the first record but it flies okay so we're going to say cbx food cbx food selected index equals zero that just sets it to the very first item that exists and it changes the value the text value and whatnot to whatever item is there selects it okay and sorry ran out of time there down here we can say else if SQL dot exception not empty then report occurs messagebox SQL dot exception alright so let's go ahead and run this guy and see what happens I'm just going to hit play and look at that looks like it's going through our database and filling our combo box now if you do this multiple times your combo box is probably not going to clear out so you know if I ran this get food several times it's probably going to start duplicating the items in the combo box because it won't remove them if you wanted to you could probably throw something you know up in here something like clear combo box do something like cbx food dot items dot clear I think that would kind of purge it for us if we had a button we could sort of test that but main thing is I wanted to show you how to draw data into this combo box from your database next up what if we want to have some sort of action when we select banana okay what if we wanted it to query the database again and find out what the fruit type of the food type was well we can certainly do that what we're going to do is create a second sub down here to fetch the food type so creating a new method here called private sub get food type okay and for this command the food type that I want to get is going to be based upon the food that is selected in the database so whatever food isolate should have a food type associated with it so I'm going to say food as string okay and that's going to essentially be my query filter so here we are going to actually use our search parameters to pass this in as a variable in our sequel query so I'm going to say add a search parameter and to do this is quite simple since because we added this add parameters sub to our sequel control class all we have to do is say SQL to add program and we need to specify the name of our parameter which is usually done it's a string so we have two open quotes here and usually use an @ sign and then give it a give our sequel variable in name in this case it will be food and then hit comment and now we need to know the value that we need to pass to our database through this variable and in this case it's just going to be the food string and we have there so very simple to add parameters now to our queries and they're also pretty simple to use so now that we have that all's we have to do is run query okay SQL dot execute query and we're going to build a query string here I'm going to say select food type is what we want to return here from our food table where hmm so forever filter say where the food name is equal to our food parameter okay our food variable so this should match what we're going to do is pass this into here and it's going to load it into this variable and we are going to call that variable inside of our string right here so it actually normally if we're not using parameters what we might have to do is have do something like this where the food name equals and then we have to break our string concatenate on the combo box and the text from the combo box okay and then we'd have to close our string make sure we put our single quotes in there on each side and you know it's kind of a pain and it also is less secure because people can use sequel injection attacks against your database if they're given a field to type in and execute those queries so much better to use sequel parameters all right so that's fairly simple and then we'll say if food if our food is found then send the food type to our text box okay so we're going to say if we find some food records here we'll say if SQL not record count is greater than zero then we are going to set our txt that's our text box food type dot text to equal and this is where you know this is going to be a of information here so SQL dot our sequel data set SQL D s dot the first table it finds being 0 and then the first row it finds if for some reason our you know I set it so in our database the food name is unique so it will always only return a single food value here okay it should always only return one record however if for some reason you had more than one food of the same name you know had two different types we had a duplicate record for some reason if we just grabbed row 0 it's going to force it to grab the very first record that it finds and report that item and the item that we want is our food type so just like we used up here up here is a lot easier because we just cycled through the records and grabbed all of this text into visible you know kind of wrapped it all into this R which is a row in this case we're calling table the row in the table and then the item in the row so item is going to be food type okay so just to kind of go over this again if we have more than zero records if it finds data then set our textbox to be equal to the data that it found the food type that it found okay so I'm actually firing that just yet now we need to actually cause it to execute this food type and what we'll do is make this a nice dynamic query so we're going to double click on our combo box and that's going to generate a text index selected index change event okay and from here meaning every time we click our drop-down list and select an item it's going to run this new query okay creates a dynamic query it's pretty awesome so to do this is very simple just say get food type calling upon the sub that we just created here which is you know really only three lines of code very simple and we need to know what kind of food we do because we want this to be dynamic we don't just want to say you know apple or something like that we want it to be whatever is in the combo box when we change our selections so every time we select a new item in our combo box so we'll say cbx food dot text and that will show the selected item the text so we're going to do is when we select an item it's going to pass that text into this get food and that text is going to become a parameter and that parameter will be a variable to filter our query by see how that kind of just rolls down nice and clean so we run this guy look at that it already because what happened is when we first started it ran this get foods and at the end of get foods we are changing the selected index and because it sees that it sees that as a selected index change event it automatically fires as soon as the application starts and so it pulls Apple queries the database for our food types loads the list and then as soon as that index changes it runs another query against the database grabbing the food type associated with that so if we select banana that's also a fruit if we select pie it's a dessert if we select chicken it's a meat so every time we do this we're running a new query against the database and just grabbing the Associated data using this as our filter pretty awesome stuff so I hope that this data database tutorial has been helpful to you and keep in mind like I said earlier you know I know we're using sequel here sequel seee specifically not even sequel server but this can be used exactly in the same way with sequel server or with you know any other database type that we're connecting to so I hope this helps out you know please leave your comments and you know and your thumbs up if you liked it I appreciate that I wish you all well thank you bye bye

Channel: VB Toolbox

Views: 61,300

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET Database Tutorial, VB Database Tutorial, VB.NET Database ComboBox, VB Database ComboBox, VB.NET ComboBox Tutorial, VB ComboBox Tutorial, VB.NET SQL Tutorial, Visual Basic Database Tutorial, Visual Basic .NET Tutorial, Visual Basic .NET Database Tutorial, VB.NET ComboBox DataSource, VB ComboBox DataSource

Id: jTlNxwnwhSM

Length: 47min 38sec (2858 seconds)

Published: Wed Jun 25 2014