How to Create Visual Basic.Net MS Access Database Connection with Save, and Search - Part 1 of 2

hi guys and welcome to visual basic.net access database connection okay let me show you guys how this works in this very project we can search let's as you want to search for maybe piles or jonathan just enter whatever you want to search for in there and click on the search button here there we go we can see pious there okay and we can even add let's assume we want to add let's reset click on reset add new data in there and let's say who we want to add is going to be as the reference number and the name is allen morehouse and underneath here we have the address norman close postcode telephone number all we then need to do is just to click on add and we should be able to see the details of arlen right there click on okay there we go and let's scroll down we should be able to see his details there we go that's the details of allen okay one other thing is we'll be able to see that very details on the access database itself okay let's open up the database and double click on it and right there that's that's all in my house details in there okay we can close that the other thing that we can do is we can delete let's assume we want to delete whatever details in here let's say i want to delete john gambino who's coming here deletes john gambino there we go john gambino is off the system now so let's go straight into the database as well double click on the database and john gambino is gone we only have peter and jonathan gambino in there okay so let's say we want to print out click on print there we all then need to do is just to click on this print button there and that is it supposing we want to update details okay let's say we want to update the detail of peter here all right just change these to the number two maybe that that's the new student number we then need to do is take note of these guys all they need to do is to click on update and that is peter's details updated and we can view as you guys can see okay we can exit so what i am going to do now is i'm going to take you guys to the visual basic development environment i will put one of these together okay guys let's exit out and start a new project so click on exit yeah all right so in here we create new project and select visual basic i'm gonna give that a name i'll call it vb save assets i'm going to click on create and there we go now i'm going to define the size of my form so i will make that 1300 by 788 there we go now let's go straight into the tool box here and inside the toolbox i'm going to select i think let's go to the all window form or windows forms actually now come right down here select panel there we go i'm gonna drag a big panel here okay that's my panel right there so i'm going to change the color of that panel to something maybe cadet blue let's go to back color here let's type into that blue or you can always search for it there and i'll create another panel let's grab another one here it's actually inherited the color of this very panel so i'm going to change the color of this very one so let's come in and change the color to control so control is the default column so i'm just gonna let's drag it along now hold on to your control and just copy this very one and let's copy that again reduce the size of this and this one i'm going to change the size of this to about 882 by 576. and the other one let's come in here that is it right here just bring it out okay that will do and i am now going to copy that very one ctrl hold on to the control click and drag again just bring it down okay that's fine so i am now going to add let's add the following so first of all let's come into the tools i will need data review let's scroll right up data preview there let's draw that here just bring the data preview down and drag it all the way all right so i will also change the back color of that data review to something lighter let's come in here and just yeah button face and the border let's make that fix 3d let's go back in there i need a yeah border style fixed 3d right that is fine now let's come back in here and then need labels one label here and i also need text box there i'm going to change the size of both components just select them both and come right here let's change it to something a bit readable maybe 32 let's make it 36 right stay all i need is i'm gonna need about six of those and this one here that is going to be student id and let's change the name of the other one to txt student id and this error one txt student id there okay so let's just select and copy to yeah and i need just two more now move this select there i will realign it properly okay that is fine now copy this just put this here might as well just give that a name right away and forget it so i'm going to call that maybe something to do with access access database connection access database connect let's see all right so that is fine let's make that one a bit bold and this one let's change that to first name and the text content that'll be txt first name there okay so the other thing i'm going to show to you guys now is let's copy one of these and just bring it here it's going to be here i'm going to call that a session engine so that'll be txt search there we go and here i'm gonna need some buttons so come straight to the toolbox here let's grab hold of buttons then i kind of speed it up add a button here let's change the font size to something readable as well and we could just call that i'm going to call that save instead of add data you can call it that data you can call it whatever you like save data yep right okay i suppose you guys get the whole idea so i'm gonna speed that up we will need about eight of those in there there we go so i'm going to speed up the development process and just get back to you guys shortly okay that is it all done so what i'm going to do is i'm going to select the form i want to get this form centered then we run it and you guys see how that looks like there we go so click on run let's see that's how it's looking now okay so we can enter whatever i want to enter in there okay before we do any other thing let's end that i'm going to double click on the form itself and let's come first of all let's add as follows because you might not have the connector o l e db in your system so go to project here and you select manage nuget package click on that and that opens up this browser for you click on this browser itself so in that browser type in o l e db and once that pops up that is a system dot data.oladb select that and click on install there and click on ok and that is it you're done with that you now have your system.odb whatever on your system just before i close that if you notice here you see the options that's the description of what d o ldb does provide a collection of classes for oldb the commonly used type include system.old dots command and so on here we go okay so it's good to know what they are used for so i'm just going to more or less like cheat i'm going to copy that now let's go in here close that we don't need it anymore and up here inside your coding area right on top of the public class from one we need to then import the b itself there that is it imported okay now before we carry out any other thing i am going to first of all go in here i'm going to open up my open up access database so let's select a new a new project entirely and this one let's click on create and uh here let's just give it a name i will call it save database or save data close that so i'm going to say vb save yeah save data that's fine vb save data that's the name of my database click on create once that is created i'm going to just let's give this project a name first i'm going to right click on the table there and i'm going to call it exactly the same name vbs data maybe save data that's all there is to that and right click india again select design view so my very first field that is going to be student id instead of making that as primary key i'm gonna disable the primary key and select data type is going to be short and the next field is going to be first name we have surname we have address then we have postcode and finally i'm going to enter telephone yeah that's cool there so i have all my fields in place so save that and i'm going to right click and just open the the table itself that is it so let's close it i don't have any use for it right now so close that let's go straight to the server look at it server explorer if you don't have server explorer on your system go to windows and select reset window layout and then click on yes and that's the server explorer right here so i'm going to select the server explorer and right up here you see connect to database click on that when you click on that this dialog pops up add connection click on if you don't have microsoft database file or ldb here click on change and you just change it to whatever you want but the default is microsoft access database file or ldb so that's fine so now click on the browse inside the browse navigate to wherever you have your database that's my database right here vbs save data there we go i'm going to get rid of this admin and let's click on test connection that is it the connection is successful click on ok and let's click on advance so that we can copy the location the provided location there we go look at it provider microsoft source and so on there we go i'm going to copy that that is the address that i need copy that copy click on ok and click on ok again there we go that's my database is now connected to the to the server explorer right there okay we're done with that we can then minimize that let's double click on the form to get the database connected to the system so i'm going to double click on the form let's go right underneath public here first of all i'm going to have to connect the following so let's say din connect as new [Music] o l d b through l e that would be connection there we go and i'm also going to sedim cmd that would be us ol command now let's get hold of the table dim dt as neon data table and d ba as nu that would be o l a d b that should be us ol db data adapter and yep that is fine let's get hold of let's assign my command to it here private here private bitmap as bitmap i'm going to be using that for the printer okay whatever we might need again we have to get hold of that okay let's come into my form load here so the way you can get into form load is just double click on the form and that will bring you to form load and now i want to connect this so i'm going to say connect dot connection string there and i wouldn't say equals as follows the location that i copied earlier so just in case if you guys have forgotten this very location you can always go back to your server and that is it right click properties and that's the location either connection string location look at it just copy it okay and that's what i've done all right so now my data or the database should be connected to the system now the other thing that i want to do is i'm going to say txt search the text i want the following text to appear on it there and i will also want the color of this text i want it to kind of like look a little bit like silver color so let's say dot for color that will be equals color dot silver there right okay now i want to let's do one thing to add data i change this to our data i'm going to change it back to save data save data so that you guys you know you have to save the data and i'm going to change the name as well to btn save data that's fine okay i'm going to double click on this so i'm going to start by opening the database first connect dot open and guess what it has to be closed as well so connect dots close yeah in between what i want to now do is i'm going to get a command that will be equals connect dot creates command and let's say command dot command type that will be equals command type dot text dot text right so we also need the command let's come in and say command dot command text and the text i will go in there that is going to be my sql statement that's insert into my database let's check out the name of my database again let's click that and it's called vbsave data so minimize that and in here enters vb save data right so we want to enter the following and if you guys can still recall inside my vbc data i have all right this is our vb set data so right click and open up the design view inside the design view i need student id so i'm going to enter student id in there that's a comma the next one is going to be first name comma sodium comma address post code yeah and telephone we now need to add the values so i'm going to come in here let's open up a bracket and we see the values that we're talking about will be as follows okay plus and the very first value is going to be txt student id dot text that is my very first value i'm going to press enter here and here what we want is this i'm going to enter a comma there it's a comma okay press and the next one is going to be txt first name dot text the first name dot text and i'm going to say plus double quotes single quotes comma single quote right and let's put another clause here and add the next one so just grab all of these paste it here the next one is going to be txt surname dot text okay that again the next one in here is going to be txt address dot text that's good paste that here and this is going to be txt postcode dot text and enter finally i'm going to have here txc telephone dot text all right and i have all my data in there so i'm just gonna call this one let's close here and get rid of this there all the errors is gone now okay i am now going to round up the command and say command dot that is going to be execute non-query let's look for execute non-query right and underneath here let me enter a message box that will kind of like confirm if everything is in process so let's say message message box dot show let's say record saved okay record saved that's good enough and i'm just gonna say comma let's enter my title and there is a vb database vb save database right record save nvb saved a bit in vb saved in ms assets right so we have the first argument the second argument the third argument i'm just going to call that message box buttons dot okay and enter another argument that will be message box icon message box icon so that'll be just an information information that is good okay that's fine so that is my ad taking care of but supposing if i run into an error i can always use a tri cache there but let me run it first and see if it's going to work anyway so let's see and enter some data in here and a name in here i'm going to call that sally sally mo address of solemo hill tab plus code telephone okay and click on save there that means it's working i'm gonna click on okay so if i open up my database double click on this i should be there we go that is it details of salomon is right there so close that so that is fine but like i said earlier on it would be a wise idea to enter try just in case there's going to be any problem try and underneath here i'm going to enter the cash statement in a cash try cash if there's any problem so i'm going to say ex ask exception and my exception statement i'm going to grab hold of all of this statement in here and come right down here first of all say end try and my error statement will be as follows now i'm going to get rid of all of this get rid of this one so the error statement would be generated by the content by the computer system it says so let's say ex dot message there on the the message box icon i'm going to make that an arrow dot arrow yeah that is fine let's try that out again click on run and enter another details and the name is tony montana address he lives in number 17 little venice postcode telephone number there click on save there we go saved so we should have two records now on my database let's open up the database and see let's click on the database double click on that there we go we have 20 montana's details there as well that's fine so minimize that so guys with that i'm going to call it the end of the first part of this tutorial and i'll see you guys shortly

Channel: DJ Oamen

Views: 10,852

Keywords: Visual Basic.Net Access Database Connection, Visual Basic.Net Database Tutorial

Id: uBIYUnUSmHw

Length: 32min 22sec (1942 seconds)

Published: Mon Mar 21 2022