How to Create a C# MS Access Database Connection with Save, Update, Delete and Search -Full Tutorial

hi guys and welcome to c-sharp access database in this tutorial you'll be able to add data updates delete save straight into record and so on so what i'm going to do right now is i'm going to take you guys straight into c development environment and you will see her put this together so let's do that now guys hi guys and welcome to c-sharp tutorial of access database and how to connect delete update save and so on so let's click on create new project i'm going to select c sharp let's click on next and we give our project a name so i will just call it cs underscore access save update and maybe delete so that's the name of my project i'm gonna click on correct okay now that the form is ready let's click on the form and set the size of a form as follows i would make it 1030 by 780 and let's just click on enter there we go so i'm going to come back to the toolbar and let's get hold off let's collapse this i'm gonna get hold of a panel i believe yeah there we go okay i'm gonna punch it there let's drag it that much right i'm going to change the size of that the color of that panel to i think maybe cadet blue would do yeah let's come straight to the background right here web scroll right down we might be able to guess get that blue day there we go now i'm going to copy the same panel that is it right there so the panel that i've just copied i'm going to change the color of that to control there we go and this panel is going to be a child of the previous panel let's just align it properly i'm also going to copy it again just hold on to your control click and drag okay that's fine looking good up here i'm going to need a title so let's bring this down a little bit and this down as well copy this very one and just paste it right there and now we're gonna increase that or decrease the width okay let's get hold of this one again let's see just drag it this way and i'm gonna bring it right down here i'm gonna paste it somewhere here so let's move this down a little bit and move this up yes maybe i'll put my i'm going to put my search criteria in there okay let's go straight into tools now inside the tools i'm going to need some buttons here as one button there now text box come right down here grab the text box put that here that would be for the search now click on this small rectangle select multi-line and that takes care of that now we also need a label come right down here we need a label one here copy this lever put it up there select all of these cell all of the component that i have on board and i'm going to increase their font size to something a little bit readable so come right here and the very first one maybe i'll make that about let's say 36 and see what's going to happen i think this uh that's all right and here this one let's increase the front of that a little bit maybe 48 make that bold and change the font of that to maybe access connector or something let's say access data connection data or maybe database connection we don't have enough room and we don't have to increase the size let's see okay that's not bad data so we can put database then that's fine okay that's this boot right like that so now the next one here that's going to be for student id and so on so let's scroll down just like i said this is just all about creating a database and at the same time we should be able to save the database straight into access that is what this tutorial is all about the tutorial is actually at the request of a viewer out there so let's copy this copy there we go and all of the required buttons let's speed that up so just copy the button across i think i'm going to need about yeah let's put one more okay this is going to be let's call that maybe art our data and this is going to be update and this is going to be btnum the btn update why this one is btn our data i guess you guys the whole idea so i'm gonna speed that up okay guys this is how it's looking now let me add a data grid view this one grab that now i'm just gonna draw that right here i think that we do right okay if i run it now this is what you guys will see there we go okay let's exit out now the next thing i want to do let's create our database right now click on database and select the blank and in here i'm just going to call it cs it's save yeah that will do see is just a c save yeah that's the name of my database okay c sharp saves okay then click on create that's saved inside my document so i'm also going to give you that same name right click on the table and i'm just going to save the table as see save there we go that is the name of my table now right click and let's select design view in there i'm going to call the first data student id yeah that's fine i'm just going to change it to shots that's that's okay then we have first name surname followed by address postcode then uh telephone there we go that should be about six yeah that's fine okay and that that we do so i'm gonna get rid of this uh primary key i don't need that for now just get rid of that okay that's fine let's right click up here and just save that's my database now as you can see it's empty so i'm going to close that close the database now back into my project right here what i want to do now is to go straight to the server look at the server there if you don't have this server all you need to do is go to windows and select reset window layout and just click on yes and your server toolbox data source will appear in there so let's click on the server server explorer now right at the top of the server explorer is the connect to database click on the connect to database and connect to database open up this dialog box that says add connection the data source that i'm going to be using is microsoft access but if you want to change yes you can always click on change and that's the one i'm using that's fine for me i'm now going to click on browse to browse to wherever i have my database i click on see save that is it right there you see the admin i'm going to get rid of that so let's test it out you see the test connection that is it the connection is successful click on ok now you see inside the admin advance right here where it says advanced i'm going to click on the advanced what i want to do in here is to copy the part of my database okay and that is the part right underneath here look at it so you can just copy we're gonna need that click on ok now make sure this admin is out click on ok again there we go and just for your information if you have any problem with the path you can always right click here and select properties and right inside the properties you should be able to see your part right here okay look at your part right there so you can just copy it it's the same it's the same thing okay then i'm gonna minimize this and let's go straight into our form double click on the form and the next thing is i'm going to come right underneath here and let's say using system dot that will be data dot o l e d b there we go okay the connection library is now in place i'll now come straight down here you see where we have probably passion class from one right there i'm going to say o l e db that's the one i need is for connection for now so i'm just going to give that a name i'm going to call it connect con con is file equals new ole connection and the ole connection i'm going to enter parenthesis at enter to speech mark and end that now if you guys can still recall the the connection part that i copied i'm gonna paste that right in there look at that see that makes life easy like i said earlier on if you if you miss out with the connection part all you need to do is to come straight to your server there and just right click to get hold of the connection part and that is the part right here that is it right there just copy and paste it there all right that is taking care of minimize that now the next thing that i want to do since we have the connection part in place and you guys know how to get that done i'm now going to go straight down to my form let's come in here i'm going to double click on this uh add data and right inside our data what we want to do is to first of all open up the connection so let's say connect without open enter parentheses and right underneath open i'm also going to say connect dot close okay make sure you always close your database now in between them i'm going to say o l e command and i'm going to now give that his own name so c cmd so i'm going to now say that is equals connect dot create command there now the next thing is this instant that i've just created c md i'm gonna say dot command type right there and that will be equals command type dot text equals command type dot text okay and testament column now we now want that very instant to get our path so it's going to be command command text that will be equals using the sql statement i'm going to say insert into remember the name of my database right here that is it right there okay grab that just paste that in here so i will advise you guys to save the table and the database with the same name that's cool now what i want to enter in there first of all is going to be as follows open up a bracket student id let's open up the database and copy it exactly so i'm going to go straight into my documents open up that very database and just right click on the select design view i'm going to copy the the fields in exact same format so the first one is going to be id and the next one is right first name surname and so on just copy and paste it there comma so name comma address comma that to be postcode comma and telephone just last check right i think is correct the slightest mistake you will end up with an error so we don't want that now but i have all my fields in place there are six in total the next thing i'm going to do is i'm going to say the following values will you'll get the following values from as follows so open up a bracket i will enter in a single quote and a double quote then i will also enter a plus sign another plus sign another double quotes single quotes right okay right in here now i'm going to enter the name of my text box if you come in here grab that we want to grab the name of this very text box so you can see student id grab that back into our codes and paste that in between here that is dot text day so we successfully get the first one in place now the next one i'm going to press enter so that you guys are able to see it all properly now the next one that i'm going to enter will be as follows oh let's do one thing do that and press the next one right here so what i'm going to do is not to get things mixed i'm going to copy all of these and put a comma here and paste okay i'll press enter then here change the name of this one to first name there we have the first name okay i'm going to copy the first name as well okay since i've pressed enter and come right down here and paste that right here that's going to be so name surname is in place now the next one let's grab hold of all of these now let's grab ahold of this one it's kind of like tricky so you have to be careful grab hold of this one come right here and paste that right here and that is going to be for my address okay address in place after address we have postcode so that becomes postcode there we go do we have one uppercase there yet okay postcode is in place then after postcode we have telephone yeah paste that right here and change it to telephone txt telephone there we go now let's clean up the whole process here so this we have to go here get rid of those and here enter semicolon and that is it so you have to be careful when you're entering this okay so have a good look at that that is the name of my fields student id first name surname address postcode and telephone now the next thing is for us to execute the query so let me say command dot execute is not execute reader it's going to be executed query this is where the one execute none query enter parenthesis and close if you want you can always add a message here just to reassure your user so i'm going to say message box yeah message box dot show and that's going to be data saved up a record saved whatever record saved in database or on database on any of the above is fine so let's come in here comma and i'm just going to call that access let's say access connect yeah that's fine that is the name of the message box comma and i'm also going to say message box dot button that'll be okay comma let's say message box dot icon message box brought him icon where is that it should be there somewhere there we go i'm going to grab the one with the information and just end that that is good so all i did need to now do is just to try out my database so but before i do that i'm going to play let's just be on the safe side i'm going to come in and just type in try let's try cache whatever error that we might run into so close that here and cache underneath here hash any exception ex right press enter and in here i'm going to enter a message box there i can copy all of this message box as well now first of all let's copy this paste that here and in there i'm just going to enter the x dot message so the system with the generated message for me then i can copy all of these grab all of these and just paste it right here and there now i'm going to do one more thing i will close then close the database there we go oh my god look at that that would have given me an error there an approach the fee is missing yeah because i started this one okay that's fine now let's see so i'm going to run it let's see how the whole system works first click on run okay no error so far so let's enter some data in here okay so that is going to be let's say student id just enter whatever in there and the name is going to be my main man today montana that's going to be montana and address 17 dude awareness there and the postcode telephone number and there we go so i'm going to click on add oh my god that's fine look at that i'm going to click on ok there so the system is working so i'm going to exit out let's go into our server look at our server there click on our server and inside the table is my database right click on the table itself and let's click on retrieve data if there we go look at that 2d montana is now stored in my database i'm going to close that and what i want to do now to prove a point is to open up the database itself double click on the database and we should be able to see to the montana's details in there look at that so that is fine the system is working okay let's move on close that and minimize this the next thing that i want to do now i'm going to add two more components so i'll go straight into toolbox i then need a print document and it prints dialog view this very one double click on that there we go i think it's there now i also need the print preview dialog there we go so those two are up there now we will come back to them but you you do need that and one more thing let's do one thing i'm going to just change this around move this up here bring this here then i will reduce the size of this and copy it yeah i think one of them should be exit that is the exit so let's put the exit to the right and i'll just add one more i'll call that maybe view or something like that yeah that's a view there and i'll change the name of this one to ptm view there we go all right now for me to be able to view the data and so on what i'm gonna do is i'm going to i don't want to put the code in there let's create a function double click on that and right underneath here i want to say void view let's say data video data viewer there enter okay my data viewer i'm going to copy everything that i have inside the audio including the try class just copy all of this come right into tidy's function here called data view there and what i want to do is first of all let's change this sql query get rid of this all i just want is to be able to view the database so get rid of this as well there and [Music] this i don't need that and in here let's just select select all from my database c save and that is all there okay now that that is taken care of we'll get this system executed and then i don't need this i'm going to get rid of that so i'm now going to use the date heart table let's say data table and we just call up let's say dt equals new data table right so now let's get hold of the ol did a dick adapter data adapter yeah and i'm just gonna call that dp okay dp is fine okay dp equals new o l o l e data adapter and this data adapter will take in the command right there so whatever we have inside the data adapter uh inside the data table we want it inside the data adapter so i'm going to come down here and say dp dot fill dt there's a table now our data grid view let's make sure we know where the name of that grab the name of the data review that's it right there and come right down here underneath you just paste data grid view that'll be dot data source and where is the source of the data the source of the data is going to be from the equals data table there we go and that is that's what i'm going to use for my viewer okay i can also use it on the form load straight away so i'm going to double click on my form load and just call this and i will also call it inside my data viewer so let's copy that i'll call it inside my view button here first of all double click on the view button paste that in there and copy now i'm going to go straight to the formula double click on form load paste so let's run it and see run there we go look at the data right in there okay let's add more data in here so let's say seven whatever and the name is paul on address number one ghost town postcode and telephone number there we go there so that is the telephone number now let's click on add yeah okay it's right there so if i click only if i click on the view do we see that you see that and one other thing is we can also call the view in here so that is fine anyway anyway so let's take care of let's take care of maybe exit so let's just double click on exit take care of that right away so let's say dialog result i exit and now say i exit equals let's graph grab hold of this message box all of this message paste that in there equals that and get rid of this that'll be my message so that is confirm if you want to exit there that is taken care of but we change this let's change this to dots yes or no okay come right down here and let's use an if statement if i exit equals equals dialogue results yes application that exit so we've taken care of the exit so that would prompt the user to confirm if they want to exit or not instead of just exiting without any prompt so let's run that and see click on exit yeah no yes so we are able to exit now the next thing we want to take care of let's take care of the updates double click on update so with the update i'm going to copy some content from here let's go straight into yeah let's come in here and just copy i'm gonna grab all of all of that let's copy all of these again um no let's copy the viewer copy the viewer and come right down to update double click on update and paste that in here so with the update i'm going to delete all of these here i don't need these yeah get rid of this it seems a bit lighter now let's do one thing in here in the update we have to change this to update and change all of this as well let's see update okay update the database and set let's set whatever we might have inside the student id that would be equals txt student id dot text okay whatever we have in here equals to whatever we have in here there should be a plus sign here and there should be a plus sign here as well okay so that is taking care of single now let's do one more thing so that we can update using student id surname first name so the next one let's say coming in just say where first name and that is a course the first name equals txt first name dot text right so we are able to search with first name and so name now now let's enter soul name as well okay so we have first name we can say sweet id you can say shooter first name and surname let's add surname so with this for them i'm just going to say and here so let's come in here so that would be and track this here so name so we saw them i'm going to say equals and let's just enter the plus sign there cxt surname dot text okay that that tree we do there okay okay so that is taking care of i'm going to just press enter here so that you guys can see it properly there now underneath here i'm going to enter a message let's just get hold of this yeah get rid of that just enter a message box that will tell the user that if you've just updated the system so i'm just going to enter in the record updated successfully there okay that we do and one other thing is we can also just get hold of the viewer data view i'm going to grab hold up my data view so that the updates reflect on the data viewer so come right down here and just paste that in there okay that's good now okay i think it would be a good idea to try out the update now so let's let's see how that's going to work you see in here if i click on this i would have liked to be able to just select all of the data in there so before i update instead of me typing out everything let's come back in here you see the data review let's go to this event in there i'm gonna need a cell let's come into cell cell click this very one here so double click on cell click so inside the cell click now so i'm going to say let's use try cache and where's my cash statement grab all of those let's put that underneath here and i'm going to do as follows right in here txt student id the text that would be equals data preview one dot select row and the selected row that would be an array of zero dot cell and that'll be zero dot value okay cells with an s here and the value return without the s there we go get rid of this all right now that's fine so i'm going to copy that the next one that is going to be for the text box and so on so this is for username oh no first name this is for surname and we have address then we have postcode and finally telephone the whole idea is for me to be able to click on the data preview and everything displays on the text box so it's all row zero and in here that would be one so it will be one here two three four and five there we go all right that should work so let's save that and run it click on run so far yeah that's good so if i click on this there we go look at that so now let's assume we want to change any details in here uh let's go for the student id of tony there we go all we then need to do is just click on update yeah update successful there we go look at that can you guys see that look at this details of students of tony montana there that has changed we can do this the same thing for this let's change that too and then just click on update there look at that that's updated as well so the update is working fine as you can see that's good so let's come in and just take care of delete yeah double click on delete so let's grab the data we have inside the update so double click on update and i'm going to just maybe copy all of this as well copy let's go straight inside delay double click on delete again paste that in there and now we just need to change the sql query in here so this will become delete there so delete from was all delete all from my database and here we set that will be where the student id equals as follows uh my okay we can use the student id yep that's good enough for me so i'm going to get rid of all of this and just use the student id alone so come right here get rid of everything there there we go okay and get a query execute the query close the table message box and that is delete record successfully deleted successfully deleted all right okay let's try out delete and hopefully there's no error first of all i'm going to add some more data in there so let's enter here and let's say the name is sally sally jones number 13 hilltop clothes and telephone number right odd yeah and let's see you click on add let's view oh sally is there so i need to add view in here so let's say we want to delete maybe tony montana okay we want to delete the record of today montana we just hit that there if i refresh as you can see tuning maintenance details is not no longer here so if i refresh does it so when we delete we should also clear out all the details that we have in here so let's come back in here yeah right here so now i want to clear every single details that we have inside the in there so you can just say well txt dot play so you're gonna have to write that for all so the next one is gonna be the text box uh that is for the name sour name where is it paste um post code paste address test all together should be six telephone number is missing txt telephone number and paste there so we just clear out the whole of the text box okay so let's let's copy this view i want this viewer to also update data vr to update the system in here once you add anything that is updated straight away okay inside the let's see okay i think i'm going to press enter here so that you guys can see that properly as well okay that's fine so let's run it and add more data in there it's coming up we are almost done okay we've done one two three four five so we need about three more to do to enter some data in there so that's the student id there and the name is let's say john johnson an address of johnson kings king's land road there let's see let's just change that to rd okay all right um code telephone number update yes and there we go johnson's details is there unless as you want to get rid of johnson's details again so just come right here click on delete yes there all gone see that guys that is fine so now let's take care of reset so reset we can just copy and paste whatever we have inside inside delete so i'm going to come in here come right here double click on reset and just grab hold of all of these including the viewer so that'll be my reset done that's a quick one that is the reset so come back in here we now want to take care of the print let's take it off search first double click on search we have you are working this is working one two left double click on search okay let's go right up there and copy yeah right here i'm gonna copy every single content in the inside the data viewer copy that scroll right down back to the search and paste that in here so what i want to do now is you see in the command text i'm going to change the sql to select all from the name of that my database and that is going to be where our student student id equals as follows so let's say that would be txt student idtxt student id dot text so that is gonna be one so we can search with student id and let's search again with i'm going to say oh we can search with first name okay that's good equals first name so that is going to be approx and first name plus first name txt first name dot text okay let's let's also have the option of session with surname so i'm gonna copy this let's copy all of this yeah and paste that in here and this will become surname and changes to surname and here we just end it with plus speechman approach the v speech map that is done okay i'm going to break it into two so that you guys can see that ah yes yeah that is fine so let's just press enter here okay now there is one thing because i'm now session with each of these text box what i already have is search text box indeed i'll take care of my search so i might as well call the name of this very textbox so come right down here and just grab the name of that text box and let's go into our text search criteria in there and here instead of student id i'm going to put in search box so if student id equals whatever we have inside the search that's fine if the first name equals that or his first name equals that that's fine and the surname as well there okay so that would do now let's do one thing now i'm going to save that i need to add more something to validate the search so let's just run it and see how that's going to look like for now right and let's assume we want to look for open click on search there we go it's able to find me and let's view and look for the other guy jones let's instead of jones let's just enter sally and search we've got sally's details in there so that is brilliant okay now one more thing supposing the details is not there we want the system to tell us then it's not there so so maybe in here i can just enter let's okay let's enter a local variable a global variable here what i need i'm going to need to so in here that is going to be i'm going to call that checklist and checkers or into checker that's fine then i'm also going to declare another one for my bitmap once i'm just going to call it a bitmap i'll be using that for for the printer there we go okay now let me grab a hold of the of the checker and i'm going to go back into my search here and up here i'm just going to say let's get the checker initialized the first value in there is zero however if checker equals zero we want something to happen but before then underneath here i'm going to say check or that to be equals convert dot to end 32 and what is it converting it's actually converting whatever we have inside the database so this is dt dot rules [Music] dot count dot to string there okay and that so that is my search so right underneath here now i'm now going to use the following so let's use an if statement if checker equals equals zero so i just want the message box to say something not found so i'm going to grab this paste that in there and just say record not found not found and that's it okay so now let's try that out i wanna see that's gonna work so have a good look at the lines of code for the search okay have a good look at it you're gonna need that let's move this up a little bit just and this okay that's it that's the lines of code for the checklist run and let's see it's coming out i'm going to enter whatever in here and let's search not found in there so delete this one let's enter it in here not found so if it's not found i want you to get it cleared so let's come in here and say if it's not found txc okay that is done for the for the search save that now the next one i want to now take care of is my prints function this very one first of all double click on the wait wait a minute before we double click on that i'm going to select the print preview here and let's go to the properties of the print preview you see where we have documents drop that down and let's select print document one this very one now double click on the print button and right inside here so i'm going to enter the following lines of code scroll right up there so how to speed that up those are the lines of code for that so have a good look at that save just one more to go so i will now double click on the print document itself double click on with the print document in here i'm going to use try cache again so let's just grab hold of that copy come right down here there's a try gosh this is not gonna be long so that's fine how can so i'm just going to say e dot graphic dot draw that will be draw image let's see that might as well write it type it out draw image and that is going to be my variable that is right of the bitmap i want it to start from zero comma zero that is the coordinate which mean i'm starting from which is right up here yeah and that's all there is to that so what will happen is with these lines of code in here it's going to print out whatever i have inside the data review so i'm going to save that and let's run it and you guys see what i'm talking about first of all let's add more data in this so let's say the student id is five whatever and the name of the student id i'm going to say the name is peter gambino an address of peter clambino good man road and here there are the details of gambino and that is peter gambino there okay and if we click on reset you can add more details of any other person in there so and so let's say we add in king kingsley kently wells i say he lives in oshodi okay and here we have the postcoders and here telephone number there we go click on update yes that is added as well and suppose you want to print that out just click on print there we go and look at that all they need to do is just click on the print button right there there we go i don't have a printer so i'm just going to exit out there okay i am now going to call this officially the end of this project so let's minimize this so that you guys can see the lines of code that are used in developing everything so let's come right down here all right let's stop that from running come right here add our data double click on our data those are the lines of code for add data okay now double click on update lines of code for updates right there next one delete lines of code for delete reset that is easy right there now we have print double click on print those are the lines of code for print from here down here for the button on front for the objects that is right there for the object now let's have a look at search lines of code for search from here all the way up here down here i mean now view with the view i created a function that is the function i call it data view now the next one exit let's exit i remember the two variables are these three variables in there so you're gonna have to watch the video carefully to understand this and i also make sure this library is added and that's all there is to it so let's run it one more time there we go click on that there so with that guys i'm going to call it the end of this tutorial suppose you guys enjoy it and please do subscribe to my channel and you can also join to become a member of the channel there's a lot of benefit for members so with that i'm gonna say bye for now you all have a nice day [Music]

Channel: DJ Oamen

Views: 14,393

Keywords: C# MS Access Database Connection, How to Create a C# MS Access Database Connection, MS Access Database and Connect the Database into C# applicatio, Create a C# MS Access Database Connection with Save, Update, Delete, and Search

Id: vAli4PZwCiw

Length: 64min 14sec (3854 seconds)

Published: Sun Mar 20 2022