VB.NET Tutorial - Update Records In An Access Database

welcome to the BB - a box and depart for my series on working with Microsoft Access and visual basic.net in this tutorial I will be showing you how to update records in your database from your application when we're done I hope to have something that looks similar to this this is a continuation in the series so you may want to go back and review earlier videos but if you just want to follow along you can download the source code in the description below so what I will be doing is showing you how to create this form here and edit user form and I will show you how I yeah create a give you the ability to cycle through your records in your database and go to first and last record as well as edit them I haven't actually added functionality to this yet not a big deal I was just going to make it so we could gray out these boxes but more importantly I want to show you how to actually update records so you can do something like this and save to the database then I can go back look at it looks like it's saved so if I go back in should still be there and it is so anyway let's go ahead and get started and try to make this as quick as possible it's a pretty extensive subject I'm going to close this out and get back into where we left off from the previous tutorial so let's just go ahead and start on our first form here I'm just going to double click my menu strip here to add another item and I will just call it edit users but I capitalized that for consistency okay and now what I want to do is I want to create a form you know so when I click this button it brings up a new form for editing our users so I'm going to go over to my solution Explorer right click on the solution header and add new windows form and I'm going to call this update user and that gives us a new blank form template to work with and now we have the fun job of adding a bunch of controls to this to make it work so I'm going to start with my buttons just going to add a button here and I'm going to format it so it looks flat and then what I'll do is I'll just make multiple copies of this so with your button selected go ahead and find the border style actually what is that actually on the button that's flat style I always forget that so we want to change that from standard to flat because it's sort of this flat look and I'm just going to put some characters to indicate to which direction we're cycling through our records in the text property so for this one this will be our first record button so I'm going to do double back arrows or less than signs kind of shrink that up and now what we can do is we can just right click on that and copy or control C and control V we can paste a lot four of these buttons at least and just put them up here probably not going to do that edit button since I wasn't even actually using that before and I'm going to shrink these up just slightly I'm going to select all of them by holding the shift key and clicking and then grab one side and that will just kind of scale them all the same all right I got that pretty well we can just change these change the text property on these that's to go back one record and to advance one record I'm going to use greater than sign and double on that one good now I will name these this one will be first record so I'm just going to call it CMD for command button and first and this one will be CMD previous or just prev keep it short this one will be CMD next and CMD last to indicate which record we want to go to all right now I'm going to grab a group box here just for a little just to improve the look of our form a little bit and stretch my form out just slightly so I can fit all my fields in here nicely probably should yeah I hate taking a bunch time up and design formatting I mean that's really up to you okay but I am just going to change the text on this group box group box group box one to modify user alright and next step I want some text boxes here text box and I'm going to put one little text box up here in the corner and I'm going to make it read-only I'm also going to change that to a flat style instead of that 3d and this one is actually border style so I'm just going to come in here and just say fixed single it kind of flattens it out a little bit and get it make a copy of that before I modify the other properties on that and what this box is going to be for is for the the member ID I'm going to store a unique value from our database usually you know the ID field or primary key something we cannot and do not want to ever update while modifying our database or tables so those are an auto incrementing value in our database so we want it it's an important field because I mean we need to use it to update the record it's always a good idea to make sure you you know get a unique ID there hmm when running your updates for your filter so we don't want to be able to change that we don't want the user to be able to change it so I'm going to set the read-only property to true on that this field will be for our username most applications probably don't allow you to change your username but ours is going to so that's again that's up to you and another one I'm just going to ctrl-c ctrl-v on these ones I kind of get the style the way that I want it make a few extra copies here I want one for each field in my database if we look at our structure here from before we've got our ID column username column password email website and active and we want to be able to basically update any of the fields in here I'm going to change one more property on the ID and I'm just going to change the text alignment to Center so for let's see how many one two three four and 80 boolean field here boolean field is easily represented by a check box so we'll use that for that field I need one more form field on here more text box and let's see what else do I want okay check box and put that right up here and this is going to be for boolean value of whether the user account is active so it change the text on that to active and we can start renaming some of our fields actually changing the names here so I'm going to change this to CB for checkbox and I'm just going to name it after the field that I'm updating with this record let this control this textbox is for the username so I'm going to call it txt let's see probably just txt user will be fine I'll change this one to txt pass for the password and as we learn before we want to make sure that that is a hidden field so we're going to use the system password character set that property to true and next up is going to be the email field so we'll call this txt email and then txt website oops alright one thing that I should mention when it comes to your fields and updating your database it's really important to know you know for for data validation which is something I'm really not going to be covering in this tutorial but you never want the max length on your text boxes or any of the controls that you're using to update your database to be greater than what is actually set for the column in your database for example if we go to the maximum length here it's set to 32,767 but we go to the user name field and the maximum length is 255 in the database so you know be mindful of your your database lengths and things like that otherwise you get errors when you go to update if it's out of that range and try putting in more data than the field can hold with Microsoft Access it's you know table structure is different than a lot of databases a lot of them are a lot more precise this one just pretty much you know it's a text field so stuff some characters in that or a double you care field so they all have a length of 255 again that's you know just for data validation and you know making it so your your queries and updates don't crash on ya all right so we got txt email txt website see be active and can't forget this guy here I'm going to call this one let's see probably txt user ID will be good ok and excuse me finally we are going to add one more button I'm just going to copy one of these and paste it down here select my group box before I do it so it puts it in the group box now I've pasted that down there and this is just going to be my Save button I'm gonna call it CMD save change the text property on that as well good alright we did get all these right it's gonna be last next previous okay um so now I'll probably want some labels on there again I apologize for taking so long on the form design it's always kind of tedious I'm just going to set this one to username I guess there's generally not capitalization on name that and you can bump that up or down with the arrow keys if it doesn't line just how you want it and I'm going to ctrl-c and ctrl-v make some copies just speed things along alright and this one will be oops password email and finally the website great so we have that try to get these a little bit line up here sorry to be a perfectionist yeah whatever okay you get the idea all right so we are ready to begin coding on this form um to do this we can press f7 or double click on the form and we are ready to roll so much like our main form we want to have a reference to our DB control class so we can query the database or run commands so I'm going to go ahead and create a new instance of this private access as new DB control and now we have access to our database there is one other variable we want to create for this particular form and that is going to be a current record Val variable to basically manage what record we're looking at as we cycle through it just kind of keeps track of our position and it's going to be really important so we're going to say private current record and some of you are probably wondering well white you know there's lots of wizards and tools for doing this exact sort of thing but the reason I'd like to kind of take the long way around here is you know wizards don't really teach you how things work they just you know basically give you a quick means of getting where you want to go but if you have a strong understanding of what's actually going on in the background then you know you have a lot more power as a programmer so I think it's it's good to learn the hard way to do these things and I wouldn't I don't think it's really the hard way I mean it becomes easy as you get used to it of course so anyway we want to set the current record as an integer and we're going to set the default position to zero by default you know when you instantiate a an integer variable that's going to be zero but so you really probably don't need to do that another thing we want is remember from the last tutorial we had a means of checking for errors and reporting if we found those so if we jump back to our main form you should have this function that we built in the last video I'm just going to copy that over to this form because that's really useful so just use this for error checking arrow ever checking and reporting saves us some keystrokes there okay so boy where do we want to start first we want to start on how to update I know that's what we're here for but really we need some users to show up in our you know some user information to show up in our controls here so I'm going to go ahead and probably just run a you know create our query first to do this I'm going to say private sub get users okay and this will be our go-to query for refreshing and you know basically refreshing the data table that's stored in our access control rather the DB control here so a data table is important alright so this is a very simple query we're just going to select all user records so I'm going to say query users to fill the data table so I'm going to call my access control here and I'm going to execute a query and I'm going to just select star I want all the information from this table so I'm going to say from members all right and we can as usual report and abort on errors so I'll say if no errors found and do I want to report yes it's also true there so if no errors equals false meaning errors are fountain will say or else what one more criteria inherent one say if no records were returned so if errors were found or if we queried our table and found nothing then just back out quietly I'm going to say access our record count is less than 1 then exit the sub now something to keep in mind because we're setting our repair reporting to true here which we may not want to do but in this case you know just to make sure everything's working for now we'll just set it to true but if that's set to true even if this you know this isn't true I think that well actually maybe it won't report now since that's only on that particular function never mind my brain is tired I'm messing up so anyway back out quietly if if any errors are found otherwise I get the first record and to do this we're going to need another sub so just use that as a placeholder because we want you know we've basically updated our data table now we want to check that data table for the first record that is found in there if all goes according to plants we're going to say private sub get record and when we get our record the first thing I want to do is make sure that there were no problems filling our data table once again even though we have sort of a safeguard here you know if something is wrong for some reason our current record is out of the range of the records that were returned and our get users you know we want to just back out quietly and not just crash so I'm just going to say fail if no records found now I can't type tonight or position is out of range so I'll say if access data table rows dot count is less than 1 or else remember or else says if this first criteria fails don't even bother checking for the second one so or else current record is greater than the number of rows that are in our data tables we'll say access Wow access dot and data table dot rows dot count minus 1 because it is a zero-based array we want to make sure we subtract 1 from the count when we're checking then exit the sub so this just checks to make sure that there are in fact members found in this query and also that our current position is not outside of the number of records found otherwise it would crash when we try to draw records from you know a record set that doesn't exist so all as well we're going to return first user found all right so here we want to grab the first data row that's found in the data table so it's easy to access its column information so we're just going to say dem are as data row and we want to set that equal to access data table dot rows and we want the row at the current record okay so whatever whatever our variable is pointing to that's the index we are going to be grabbing okay and once we have that record we're ready to roll with populating our text fields and checkboxes so we'll say um populate fields alright so the first one we want to grab might as well be the ID column so we're going to set txt user ID dot text equals R from our data row and we want to grab the ID column that's returned from our query up here and we want to set that to two string and we're just going to repeat this for each field so if you want to save some keystrokes and I'm going to do that just because I'm typing really slow tonight I'm just going to do this for each text box that I have out there and I'll change these accordingly so the next step will be the user name and I want to make this txt user dot txt this one will be txt pass dot txt and here I'm going to be drawing from the password column and then I want the email column and remember that these have to be exactly what you have in your table structure that's very important to remember otherwise it won't be happy txt email then we want our website and finally once we have those columns we want our check box and this one we're going to have to change up a little bit so I'm just going to put a little space here to kind of note that it's different than the others and I don't want to attempt to put a null value into the check box can't remember for 100% certain if that would cause it to error out and crash but just in case I'm going to say if our my record for the active column is not nothing so if it's not a null value then go ahead and populate the check box from the data so say check box active dot and we want the checked property here to indicate whether it should be checked and it'll grab that boolean value right out of the database and put it into the checked field and we'll set it to active and we need to take off the string we don't want to try to put a string value into a checkbox that would be bad okay so do you see what's happening there if it's not null then grab the checked value or update the checked value on the form to be equal to the active record in the tea in the database so all right so now we can come back up here to our get users sub and tell it if everything checks out we run the query got no errors we got records then go ahead and grab the first one that we found so we'll say get record now what it'll do is because we started up we got a zero value here it's automatically going to go to the first record it finds in the database whatever was returned in our data table so current record is going to be zero and you can change the ordering if you want to do you know order by ID that's totally fine so that's up to you let's see what else do we need here one if we're ready to test this trying to think let's go ahead and create a form shown event so just like our the one we want the form events here and I'm going to go down to shown so when the form becomes visible once it does let's just go ahead and say get users and we will now want to jump back to our main form and edit this edit users and one thing that we didn't change yet we have this one set to msi delete user so let's check this one set it to msi update users and once you rename that and double-click on it and will generate a click event in the main forum and this is what we use to actually bring up our update user form that we just created and make it execute its events so we'll say update user dot show and I'm going to just run that up real quick all right now we have edit users and that popped up in my other screen you can set that to pop up center screen if you want or elsewhere so sweet Network they grab the first one user ID one I don't have any functionality in these buttons yet but at least we are pulling our database record into our text box so all is working well there now we need to create some logics so we can actually cycle through our records so let's jump back into our update user form under the form code I'm going to create another sub for advancing to the next records so what I will do come here create a private sub called next record and I'm going to use a parameter on this called add value or add Val to advance the position the current position variable okay so we're going to make this an integer and once we do that all we have to do is say current record plus equals and what that does is it's an increment it increments the value of current record by the number that is specified if you're unfamiliar with that so this would advance it by one position so I'd go you know each time you executed this at be you know one two three and so on but we want to use add Val as our incrementer and so what will happen if we supply one here it's going to advance by one if we supply negative one it's actually going to add negative one which is actually subtraction so it's going to subtract that from a current record it'll he'll decrement it so I'm just going to leave a little note here say advance position by add Val okay now another nice feature we may want to add is you know when we hit the end of our table or the last user record that is found instead of just you know hitting a dead end there and you can't go any farther what you may want to do is make it so if you click Next again it takes you back to the very beginning or to the first record that's completely optional but I'll show you how to do it so I'm going to say if current record is greater than access dot dot a table dot rose dot count minus 1 don't forget that otherwise you may end up out of bounds then current record equals zero okay remember that zero is always going to be the very first position in our data table or the array so since that's our starting position that'll be the very first record so it says if our current position goes beyond the count of records or the last record then then just automatically cycle back to zero and that loops it back so let's say a loop to first record and similarly if we go back to the very first record and hit back one time we want to go to the very last record so this creates a nice seamless loop say if current record is less than zero or the first position then current record equals access data table rows dot count minus one so however many records it finds in our data table it'll take us to the very last one in that list so here we can say loop to last record whoops what I wanted all right and finally once we've updated our position our current record position then we want to make the changes visible to the end-user so we're going to say update form oops by using the get record sub that we created okay so this changes our position and then this automatically refreshes grabs a new data row based on that updated position so this will be great for these two buttons here advancing you know one at a time but if we want to advance directly to the first record or the last record we'll just throw a little extra code in those buttons and I'll show you here what I mean let's just go ahead and add those now so we'll say we'll use this one set the previous record we'll just say next record and we want to add on a value of minus one so it comes down here minus one subtracts it from the current record even though we're using addition because it's negative one it pulls it out and we're good then it automatically refreshes what's on the screen so we can repeat this for the next button this time we want to go forward so we're going to say next record plus one okay does the same thing and now we can do our first record this is fairly easy - we're just going to say current record we'll just directly set our current record position to zero boom straight to the first record and then don't forget we need to refresh what's on the screen so we'll say get record then repeat for the last record but in this case we'll say current record equals access data table dot rose dot count minus 1 that'll take us to the very last record in the data table okay all right so let's see how well that works and bring this up here edit users I got to make that so it comes up on the screen click Next took us to the next one then the next one and then the next one it's working good let's go backwards there is a gap and that is actually because user number 5 was deleted so it's not because this is an auto incrementing value we never actually set this if you delete a user from the database the database is still when you add a new user it's going to use the next ID position in its seed so you'll see some of those gaps where those members were deleted in our previous tutorial so we get advanced to the last record that works let's go to the first record one good test are cycling I'm going to back up one level takes us to the very last record I can go back down and it just keeps cycling likewise I can advance to the last record and it takes me back to the beginning so that's essentially how you cycle through the records I'm going to change one thing on my main form here I'm going to set the start position on this to be Center let's see center screen is good all right now we need to actually make it so we can change the data kind of what you came here for right so let's start yet another sub here whoops not in the DB control one at my update user and go back up to my subs and I'm going to add one more hair called update records so I'm going to say private sub update record don't need any parameters on this alright so the first thing we'll do here is do a safety check so I'm going to say fail if no user is selected okay and this is pretty easy to check we'll just say if string dot is null or empty and the string we're going to look at is the user ID since the user ID is going to be our filter when we update our database we definitely want to make sure that there's something in there so we don't just do you know update every record accidentally and wipe out data so if that's empty then just back out of the sub don't even try to update the record next up we need to add our update parameters for our command something I did actually learn here recently is that though the oledb driver that we're using to access microsoft access our database let's see where's that's actually in our DB control here we're using the system data oledb and what I learned here is that it does not care what your parameter names are it doesn't even look at the names rather it simply enumerates the list of parameters that were added that means that the order that you use them and is extremely important so we're going to say add parameters and I'll try to better explain what I mean here and we're going to say order matters okay very important so for example here if I I'll probably have to build them all before I can fully explain this because we actually have to have our command built so I'm going to just go ahead and add my parameters here I'm going to say access add pram drawing on the method from our DB control class and I'm going to add my parameter name so okay one for the username I'm just going to call it user and the value that I want to supply to this parameter is going to be txt user dot txt okay and we're going to repeat this for each field that we want to update in our table so I'm going to do a little copy and paste here and I'm going to do this for each field and I will add one for the password one for the email one for the website one for the active column and finally I'm going to add one for the user ID for our filter now that better change these before I get too far ahead of myself here this needs to be supplied by the password text field and this one needs to be the email field it was smart this one needs the website and this one is not a text field at all the active is a boolean value we want to get from our check box so I'm going to say C be active checked that will pass that boolean value to the bit column in our database this last one is the guy that we had to add in a certain order otherwise we end up some with some really weird results when we go to update our table so you know you look on the form and it may look a little confusing it's like okay the first thing we're grabbing is the user ID and then all these and then this right but here we're going to pull the username first password email website active and then the user ID and it has to be in this exact order because of our command so let's go ahead and build our command so I can explain it better going to say access whoops dot execute query and then I'm going to use my SQL update I'm going to say update members that's the table then I'm updating and make sure that at the end of each line you put a space otherwise you'll get run ons if you're doing multi-line strings here so always put a space on there and then I use a concatenate ER drop it down to the next line it just makes it more readable that's the only reason you could put all of this on one line it's not going to hurt anything readability is important if you ever go back to look at your code so in a standard SQL update we need to use the set keyword here to set the values on the column so I'm going to say username equals and then I'll supply my user parameter and then I will do the password now something I explained in an earlier tutorial again in la DB or oledb there are reserved keywords in certain databases or just the drivers so password just so happens to be a reserved keyword and if you use a reserved keyword you need to put it in brackets whenever you actually call that column directly in your table or your your queries so what you want to do is put your password in brackets otherwise you're going to get a syntax error when you try to use that keyword or update the column so we're going to supply our pass variable or parameter and next up we want to do the email so we're just going to use the email column and set that equal to an email and then website whoops Wow wake up brain website column equals website all right so just remember column name equals value and then active equals at active and then again put a space at the end of the line and we're going to bring it down another line okay for our filter and then I'm going to say where ID column equals at UID now you'll notice that the order that I use these in each one of them coin sides exactly with the order that the parameters were added okay if I took you ID which I use last in this expression and put it up at the top it's going when I update it's it's not going to care what the name of this parameter is it's going to see one two three four five six you know and so it's going to just try it's going to try updating this column if I put it up here it's just going to get really messy so what it's going to try grabbing the value here and putting it in the user field here that's actually a better way to explain it so the order that they go in is the order that they're used or vice versa very important that so user pass email website and so on finally even though this isn't even part of the update it's just you know it has to come in in its place so just think 1 1 2 2 otherwise you know it's going to try putting these in the wrong boxes I probably mangled that explanation but you can try it if you want I you know tested it out and sure enough I put my my user ID on top and then it ended up dumping it into the user name field and really messed up my data so here is where we report an abort on errors and I will say if no errors and please report if there are equals false then exit the sub don't try doing any further updating otherwise if all is well and we make it past that point we'll say refresh the users whoops sorry cut off there refresh the users data table and finally fetch the current record okay to do that you remember what we need to do first off we need to run the get users query okay once you once you save this to the database it's yeah obviously you want to see those changes reflected so what you do is you refresh the data table all of that data from the database to make sure that it actually saved properly and after that you get the current record and the position should be maintained so you know it's not just going to jump back to the beginning because this current record will be whichever record you are modifying so to make sure of that we just get the record actually we shouldn't have to because get users actually execute that for us so I think yeah I think we're ready to actually test this now oops except right it would help if I actually actually put my get users in here all right let's see so we want to tie this update record to the Save button and again I'm not doing any you know actual form control in most environments of course you might have an edit button that unlocks all these fields you know once you're ready to make changes you go into a modify mode and then these become you know they're no longer read-only so you can type in them make your changes save and then it closes all these back off once you've saved not going to worry about that for the sake of time so we're just going to kind of leave it free-flowing and wide open there so let's update record here once we're ready and one last check over this make sure everything is good and I'm going to run this bring up my form yeah comes up in front now that's nice so pizza dude doesn't have a website so we're going to try giving him one I'm going to say HTTP beats dude calm I wouldn't go there I don't know what that is just random hit save you might put something like a success message or maybe even something up here in you know just to indicate that there was a success let's try backing off go back on and it looks like it updated the real test is closed out open the forum and pizza dude still has a website so that looks good Wow I think that's it that looks like we are updating records in the database now so yeah let's try it let's try this checkbox make sure boolean is going in there pizza dude is now an active member of our group and he's still active so that's good what else could we do there you know if it was successful we could say maybe me being update userform text equals me to text and throw in a space and put that save it's kind of silly it's just getting every time we hit save it should add on to that so edit user come here my dark cool site.com save ya it saves successfully good good so it looks like we're working okay if you want you can try deliberately throwing in an error let's take our password brackets off make sure we're reporting errors properly try saving you syntax error an update statement didn't like that so yeah put those back on all righty so that pretty much concludes this tutorial I hope that it was helpful to you I'm sorry it took so long it is kind of a big subject probably took more time than it needed to just based on you know setting up the form this is actually something I get a lot of requests for people like how do i display a single record two text boxes or text fields and so now you know how to do both of these things anyway I hope this has been useful give me a thumbs up or a thumbs down if you didn't like it um preferably a thumbs up if you think anybody else will find this useful feel free to share it I appreciate all the support for the channel and I wish you all well on your projects take care everybody bye bye

Channel: VB Toolbox

Views: 31,626

Keywords: VB.NET Tutorial, VB.NET Programming Tutorial, VB Tutorial, VB Programming Tutorial, Visual Basic Programming, VisualBasic Programming, VB.NET Database Tutorial, VB.NET Access Tutorial, VB.NET Microsoft Access, VB.NET Access Database, VB.NET Update Access database, VB.NET Database Update, vb.net update database, vb.net database update tutorial, vb.net ms access

Id: fNyXNuEHIfM

Length: 57min 4sec (3424 seconds)

Published: Fri Nov 06 2015