VB.NET Database Tutorial - Query a Microsoft SQL Database To A DataGridView

welcome to the VB toolbox this is part two in a series on accessing Microsoft SQL database with visual basic.net in this tutorial segment we will be covering how to query your database using your VB application in part one we established a connection to our database and tested that connection we'll start where we left off in that tutorial and uh we'll be using the same code as the Bas we'll start by opening that project and now let's go to our solution Explorer and open our SQL control class that we created uh if you don't already have that open um in part one we created a SQL command and what this allows us to do is fire a query at the database using whatever your uh open connection is in this case it's SQL or SQL con uh which provides our connection string to our database um we've already tested the connection we're going to go ahead and add a new sub to this and I'm going to call this sub Public Sub runquery and let's go ahead and provide a query string so I'm going to say query as string Str for the parameter and in here we're going to use the TR catch to make sure uh that we catch any errors that are returned from our query so we'll say try and it'll generate that code for us when we hit enter and as before we're going to use the SQL con open and SQL con close so if you want you just copy and paste that down there in the try section and for the exception catching I'm going to say message boox ex. message to capture any errors that we may have in our query um what we can do uh we can do this in a couple of different ways the first way I'm going to show you is how to read directly from the SQL server using the SQL data reader uh that'll allow us to capture records uh pretty much as we see fit as it's iterating through those records so um to kind of give you an example I'm going to go ahead and comment this I'm going to say read directly from the database and I'm going to create a SQL data reader by saying dim r as SQL data reader and I'm going to set that to be equal to our SQL command so SQL CMD and the method we'll be using is execute reader Next Step what we have to do is while this connection is open and this is reading uh we want to Loop through the record so I'm going to say while r. read and that giv us a loop so as long as it's reading from the database and it hasn't come to the end it will continue to return those values uh that we choose so uh what we'll say oh let's do uh message box and there are two ways to return uh our records you can call the records we can say R and provide an index value so zero is always the first record and and uh if you have returned multiple records you can do R1 R2 um and another thing we can do is actually return the column Name by using the data readers get name method so uh let's go ahead and uh return the column name first I'm going to say R.G getname and I want to return the name of the first column in our query and I'm going to concatenate on the value of that column so I'm going to go ahead and just put a colon in a space there to separate the column name and the data that's in the cell so I'm going to concatenate that on as well and um that should do it so here we should be reading directly from the first table the the first column in the first cell that is returned so um I think that's all we need there let's go ahead and try creating a simple query here to our database um again uh to kind of review what I'm using here from part one I created a a SQL Express server on an old box and the database I'm using is my my SQL apps database and I've created a simple Forum structure for that so I've got a members table um The Forum boards Forum threads and Forum replies so I'm going to run a simple query something similar to this here so let's just jump back to our application and go to form one and the code now you can get there by double clicking your form and I'm going to go ahead and comment out our test uh test message this will check to see that the connection is available from our first video um and if it is successful we'll say uh SQL calling out the instance of our SQL control class that we created SQL do runquery that's the new method we created and now we need a query string if you're familiar with the csql language which you should be for this uh tutorial uh we're going to do a uh select and I'm just going to grab oh going to grab the usernames from my members table so I should be able to run this query and it should return it should Loop through um the available rows and grab the first cell in the first column oh you see I made a mistake here I just just now saw that you might have seen it uh the first index value is always zero these are zero base so I want to grab the column name of the First Column and the um the record in the first cell so I'm going to go ahead and run that and it says object reference is not set to an instance of the object so we know that our uh catch exception is working properly so what you can see that I did wrong here is I tried calling my SQL command without actually uh creating that command so I'm going to come right up here and I'm going to say SQL CMD equals new SQL command and the parameters I will use for that are my query string I'm going to pass this query into this connection I'm going to use my SQL con so using a new instance of the SQL command to call upon this using this query which we've provided here so let's go ahead and run that again and it's connecting and there it is so it is returned the column name which is username and added my little colon on there in a space and return the username from that field and it's going to Loop through until it hits the end of that table and then it will quit so it's going to show us each user now that's kind of cool and we know it works now but it's not very useful um to be firing back message boxes so what I'm going to want to do is use a data grid and fill that with the information from my query so so rather than using um a data reader what I will end up doing is adding a SQL data adapter and creating a data set so let's go ahead and add at the top of our class here I'm going to add public SQL da as SQL data adapter and public uh SQL data set as a data set all right now we will scroll down and let's go ahead and com comment out uh this data reader because we don't want to use that anymore we're going to do this a little bit differently now that we know we can query our database I'm going to say load SQL records for data grid now we don't actually have a data grid yet we will create that in a moment um first off what we need to do is set our data adapter to reference uh our SQL command so we'll say uh SQL da equals a new instance of the SQL data adapter and we'll pass the SQL CMD into that so that fires our uh query and connection into the data adapter and SQL data set will be equal to a new data set creates a fresh data set and we're going to fill the data set with whatever comes out of our SQL uh data adapter so what we'll do is we'll say SQL da. fill and then we'll just pipe in our SQL data set pretty simple excuse me so what that does this creates a new uh data adapter referencing our query and our connection this creates a brand new instance of a data set and this fills the data set with data now you can't actually see that data as it as it is if you wanted you could actually probe that data uh using various methods but instead what we're going to want to do is pipe that into a data grid on our main form so it's a little more friendly and easier to read now there's one other thing once we once we start querying um it adds a second point of possible failure in our routine so instead of uh maybe just crashing because it wasn't able to open the database it might also crash because of the query so what I'm going to want to do is say um if if it crashes here it um you know if there are any errors or exceptions it's going to jump directly to our exception uh catch and fire whatever's in here and then um you know it's going to return back to the form but it may not have actually closed the connection before it did that so we need to CL make sure that connections Clos before we try uh issuing any more queries to the database so I'm going to say if if SQL connection. State equals connection State open then SQL con. close Okay so that way it tries to close it if it's still open even if there's an error and it skips over this one up here all right so now we need some uh form controls to play with this uh query um I'm going to go ahead and save my project here and return to the main form let's go ahead and stretch that out just a little bit so we have a little more to work with now because I intend to use this uh same project uh throughout this tutorial series I'm going to try to keep it uh organized and nice here and to do that I'm going to use a tab control to separate uh our activities into tabs so let's go ahead and grab a tab control from your toolbox and draw that to the screen and um I'm going to rename that um see in our properties go up to the name we can see that uh tab control um I'm just going to call it TC SQL in case I ever have to reference it and I want to modify my collection here so I'm going to go back down to the bottom find our collection uh of tab Pages I'm sorry look for the tab pages and we're going to modify that collection by clicking on the little ipsis there and all I want to do is just change the tab text on the first page to query then we'll hit okay let's add a couple more controls to this guy uh first up I'm going to want a text box to type my query into so I'm going to grab a text box drag it on there and uh we're going to want to make that multi-line so with your text box selected uh go into the properties and first thing we'll change is the name I'm going to call this uh txt query and then I will make it multi-line set that to True allows this to expand out there a bit and I'm going to add a command button next and I'm I'm going to draw a great big one right over here for fun and I will call that um let's see I'm just going to call it uh going to change the text label to run query and I'm going to change the name of that to CMD query and now for our output data grid and this will be located down ways oh no it's not it's right there in the middle so let's go ahead and grab a data grid drop it on our form and I'm not going to use any of the data grid tasks I'm going to we're going to do this all in code so uh go ahead and size that up on your form that looks good enough um and I am going to change the name of that to instead of having that long name I'm going to do data grid View dgv and uh data so this will be where our data comes out all right that's pretty simple okay now with these um what I'm going to do is use this text box to create our query so we can actually type in there and then we'll be able to hit the Run button run a query to the database and then output that data to our data grid view so to do this let's go ahead and double click on our Command button and if you want um you can run your connection test again to make sure you have a connection before you try running a query just like we did in part one we'll say if uh SQL do has connection uh equals true then go ahead and proceed um we could also test make sure that there's actually some text in the text box if you wanted that's up to you um I'm just going to go ahead and say SQL runquery and the query string um will just be whatever is in our text box so txt query. text simple enough and uh to avoid any errors on this side uh if if this crashes uh it may not be captured in our uh catch our Tri catch over here in the class so let's just uh make sure that it actually returns a value before it tries filling the data grid with information actually what can happen is it you know if you run a query and it doesn't return any data uh your data table may be non-existent so if we try uh accessing that index it'll crash on us from this side so we'll say if SQL do SQL data set tables dot count is greater than zero then Pro proed okay so we want to at least make sure there's you know one table in our data set before we try before we try filling in our grid so we'll say uh data grid view. data I'm sorry data source equals our SQL do SQL data set. tables and and the first table it finds which is really all there should be um it should fill in our uh data grid with that data set so I think we're ready to go ahead and give this a try I'm going to go ahead and save first oops before we do that let's uh get rid of this um test code from our form load go ahead and just uh backspace that delete it um and let's try running our application I'm just going to click Start there looking good so far so let's see what do we want a query I'm just going to do a basic query I'm going to say select star from um members and I'm going to hit run query and see if that works oh have a look at that it's beautiful so uh what happens if we leave this blank try to run it o doesn't like that execute Reader Command text property has not been initialized um let's go ahead and come back to our code here and let's wrap this here let's say if txt query. text is not equal to nothing then go ahead and proceed we'll end if down there whoops see if that fixes it ah there we go now we can't run a blank query so that works pretty good uh can we do more complex queries uh we could try it out oops let's try it let's see select um username from from Members where uh username is like throw a wild card in there we'll say am end it with a wild card ruin the query look at that work beautifully uh you can also do more complex more complex uh queries like uh joins and things like that we could say members inter join um Forum threads on members. username equals uh Forum threads do oh what is that author I believe is the field look at that so it picked up uh one thread in our discussion forum uh wherever it found this user as being an author in that Forum so works great okay so that is how you query the database using your application going to try go ahead and uh create a bad query here select um member name from database see what happens incorrect syntax near keyword database um valid column name member name let's see select um Forum replies body from Forum replies at that so that is working quite well not sure why it dropped it in the other column maybe it's because we added it in after the fact if we delete that quari it add it back on and there it is interesting so uh I guess that concludes this tutorial that is how you populate a data grid from your SQL database information and you know we've created a really cool little um query screen here for checking up on our records um what we're going to be learning in the future is how to insert new records uh Delete records and also update records in our database so uh lot of neat stuff to come I hope that this has been very helpful for you um there's just a number of other things we could cover but um that's the basics of quering your database into a data grid so if this has been useful consider sharing it with anyone else that you think would find it useful uh thanks for joining me bye-bye

Channel: VB Toolbox

Views: 85,287

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET SQL SELECT, VB.NET DataGridView Tutorial, VB DataGridView Tutorial, VB SQL DataSet to DatagridView, VB.NET SQL to DataGrid, VB.NET DataBase Tutorial, VB Database Tutorial, Visual Basic .NET (Programming Language), VB SQL Server Tutorial, VB.NET SQL Database Tutorial, SQL (Programming Language), vb Database tutorial

Id: D3C7wcAgSJQ

Length: 26min 43sec (1603 seconds)

Published: Fri Dec 06 2013