VB.NET Tutorial - Connect & Query a Microsoft SQL Server Database - Part 1

hey there everybody welcome to the VB toolbox this is a reboot or refresher on my Microsoft sequel server series that's a tongue twister I wanted to offer some significant improvements over the old video series and try to answer some of the questions that I've had along the way in regards to those videos so I'm going to you know show you one of many methods to connect to and manipulate data in Microsoft sequel server using visual basic.net I am using an old version 2010 professional but the syntax should not have changed significantly in newer versions if you're following along with those there may be some subtle changes that need to be addressed but I think that you should be able to follow along still anyway let's go ahead and get started now when working with Microsoft sequel server first and foremost of importance is to have an accessible Microsoft sequel server I have an instance of Microsoft sequel server installed on another computer on my network where we are going to have to set up a database to use with our application and in the event that you don't have one set up you can download Microsoft sequel server express freely from Microsoft's website I'm not going to cover all of that information this video sort of assumes that you have a Microsoft sequel server instance to play around with and that you are an administrator on that server still if you do not have the database setup if you have a server installed on a machine but do not have a database setup I will include in this project source code a sample database that we will be using in this it's not awesome it's very basic but just something we can tinker with as we are learning here so I will be using that in this tutorial so first thing we need to do is attach that database to our server okay what I will do is I will restore it here by right-clicking and selecting restore database now that backup file that I have that I will be offering you I presently have in a network share on my server so your path may be a little bit different you just have to make sure that you have access to the file from the server not necessarily from the machine that you're working on that's an important distinction the server that you are connected to that you log into needs to have access to that ok so so I guess I should make one more important point here you will need installed on your computer or on your server somewhere that you have access to the Microsoft sequel server management studio it should come along with the it's an optional module that comes along with the installation package of sequel server and you can also download download that from Microsoft's website though I think that if you I believe it comes packaged with the server install so the first thing you need to do is open up that management studio and you will provide your sequel server name and if it is if you're you know not connecting to the default instance of sequel server if you have multiple instances available to you need to provide that as well if you are connecting to the default instance the server name should be adequate here you'll need to connect as a database administrator this is the system admin account the default administrative account so this is all stuff that you should know and if you don't know this already you know I highly recommend going out and researching you know learning about Microsoft sequel server so as you can see I'm you in version 2008 which you know the Express Edition that I downloaded from the Microsoft website so I need to connect to this administrative account and I need to attach that database to my server so I'm going to come up here to databases and I'm going to select restore database from a device and I need to provide a path to that file and again this needs to be a directory that is accessible to the server not just the client computer that you're connecting you know with management studio from so um if you want to you know if you've already got a server available to you you can skip ahead in the video to get into the actual connection portion and I'll put a link down at the bottom you know so you can skip ahead but go ahead and I'm going to do a restore database from device and I'm going to provide a path to the file on my network and I'm using a network location so it's somewhere that my server can see and I'm going to use sample DB dot back okay is my backup file name right here and when I click OK you'll see your full path it's on my Wizards computer which is where my sequel server is in the shared directory it shows your full path and I want to if you selected a correct location your server will show your backup to restore right here all right now we need to specify a database to restore to once this is attached or you know you've connected to it successfully it should show up in your list down here so here it is the sequel tutorial database is what we are restoring if you want to change any of the parameters where the files are going you can do that here so I'm going to restore it to the default path this default location for these files I'm not changing that if you want to change that you can if you have a special directory for it I'm going to set it to overwrite any existing databases and leave the database ready to use okay once I'm ready to go I hit OK and my restoration should take place and the database has been restored and now I can look at my database structure I have my tables has some billing tables members table for my users payments products so you can kind of see where this is going very basic I don't even know how much data I put in these I built this some time ago so if like and remember all I need to remember about it I'm sure I will have to reference my column names and whatnot from here from time to time throughout this tutorial but that's pretty standard when you're working with a database so another thing that we need to do is provide a sequel login or a user for our application alright our application has to log into the database to be able to you know connect up and query against it or update records and whatnot okay I think that is an old window so let's go ahead and provide a login for our database alternatively you can use the the sa default administrative login though in the real world I don't think that that's usually a good idea I mean that pretty much gives your application user name absolute power over your sequel server so I would caution you on that but we're still going to give it quite a bit of power you know over the particular day the base that we are using so anyway I'm going to go down into the security area here and two logins and I'm going to add a log in here I'm going to right click on the logins and say new log in and I'm just going to call this login tutorial alright so my username is tutorial and I'm going to use sequel server authentication alternatively you can use windows authentication if you'll be using a trusted connection and you do not want to provide credentials so that would use the account credentials for Windows and pass it into your sequel server so you'd be giving that user on your network access to your database instead we are going to be using a sequel server user so for the password I'm just going to say capital P a SS wo Rd 1 or password 1 and I'm going to repeat that capital P a SS wo Rd 1 all right I'm not going to in this case worry about policies and expiration that's totally up to you what will be of most importance here is that we specify what the default database for this user is I'm going to just make sure that I am pointed to my new sequel tutorial database I'm going to leave the default language and for server roles again if you wanted to give this application user a lot of power over your entire sequel server you could set them as you know various levels of administrator system admin giving it you know pretty much global rule over your server this would allow you to create new databases and you could totally do this if you are creating some sort of server management application like server management studio itself where you could actually add and attach and drop and backup and restore databases from your vb.net application you could totally do that in this case we're not going to worry about that but we do want to go to user mapping and select which database we want to make our application master of and that would be our sequel tutorial database I'm going to check that and by default you just get a public login ok you can pretty much see the database but you can't do much I want to be able to add insert delete records from my database and do all you know pretty much any administrative functions within my database and you can lock this down as much as you want or just make it so you're only a data reader if it's a read only database or you know user just needs to be able to do reports and you want to kind of secure it as the database administrator that is your call but for this tutorial I want to pretty much give them free rein of this particular database ok so I'm setting it setting myself as the DB owner and I'm going to click OK here and now I have that user should be able to get in there and you know connect up and do things in this database ok we almost knocked over my microphone here now quickly just before we jump into our application there is a common error if you have recently set up your sequel server there's a common connection error that people run into with a fresh installation of Microsoft sequel server so I'm going to go ahead and close out of my management studio for a moment I'm going to rep see if I can replicate this error here give me one moment all right now I'm going to bring my management studio back up and try to connect I've got my server name and my login and gonna be like what is going on here oh and this error pops up okay so it says a network related or instant specific error occurred while establishing a connection to sequel server this is something that people were encountering in my old series and just want to show you you know what the most common reason for this error is and unfortunately this error can mean a number of different things and so this may not solve the problem for everybody but for this particular instance what we run into a lot of times is and bring up Maya I'm going to bring up a connection to my my server ok so here's the here's the desktop of my server machine see if I can squeeze this in here oh ah sorry technical difficulties I'll shrink that down into my recording area so you can actually see what we're doing so on my server are on your server you can come down and bring up the sequel server configuration manager alright you might even be able to just go directly to the service on the server so what you can see here is this this server sequel server browser service has been stopped ooh get some ghosting there what I'm going to do is restart that service and by default I don't know why Microsoft does this but by default after a fresh installation of sequel server I found often that this service is turned off so just make sure that that is on and running set to automatic startup mode and you should hopefully be able to connect up to your server alright so you can see what happened there and you should you probably get that same exact error using your application to connect to the server if that service is off so hopefully that answers some of those questions alright let's go ahead and jump into the fun part we need to create a new application so I'm going to create a new project in Visual Basic and here I'm going to just call this SQL tutorial for my project name you can call yours whatever you like and make sure that we have selected a windows forms application alrighty and for this tutorial I am going to be using MDI forms okay an MDI forms application makes it so all of the windows that open up in your application actually open up within the main form so your your main form becomes the parent window for all of the child windows it's really kind of cool for inventory based applications things like that a lot of a lot of applications use this feature it's kind of neat I actually have a tutorial out there on MDI forms but I'm going to go ahead and use that in this series so go ahead and select your main form and in the properties window I'm going to stretch my out just a little bit here go ahead and find the is MDI container property and set that to true and you'll notice that the background changes now we can actually spawn new windows within this main window it's kind of cool keeps them all wrapped together you know so it's it's a neat feature and what are some other things we're going to want I'm going to go ahead and bring in a menu strip let's see menu strip alright I'm going to put that up at the top of our application here and we'll use this to create a menu bar and I'm going to change the by default in VB these menu strip's 10 really not so much the strips themselves but that each child item gets a really long name like menu strip item one and so on and so forth so what I'm going to do is go ahead and change this to ms main this is going to be my main menu strip alright now I'm going to come up here and click on my menu bar and add my first menu strip item and I'm just going to do something really basic and simple you know a lot of times applications just like this have file edit view and all that fun stuff so in this case I'm just going to keep this very simple one-liners I'm going to do inventory for my first one and this is what I am going to be using for our query module for this for the you know connection and query segment of this tutorial I'm just going to be calling upon this inventory item so I'm going to click off of that make sure I spelled that right come over here oh here we go this is the long name I was talking about I see it goes clear out of the window I'm sorry it says inventory tool strip menu item Wow really thanks Microsoft so let's give that a friendlier name I'm just going to use mi for menu item and inventory and they spell that correctly inventory I did okay sleepy evening brains not good for programming all right so we have our main menu strip we have this and this is what we are going to bring up our first child window with okay so and we will go ahead and add our first window over here first child window anyway I'm going to right click in my solution Explorer on my project and add a new windows form okay and I'm just going to call this form inventory VB all right creates brand-new baby window for us and I'm going to use this to query our database okay so what we're going to do is some fun features I want to do I want to do like a wild-card search as well as you know querying from our database into combo boxes I think that is an important feature that is used a lot in database applications as well as the good old fashioned data grid view all right so I'm going to first bring up a button here and this is you know I will use this as a search button and we'll do this a couple of different ways but we'll start with a button I'm just going to call it search for the caption and also go to the name property and we can do CMD search all right I'm called CMD find CMD search whatever you like and we'll use that to query our database and of course we're going to need a text box you know for a user input that's kind of kind of small and then just change the properties I'm going to call this txt search as the name property match my button and I'm going to change the border style to fixed single because it's a little more elegant I think bring that down cosmetics form cosmetics how much fun is that I'm a perfectionist try not to be so let's see here what else do I need datagridview definitely we need an output for our window right so or for our data so let's grab a data grid view and we can just drop this down here underneath well okay I accidentally double-click that I kind of want to show you some of the options here real quick and I'm going to start basic with this I don't want editing turned on this is a sort of a raid only query all right so I'm going to uncheck enable adding enable editing and enable deleting all right and I'm not going to give it a default data source we are going to be creating a data source manually if you're using all of the Wizards you know you can you can select a data source a data adapter and whatnot to fill this from your database and there are instances where that is a better approach especially when it comes to the reporting modules and things like that but for our actual application then the way that I use it specifically you know I like to keep that kind of freeform we do it manually and then we understand better how things are flowing so that's just me now I want to go to the properties on this datagridview one thing I like to do is change the anchor property so if the window gets stretched out you can see more of the data grid view as well it kind of stretches with the window I'm going to come down here find the anchor property click this make sure this is selected click your drop down and snap it to all four corners all right all four areas it won't snap up it will just stick right here and it will stretch out if we stretch the form all right so now if we do that they to the data grid switch you know stretches with the with the form um we also want to change the name property from datagridview one to something a little friendlier I'm just going to call it D G V data DG v4 datagridview and data alright and I want one final control on this form it doesn't I guess relates specifically to the data grid view or anything but I think it's very important to learn how to populate combo boxes with database data so I'm just going to stick this guy right over here combo box all right and one thing I like to do with combo boxes where it pertains to databases is change the the drop-down style okay change the drop-down style so it's read-only unless you specifically want to be able to type it in there or type into it but for grabbing you know populating this from your database it's best not to be working with items that don't exist in your data set all right so we do a drop down list and that makes it read-only so the user can't type into this we can only select or view data specifically you know existing in your database I guess for the combo box I'm just going to call this cbx items for the name cbx items right so i think we're ready to well now we need a way to we have our form this is a good place to output data to but we have no way to get at that data so we need some way to manage our database connection to actually access data to stuff in this form so that brings us to the the meat and potatoes of this tutorial connecting to the database with our application now this is sort of my baby I created this some time ago this is going to be a revised version of what we learned in previous tutorials and it will be revised again as we go forward because there's some other features that you know will be very beneficial and answer a lot of questions that I've had in other tutorials so this is the new and improved sequel control class all right and then some tutorials I've called it DB control as well fee and you can totally do that if you know you want a more generic name for this something you can pass between different database types and whatnot so that's up to you I'm going to right click in your solution Explorer on your project and add a new class and this will be our sequel control class so I'm going to make sure it's a class item and I'm going to change the name to SQL control alright in this control class is going to handle our database connection it's going to handle our data set the data table that we work with the data adapter that connects to and modifies data in our database or returns that data to our data grid views and other objects within our forms okay so this is where all the magic happens the first thing that we need is a database connection okay so I'm going to create a private variable here that is only accessible from within this form in this class I'm going to call it DB Khan that is my database connection so it's going to be as a new sequel up wait a minute okay so we need a sequel connection but we don't have that namespace yet so let's come back here drop down a level and we need to import a import the sequel client namespace okay so I'm going to say imports system dot data and by default I believe vb.net has this this namespace available if you're trying to connect to different database types I know this is sort of irrelevant for Microsoft sequel server you usually have to provide some sort of reference library usually a dll that you add as a reference to your project okay for like MySQL which is not part of the Microsoft suite of course you know Microsoft sequel server is well recognized by vb.net so we don't have to import anything into our project generally so I'll just say system data SQL client all right and now that we have that we're going to have a lot have many more objects available to us in this case we need a sequel connection and there she is right there now because we're creating a new instance of this as soon as the class is created we need to provide some connection parameters all right and this part is very important the first parameter that we need to supply is the name you know the the server an instance name that we're going to connect to okay so this is all a big connection string so make sure you just throw up some double quotes there and the first property we want to add is our server instance okay so we're going to say server equals and here we'll specify our server name or the IP address of your server if you're using you know if you're connecting by the IP address or the default instance in my case let me jump back here real fast you can see that you know even though this is my default into the instance this is this is what it's looking for in some cases you can just specify you know get by with just specifying the server name which is wizards and you know without using the instance name if you have trouble connecting without the instance name you know make sure to use that I think if you have multiple instances of sequel server on a single machine whatever you specify you know you you can use just the server name but you have to specify the instance something like that I apologize I'm kind of rambling here and now my brains just letting me down so I'm just going to go ahead try to match whatever you have there in that window to make a long story short when I went a big circle there and got nowhere let's see so I'm going to connect to Wizards is my server name the default instance is wizard box for me keep in mind that is this is relative to your environment what I have on mine will not work on yours most likely unless that's your server and instance name so okay each item in your in your connection string parameters is separated with the semicolon so keep that in mind the next thing that we need the next parameter is the database we have to tell it which database to connect to if we look in our databases in your management studio you will see that we have attached the sequel tutorial database and that's what I want to connect to so I'm going to specify the database name equals oh hello SQL tutorial all right and the final parameters that we require there are you know a number of other parameters and you can look those up you can google them or search for the you know there's websites that are just dedicated to connection strings and they're quite awesome that will show you all the different parameters you can use for connecting but for this tutorial we're just going to stick with the basics the next thing that we need to add is credentials okay we have to have some sort of way to authenticate to our database and there are two primary methods for this one is to provide your SQL username and password the other way is to provide I'd your network or computer credentials the ones you logged into your computer with and that's called a trusted connection all right so optionally you can forego using a username and password and just use your Windows credentials if that's what you set up in your security that's what we're talking about earlier if you've provided local credentials you can use those as a trusted connection so we just saw to do that we just say trusted connection equals true all right that's very simple and then you don't have to provide user name and password every time you log into your application in my case I want to utilize the the username and password that I created for my database earlier at the beginning of this tutorial so I'm going to say user equals and the username the sequel username that I created was just tutorial and the password I created let's use PWD equals password one okay now some of you may be going is that smart storing your connection string you know your username and password directly in your application hard-coding that in no it is you are absolutely right it is not a secure means of storing this but for simplicity for this tutorial I'm just going to be hard coding the username and password in here in the real world you will not be doing that most likely okay anybody with a D compiler or you know someone who can read the memory read your your system memory could could possibly extract this information from that from your memory stream so keep that in mind in this tutorial I will still show you how to create a connection string override so you can actually provide these either via an encrypted file or you know force the user to type it in each time they connect not usually something that's done in the real world usually you can enter these one time you'll direct your application to a server in a setting screen say Oh where's your server what's your you know application credentials and boom you're in so then you can save those encrypt them in a file we're not going to go quite that deep in here but I will show you at least how to provide a no connection string override so I'm sorry I keep getting off on tangents here but this is all this is relevant information and hopefully will answer some questions you know in the real world your your boss is not going to be wanting you know or your clients whoever you're coding for is not going to be wanting you know your database credentials stored hard-coded in your application like that I will go over that later hopefully but let's see next thing we need is a database command ok the database command what is that this is what we pass through our connection to essentially execute a query against our database okay this will store your connect your sequel parameters and your query string and all of that this is not good this is actually going to be regenerated every time we run a new query okay so we don't need to create a new instance of it right now we'll just say DB CMD as an SQL command all right so we'll build this sequel command each time we want to run a query against our database so that's why we're not creating a new instance of this right now it has to be you know something that changes so next up we need somewhere to store and manipulate our data okay so we're going to say DB data and just make myself a little note here the way that we manipulate data or pass the command to the database is using something called a sequel data adapter the data adapter can be used to modify the data in the database returned query data to add a to grid or generally fill up a data table so what we'll do is we'll say public DB da as a sequel data adapter all right and that database that data adapter also gets regenerated each time we run a query okay so every time we want to utilize our sequel control class this is going to get refreshed and rebuilt so we're just creating a variable to hold that variable data next up we need a storage container for the data that we pulled up out of our database okay and this can be either a data set like we used in the older tutorials or more efficiently and much easier to use a data table all right so instead of returning a data set which can can be comprised of multiple data tables we're just going to use a single data table to retrieve our data from the database much smaller much easier to utilize with our data grid view much easier to test against for items so I like I like this much better than the way we did it before next up query parameters how do we want to pass our parameters to our database command okay this has a container for parameters you can access that directly or we can just create a list of those each time we run run a new query against our database so that's what I do just for fun or rather to simplify it in my own mind I guess say query parameters and here I'm going to say making this one public because we will have to access these from the other modules in you know other forms in our application need to have access to this as well as these ones so that's why I'm making these ones public so public params as a new list of SQL parameter okay so public params is a new list of a SQL parameter this list gets generated one time and then we just re rebuild it each time we run a query so do go ahead and create a new one so we don't have to specify a new list every time let's see query statistics something we probably want to keep track of this would include a record count and any errors or exceptions that we receive so query stats will go here I'm going to make these public because we will need access to these from the outside so public record count we want to know how many and record count you know something I added a long time ago and could probably be done away with now that we're using a data table it's just as easy to grab a count here of updated records so probably don't really need this but I'm just going to add it another one is the exceptions okay this will store any error messages we get along the way sometimes we will want to return errors in the event that we encounter a problem during queries or updates so I recommend that and because I want to specify an override I want to be able to process or create my SQL control either with this default connection string or as I promised earlier a way to override this connection string and create a new one I'm the first one we want to do is just add a basic new sub so I'm going to say sub new with nothing in it okay this generates a blank sequel client class or a sequel control class with this default connection string okay but in the event that you do want to allow an override which is most likely scenario we'll say public sub new provide connection string as a string okay so the user supplies the connection string if we were running connection you know want to pull this information from a settings form in our main application you know as is really generally done in the real world then we can do that and all we have to do is just create a brand new DB con okay so all we do is we just say DB con equals new sequel connection and instead of using this string we'll use the user defined string here okay the one that the user provided that's all there is to it now you have a brand new sequel control class you could actually use this say you had an application you wanted to connect to multiple databases at the same time you could totally do that pull information from two separate sequel servers kind of cool because yeah you could create multiple instances two three four whatever that's up to you all right Wow I apologize for taking so long to explain all this but I guarantee you in the end this is going to be much better than the way we did it before the next part is the query execution this is the engine behind this whole class okay this is how we get our data get into our database and get our data so execute query sub we're going to say public sub execute query and this will be our query string so the only parameter we need is the query string and once we have that we can just do all kinds of stuff in our database query records add records delete records update records everything can be done through this sub so it is going to be kind of cool so the very first thing we will do each time this is fired is reset our query stats all right that means no errors no records will say record count equals zero exception exception equals nothing all right blank string now a common problem in database applications is you know things don't quite pan out or don't happen the way you want them to or you know maybe you can't get your connection maybe there's an error in your query string so we want to use a try-catch to capture any errors okay so I'm going to use a 3-part try catch the try catch and finally okay add finally down there so first try all this open the database connect to the database query the database if there's no problems close the database you know otherwise if there are problems catch the problem report it optionally or dump it you know then close the database so on the finally no matter what happens whether it's good or whether it's bad we want to make sure that connection is closed always close the door behind you don't leave your connections open if your application is leaving open connections you may flood your connection pool and cause issues other people may not be able to connect or other routines and methods later on so just something to keep in mind so to say if the DB connection dot state is open still then DB con close all right any time you know if we have our connection open and our connection close here so we'll do that we'll just say open a connection and then when we're done you know say we did DB con close okay and then say we we ran a query okay we open that database ran a query and error occurred during the query it automatically try says Oh ran into a problem jump down to the exception all right it totally skips over this close so we want to make sure that no matter what happens if it sees that it's still open and close it so we don't need this here and now we're ready to do some stuff so first thing we do is open the connection and then we create a database command all right so we'll say DB CMD that we created earlier equals a brand new data sequel command now the sequel command does need some parameters first it needs to know what to do okay a sequel command has a job it needs to do something and the job that it needs to do is whatever our user provided in the query string or whatever application provided so this is the job to do but it needs to know where to do the job all right so we're going to tell it to do the job on how our database connection all right so we have a job to do and then we have a place to do it now the next thing we need to do is provide any query parameters that need to be appended to this query during execution okay so what I will do is load my params into the database command before we actually execute this plan this command till it loaded up with any parameters so we'll say params dot for each we're going to use a lambda expression here so we don't have to do for each this then do that we can do a nice clean one-liner here if you are using such an earlier version of VB net whether it's 2008 or 2005 you actually have to perform a for each expression I don't believe you can do lambda expressions in those you're welcome to ask me how to do that it's if you are unfamiliar with for each loops so for each parameter found in the params list we're going to execute this lambda expression this nameless sub we're going to return the parameter and add it to the parameters for the database commands we'll say DB CMD parameters that's a container I was talking about earlier and we want to add P okay so it cycles through each value it finds in this parameters record takes that parameter and shoves it into the DB command alright and parameters are extremely important in database applications because they help prevent sequel injection attacks and I have a video tutorial on exactly what that is and you know how we use parameters to prevent it so highly recommended not a requirement but highly recommended so next thing we do is clear out that list clear Paramus I don't want to leave any parameters in there for the next query that runs or we may run into some problems so we'll flush it out we'll say params dot clear and then once we have put all this together this is all of our action stuff right here okay once we've got our query all put together telling it where to do its stuff what parameters to use then we're actually going to execute it and return the data okay so we'll say execute the command and fill our data set so the first thing we need to do is actually create you know because up here we did not create a new data table it's just an empty container these are empty you know an empty dated adapter and data table so here we're going to create a brand new data table which is an empty bucket to store all that data that we're pulling out of our database so say DT equals a new data table now we have a brand-new shiny bucket to put our data in and now we are going to use the data adapter sort of the engine to ex-excuse me to execute this database command okay that's what it does it's we're taking it and running it through our data adapter which is the engine so we'll say DB da equals a new sequel data adapter and we're going to tell it to you to essentially execute the database command okay and then when it's done doing that our record count is going to be given a number by filling the data table okay so the data adapter when it's all done chewing up this command it's going to spit out the results using the fill command okay so we're going to say DB data adapter dot fill and it's a function that returns a an integer value as you can see here it's going to return an integer and that is going to be the number of records that it that it queried in the event that we are using you know an update statement or delete statement it will return a zero value but if we did a query it will return the number of records so the place that we want to fill is our bucket our data table okay so we're going to fill that data table with the results and that is it that is that is the whole engine behind this class now when bad things happen we want to capture them I want to know what happened so we can correct it so we're going to capture the app by saying exception equals x SEC query error all right we're just going to return a string here I'm gonna say VB new line give it a line break and return the exception message okay so e^x message so it we're going to catch this e^x as an exception we're going to return the message the error message in that exception store it in this variable because we don't want it to just stop our application dead actually if we don't capture if we don't handle these exceptions it will crash our program and dump us back to the desktop so we want to actually grab that safely and manage the exception so hmm once we have this we I mean we're ready to rock and roll but we probably want actually there's a couple more things we need to do we haven't really told our application how to put parameters in this in this new list okay so you know we're providing a query string here but and we're telling it to loop through this params list but if we don't have anything in it you know we're not going to get any params into our command so let's add a sub for adding params we'll do public sub add pram and we need to provide a couple of items to our for each parameter okay a parameter pretty much requires a name it has to have a name so we're going to give it that and it has to have a value okay so if we want username equals VB toolbox the username is the name of the field that we want to update and VB toolbox would be the value we want to pass into the parameter okay so value as an object now the reason I'm using an object here is because this could be returned or it could be populated with values of many different data types it could be strings boolean values integers you know decimal values and whatnot so it is important to make it so it can be different types an object is sort of a you know allows any any of those main data types next up let's see we're just going to dimension a new variable within the sub so I'm going to say new pram as a new SQL parameter okay and I'm going to specify values for this parameter and it's just going to be the name that I provided and the value I provided that is very simple and I like simple so then once we've created our new parameter we're going to dump it into our params list so we're just going to say params dot add my new pram okay so now we have we've provided our class a method for filling up this list okay and providing them to our sequel command or database command alright and finally you know let's hat let's provide some optional error checking from the outside it would be nice to just say hey did I receive any errors I'd kind of like to know or you know if I did receive an error maybe I don't want to know maybe I just want to gracefully and quietly ignore it this will provide us with a means of doing that so I'm just going to add some simple error checking here and an option of reporting the error so I'm going to say public function has exception spell exception exception okay and a function as we all know needs to return a value but I want it to be optional okay so do I want to report this I'm going to say optional report has a bool in that's true false and when we use optional parameters we have to provide a default value so I'm going to say equals false and then for my entire function I want it to return a boolean value yes or no did I get an error I just want to know if I got an error whether I reported is optional so yes or no is again a boolean value all right so how are we going what are we going to return we're going to say if the string is null or empty and what string are we checking for errors that would be our exception right so if this is blank if there's no errors up here we set this to a blank string if we ran through this and nothing happened nothing no errors were caught then this will still be blank okay so if it is blank if it is null or empty that's what we're checking then return false okay that just says hey I didn't get any errors we're good to go let's keep going now if our optional report if we want to know what the error is I'll say report if a report equals true then fire off a message box to the user saying hey we have a problem and what message do we want to send to the user to end report on we want to report the exception and we can go ahead and give it a critical style if we want say hey we got a problem here and if we want a title we'll just say exception I don't know why I have so much trouble typing that word my brain doesn't like to okay now this this is this line handles if we're using our optional value now what if the user doesn't want to see the error what if we don't want to return an error to the user just want to quietly ignore it then here we'll just say if if you know if this isn't true but we do have an error then finally we're just going to say return true so we tested for a true condition but we didn't want to know about any errors oh my goodness Wow so look how far we've come we have created an entire class to control a connection to our sequel server and now we are ready to actually try it out I'm going to go ahead and make sure periodically you save because it really hurts to lose your project if you get an error so where do we want to try this out at well we've created this inventory screen right so we can populate the data grid view over there and we also want to be able to learn how to search our data okay so let's go ahead and perform these functions now before we can do much of anything you know we do need to tell our inventory form to show up in our main form so we need to jump in there and press f7 to get into your code for your class or alternatively in this case it might be a good idea you can double click somewhere in your window there to generate a load event for your class in this case we want to do that because we need to tell this inventory form who its daddy is who its parent is so we can say MDI parent is form one okay form one is the the base form for a project that's the one that shows up if we hit the Go button okay so if we run this as is we're gonna get you know this blank form that does pretty much nothing all right but hey it's a nice-lookin form i guess we've told it who its parent is but we still need to make it show so we're going to jump over to our main form and we want it to open up we wanted to show this inventory form when we click our inventory item in our menu strips so what I'm going to do is double click on inventory here and it's going to generate a click event and now all we have to do is say inventory dot show what it's that simple yes it is now there is one thing to know about MDI forms you can create multiple instances of these classes if you don't do it by name you can create them as a variable I'm not going to cover that in this tutorial I'm just going to use the base inventory class of form class that we created here so I mean that should be fine I'm going to go ahead and run that and test it out make sure it works so check this out we just created a form within a form all right that's pretty cool we can stretch that out so we get a little more real estate that's cool all right so now that we have that how do we get some data in our data grid view here well we need to use our sequel control that we just created so let's jump back into there go back to our inventory form and what we need to do is provide a new instance of our sequel control class okay so what I'm going to do is say public SQL as a new sequel control now when we do this let me show you something real quick if I do it if I just do it with no parameters it's going to create a new instance and it's going to as soon as this instance is created it's going to use this connection string if I want to override that with a new connection string I can provide that string now okay so you'll notice that we can do that so I could I could specify a new server whoops and I could put that new server I equals some other machine okay so you can create a brand new connection control string and you could use that you know if you're drawing that from a file or from a saved location you can just pipe it in through there as a single string without you know putting all the parameters right here every time you want to run a you know use that control class so just saying that's how you can override that using you know a setting screen or something so once we have a you know a default sequel control class instance and it we use the new here so we don't get a null reference exception I've seen that in the past a lot of people are like I it's a no reference exception and you know it's because you didn't create a new instance of your your class and then you go and try to use it so um what we need to do is generate a query to fill the datagridview with okay so let's go ahead and add a new sub here I'm going to say a public sub called load grid now by default we're just going to test a basic query I'm going to say SQL I'm going to call on this guy here and then you'll notice that we have it access to its methods that we made public okay it's its properties and its methods so I'm going to call my my execute query and this is where I need to know a little more about my database now like I said I may use this in a long time so I want to see a list of members or products because we're doing inventory how about we do products so I'm just going to draw everything from this products table here using a very basic sequel statement I'm gonna say select star from products technically it's an SQL structured query language at this point so wow that's a very basic query we just we just did how awesome is that this is where it's way better than what we did a long time ago okay we're messing around with the instead of using the data table we're using data sets and everything and you know we didn't have an easy method for checking for exceptions so we had to do it every single time we ran a query in this case we can do it in a simple one-line expressional sight if SQL dot has it has exception I'm going to use that function we created true remember do we optionally we can say do we want to report this if we don't want to know if it has any errors then just leave this blank or say false either way it's not going to show you so if there's an exception then exit the sub that is some easy error checking right there I like that I like simplicity if everything checks out then proceed on with populating our data grid view so we're going to say dgv data and we're going to set its data source the one that we omitted earlier to the SQL dot DB DT so this is so nice and simple and clean in comparison to what we had before what we're doing is we're saying you know run this query check for errors and then snap the data bucket that we created in our control class take that bucket full of data and dump it into this data grid view so if we did this correctly and goodness I hope I did you know so it's not awkward we should be able to populate our data grid view using that as soon as the form loads I'm going to go ahead and run it click my inventory and if my connection information is correct boom all of our data is right there everything we want to see how awesome is that ok so you have just successfully connected to your database I hope and queried data into a data grid view that's an it's very awesome that we can do this okay so that is the biggest part now now we can just kind of play with some you know add some more bells and whistles to this this is how simple it is what would be something that is really awesome that we could do next how about searching our database actually you know what before we go to that level let's make this a little a little more robust or load grid what if we you know every time we start up we don't automatically necessarily want to see these products or what if we search you know obviously we might not always want to see every single record okay so let's make this a little more flexible by adding a new query string let's say let's make this optional again will say optional query as a string whoops sorry and add set a default value for that string to be nothing okay and then we'll say if query is blank or you could use the is null the string dot is null or empty then do something else do something else okay so if the string is blank meaning nothing has been provided by our user or application then run that basic default query that we just ran okay and rather than duplicating these efforts here I'm going to put this in a place where it will be I guess air-handling okay so if nobody provides a query that just run out our basic query here otherwise run the query that the user asked for so we're going to say SQL dot execute query and the query 1 1 execute is just query well that's simple so now we have a more flexible application I could say load grid started up it runs just like it did before or I can provide an additional query so let's run this up right bring this up real quick say I just want to see you know a part number that's a lawn mower okay a part know is my column so I'll say select part know from products where part know like and like so use a couple of wild cards here and I'm going to say mo and I mess that up ah look at that so this time it grabbed a single column because I specified a an override query for this yeah that's pretty nifty but I think we can make this a lot better all right I'm just going to return this to the way it was for now and let's do let's do a search okay and let's let's put some of those sequel parameters to use now one of the coolest things just like we just did we used a wild-card search to search the database what if I want to use wildcard search using this text box at the top of my inventory form oops I mean to do that sorry I want to use this search box here okay so I need a I need a method first to perform a search a database search so what I'm going to do is I'm going to say private sub find item okay now the first thing I need to do is provide a parameter for the item that I want to find just like we did a you know a wild-card search for the mower right I want the user to be able to type in a value that they want to search for whether it's mower or hammer or you know any any component of what they're searching for so I'm going to say sequel dot add parameter and we want the user to be able to provide some data so I'm gonna say add item this is the name of my parameter remember and then we need to provide a value in this case I want to be able to create a wild-card search wildcard searches are just awesome so I'm going to say in in SQL structured query language generally the the wild-card is a % so I'm going to take that I'm going to append or concatenate on the value that's in my textbox so txt search text and then I'm going to put another wild card at the end so anything that the user puts in that text box is going to be encapsulated in these wild cards so using this using a parameter instead of just using a string that the user liked using this text box the user if we weren't using parameters and we just pipe this straight into a query the user could do anything that our connection user could do and remember we set tutorial as the DB owner so they could inject a string into this this query field and run that against your database they could break your your query string run a command like drop a table or anything like that against your database and totally wreck your system that's what sequel injection attacks are now using parameters it returns this entire string as a literal value on the on the engine side on the in the actual sequel server engine that you know from Microsoft so they're not just piping text in directly into the query string okay hope that makes sense if you're uncertain with what I'm talking about go check out my tutorial on sequel injection or sequel parameters okay anyway we have created a a parameter now we just need to utilize that with our new load grid query string so we'll just say load grid so we don't have to rebuild you know generate a new exact query we can just reuse the one we already have so we can say select and I'm going to say return all the columns from products whoops we're part know is like and because we're using parameters a sequel parameter we do not have to encapsulate this in single quotes like we used to okay if we were letting the user pipe this text directly in we'd have to break the string bring in the text box down here and allow them to you know have their way with our database not what I like to do use parameters I'm going to use that parameter name and sequel recognizes it natively so you don't have to specify use quotes and then I will end my query with a semicolon so hopefully find item will now generate a parameter and run a select statement with that parameter okay where part number is like items we is like whenever we want to use wildcards use the like expression okay in SQL so if that is ready to go then we need to add some logic for our text box in our search button so I'll jump back here real quick and we'll say double click your search button and just say find item alright so as soon as we click that search button it runs this find item and whatever we have in that text box that use this as the search parameter so let's see if that works come over to inventory hey looks good but I want to search for hammers so I'm just going to put in Hamm hit search oh there we go so we just created a database search feature how awesome is that okay good job if we don't want all the columns and you can specify which ones you want and instead of the select star like part number your standard price part description I could just say you know I just want to see my part know and my part desk when I search let's search for mowers all right so I've got my good old tour trim one thousand and the 24 inch in red and I have black as well sweet so you know I I know it seems like a lot I mean we've come a long way here but you already have like one of the most powerful features of any database application simply the ability to return data and search your data okay and you can do you know people ask me all the time about whether they can do multi table queries and stuff using you know using multiple tables of course like if I had sales items in sales master I can join these two tables together return them just as simply using this select okay and I will be covering that in the future as well probably not in this tutorial because we've already gone pretty far into this there is one more feature that I want to cover in this guy as far as queries go and that is how to pull items from our data data table you know we've shown you how to pull items into a data grid view but what if we want to pull those items into a combo box because combo boxes are used a lot in data driven applications so I'm going to show you how to do that real quick before we end this um let's see let's go ahead and create a method and come down here I'm going to say private sub load CB XOR combo box and I'm not even going to add any parameters when we keep this simple and let's see first thing we want to do is if we've already filled at one time make sure we clear it out you should always do this before refreshing your or before adding items to your combo box make sure you dump it each time you run the query so I'll say well am I doing here refresh items or combo box I'll say CB X items dot items and we just want to dump its containers we're just going to say clear and then once we've done that we're ready to run our query okay so we'll say SQL dot execute query and let's try something simple first I'm going to say select star oh yeah okay now here's one important distinction we can't just select our entire data set all every column into a single column drop-down box right or combo box we just want a single column so let's say select user name from members I believe I have a members table in there I guess we'll find out select user name so I want every user in my database okay it's pretty simple and I want to check for errors so I'm going to say if SQL dot has any exceptions and go ahead and report it because I'm going to show you that before I quit as well and I say then exit the sub all right so it's going to test for errors report them optionally and if there is one it's going to bail out but if all it goes well we're going to loop through each row of oh what am I doing here I meant to do a comment loop through each row of our data table items and add them to the combo box all right this is very simple um the data table that bucket of data that we returned from the database is just a listing of rows and columns it is literally your data your database data so we're going to say for each R as a data row that it finds in an SQL dot db/dt that's our data table dot rows so for each row that it finds in its collection of rows it's simply going to add that item to our combo box and the item we want to add is going to be our each row that it finds and we have to specify the name of the column that we're searching for okay because we can only add one here we're going to do the username is all we brought in so that's all we can use in this case if you use star you need to know the names of each column that you want to bring into the combo box and you can bring multiples in but you have to concatenate them into a single string so in this case we just want the username and just to be safe we can return it to as a string in case it's some other data type the line break there all right so let's go ahead and test this on startup after we load the grid let's load the combo box so a load cbx and run it bring in our screen this loaded up good hey look at that it found every single user in my members table and brought them in you can also you know set that as a sorted box if you want them alphabetically sorted or alternatively you can sort using your query string you know I could say select user name for members order by user name and I could make that ascending or descending I could sort them forward or backwards from A to Z or Z to a okay now check it out now they're alphabetically sorted so you can do that or you can set you know there should be a property on that combo box that allows you to sort it from the VB side instead of from the SQL side either gov them you know use whatever you like whichever is more efficient hmm so that is looking good you know we could also I'm probably not going to cover I mean you could take username and password put them together and concatenate them together right here if you wanted to bring two items in at once that's totally up to you so I mean essentially that's that's what I wanted to cover now there is one the one the last thing you know we have this has exception let's go ahead and use that I want to I'm going to purposefully break this query by taking out a name or taking out a character from the column name and see what happens when I run that oh it's not happy execute query error invalid column name but you'll notice it did not crash my application it's still running because we have the try catch on the other side and we're just pumping the error into a into that string in our class so that works good now what if I you know Oh let's you know check the functionality here so one last thing look in here oh there's no users why is there no users because we said if you if you run into an error bailout of the sub so I bailed out of the sub they quit the sub before it tried adding anything so we didn't get any further errors again crashing the application so mmm this is just a safeguard it keeps us from crashing and burnin now if I take this out or set it to false and I get this that lovely error message notice I didn't pop up it still bailed out because we told it to exit the sub but it didn't bother us but in that case you know you know maybe the user maybe we don't want the user to know there was a problem you could instead optionally have a setting screen where you say hey if you get an error don't bother the user with it but put it in a log file out on the network somewhere that the administrator can go look at that's totally up to you you could you know an error was recorded it went into a log file or good optionally you could pipe that into a window you know that would you know avoid that hard bong you know critical message some people yeah I mean sometimes and you just want to know what's going wrong when it happens and you just do that ok so that's why we created that lovely little has exception that was an idea that was given to me by a friend of mine some time ago so I like that a lot it's a lot more graceful keeps things running smooth so anyway I got to get out of here before this thing quits on me so I wish you all well and I hope that this has showed you some you know how much easier and how much easier and cleaner this whole process can be I mean this is a lot less code than we used before to produce a much cooler result so a lot more sleek and clean I like that a lot and there are more you know more tutorials to come I hope I'll try and I'll try to get them rolling how to add items that process has been cleaned up a lot too inserting records deleting records and I've got some cool stuff I wonder if I can show you you know I have I think I did put together sample here let me here's here's you know I've got more stuff some other items here that's concatenated two fields into a single drop-down see managing users what I want to cover this also a lot of people like login screens we've definitely improved that process as well so you know I can create new users in my tables I can delete users I can delete multiple users you know based on selected values using a list of dynamically generated parameters that's pretty cool stuff so these are things I want to cover in the future and I don't know exactly how deep will go stuff like this again multi-table queries cool stuff so um anyway things like that you know you can filter out items and stuff I want to show you guys how to do this so I hope you'll stick with this series as it comes out and I'll try to keep on it I apologize for being out for so long new users won't notice but those who have been following for some time notice I have not been active much and that's because I've moved recently into a new area my web server is still down and you know just kind of trying to get caught up on life you know and there's a lot more coming so anyway let me know like and subscribe let me know what you think I hope you guys like this and I hope it's helpful to everyone take care and good luck with your apps bye bye

Channel: VB Toolbox

Views: 168,233

Rating: 4.9089532 out of 5

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET Database Tutorial, VB.NET SQL Tutorial, VB.NET MS SQL SERVER, vb.net sql server, vb.net sql tutorial, vb.net projects with source code, vb.net sql, vb.net sql database, vb.net sql database tutorial, vb.net sql database connection, vb.net sql connection

Id: 7Z4BGEHD-JQ

Length: 95min 29sec (5729 seconds)

Published: Fri Nov 11 2016