How to Connect a Database and Add/Update/Delete/Record

How to Connect to a entropybase and Add/ modify/Delete exhibit In this tutorial I will explain to you on how to impute to an Access database and allow you to Add/ modify/Deletea record. To amply understand these tutorials pleasedownloadthe tooth root decreeHow to Add/Update/Delete Record using MS Access Database. This citation edict is part of theHotel Reservation Systemthat I am currently working. At the end of this tutorial you will conduct the basic of database computer programming. I would desire, however, to mark especially for dons that one way to learn programming is to live how to correct a program and devote some(prenominal) of your quantify to take awaying.Dont be scared on how short or long an article should be. The outstanding is at the end of the tutorial you will learn something NEW If you already know the topic, indeed dont bother to study this again. Table of confuse of contents 1. establishment 2. Lets get started 3. Database Connection 4. Add and Update a Record 5. Delete a Record 6. Final Thoughts Introduction Before I started learning VB. NET one of the topic that I search for in the inter simoleons is on how to connect to the database and make some changes to the table. Although t here(predicate)s a lot of results, but I cannot make up ones mind one that suit to my motives.Most of the tutorial is using drag and drop features of vb. net veeror program. Well, this is okay in most cases but what if youd like to consider to it the data by rule? So, I created this tutorial so that beginner programmer will learn from this. Lets get started It is real important that you use your common sense to understand the logic of database programming. theres a lot of features built-in to opthalmic Basic editor in chief that most programmer especially beginner who overlook it. One of the deary tools I usually used is theDEBUGGER. If you only knew how important a debugger is, so you do not even lack to study this tutorial.Why ? Because you can starting time right away to the source code and start firing the F8 play from your primevalboard and analyze every striving as you step through the code. leastways beginner is a beginner. You need to start from scratch. If you put on already downloaded the source code, then spread it in the visual basic . net editor by double riffleing the HowtoAddUpdateDeleteRecord. sln. If you lack to know what is the object that runs the first time you start the program (by pressing F5) then double click the My understand at the Solution Explorer. Look at the Startup Form.You will see that the value is frmCustomersList. Now, click this object in the Solution Explorer and click the View Code at the toolbar. Look for the reduce event quasi(prenominal) under unavowed deputizefrmCustomersList_Load(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesMyBase. Load sSql =SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address FROM Customers ORDER BY Cus tomerID ASC waulFillList() FillListView(lvList, GetData(sSql)) determination replace frmCustomersList_Load is the second procedure that runs when you hit the F5 Key from your keyboard.If youd like to know how this code is executed then press F8. Believe it or not F8 is the answer to all your programming question. And I really base it. When I started programming all I do is to search for release source code and start using the debugging tool. Thats why Visual Basic is universe named as Rapid Application Development or RAD. If you follow the debugger the first line it executes is thePrivateSubfrmCustomersList_Resize(ByValsenderAsObject,ByValeAsSystem. EventArgs)then followed byfrmCustomersList_Loadwhich is actually the important procedure to note here.Another important debugging tool is Toggle Breakpoint. You will be prompted to your code if one of the line is marked by toggle break point. This can be through with(p) by pressing the F9 key or clicking the Debug menu then Toggle Breakpoint. This tool is important if the form is already loaded and you want to tract the execution of a code say within a involve passing. For example. Open the formfrmCustomersListand double click the add clit and move the up ar line key once and press F9. You willl have a picture as shown below inlineToggle Breakpoint. jpgNow, when you run the program and click the Add button you will be directed to the code editor window. This case you will see what is happening when you are punish the program. Isnt it nice? Database Connection In order to connect to the database you need a connection string like this PublicConstcn draw and quarterAs cosmic string=Provider=Microsoft. Jet. OLEDB. 4. 0Persist Security Info=FalseData Source=.. /data/sample. mdb Then open it by using this command blindcnHotelAsOleDbConnection cnHotel = smartOleDbConnection WithcnHotel If. demesne = ConnectionState.OpenThen. Close() .ConnectionString = cnString .Open() abolishWith You need this whether you us eOleDbDataReader, ExecuteNonQuery or OleDbCommandBuilderto read or write into the database table. To know more(prenominal) about this class simply click this command and press F1 key to open the help files. Be sure you installed the MSDN. Since you have already open the connection to your database this is now the time to engross the ListView with data. This can be through with(p) by occupation a function like FillListView(lvList, GetData(sSql)) The line of code will then execute a function Fill ListView incorporate with data PublicSubFillListView(ByReflvListAsListView,ByRefmyDataAsOleDbDataReader) DimitmListItemAsListViewItem DimstrValueAsString DoWhilemyData. Read itmListItem =NewListViewItem() strValue = IIf(myData. IsDBNull(0),, myData. GetValue(0)) itmListItem. text edition = strValue ForshtCntr = 1TomyData. FieldCount() 1 IfmyData. IsDBNull(shtCntr)Then itmListItem. SubItems. Add() Else itmListItem. SubItems. Add(myData. GetString(shtCntr)) extirpateIfNextshtCntr lvL ist. Items. Add(itmListItem) Loop EndSub Again in order to see how this code is beingness executed yet run the program using the debugging tool (either F8 or F9). The rest of the procedure is executed only when they are called. For example, the code below is executed only when you click the Add button. PrivateSubbtnAdd_ jerk(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesbtnAdd. Click DimCustomerIDAsString frmCustomers. State = gModule. FormState. adStateAddMode ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. textual matter Next frmCustomers. CustomerID = CustomerID frmCustomers. ShowDialog() CallFillList() EndSub This code will open the formfrmCustomersin add mode and will execute too its own Load Event. If you want to open the formfrmCustomersin edit mode, then just double click the item in a ListView. The code being executed are PrivateSublvList_DoubleClick(ByValsenderAsObject,ByValeAsSystem. EventArgs)HandleslvList. DoubleClick DimCustomer IDAsString ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. textbook Next WithfrmCustomers .State = gModule. FormState. adStateEditMode . CustomerID = CustomerID .ShowDialog() CallFillList() EndWith frmCustomers =Nothing EndSub The two procedure seems carry the same concept, by opening a form, except they vary on the button invoke for execution. The line frmCustomers. State = gModule. FormState. adStateAddMode will tell the target form to open the connection to the database in add mode and frmCustomers. State = gModule. FormState. adStateEditMode ill open the database in edit mode. Add and Update a Record Now, how to save the data in textboxes within the form? This can be done by calling a procedure calledbtnSave_Click. This procedure is fired when the Save button is clicked. PrivateSubbtnSave_Click(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesbtnSave. Click DimdtAsDataTable = dsCustomers. Tables(Customers) IftxtCustomerID. schoolbook =OrtxtCo mpanyName. Text =Then MsgBox(Please fill up Customer ID or Company Name information. , MsgBoxStyle.Critical) ExitSub EndIf Try IfState = gModule. FormState. adStateAddModeThen add a row DimnewRowAsDataRow newRow = dt. NewRow() newRow(CustomerID) = txtCustomerID. Text dt. Rows. Add(newRow) EndIf Withdt .Rows(0)(CustomerID) = txtCustomerID. Text . Rows(0)(CompanyName) = txtCompanyName. Text . Rows(0)(ContactName) = IIf(txtContactName. Text =, System. DBNull. Value, txtContactName. Text) . Rows(0)(ContactTitle) = IIf(txtContactTitle. Text =, System.DBNull. Value, txtContactTitle. Text) . Rows(0)(Address) = IIf(txtAddress. Text =, System. DBNull. Value, txtAddress. Text) . Rows(0)(City) = IIf(txtCity. Text =, System. DBNull. Value, txtCity. Text) . Rows(0)(Region) = IIf(txtRegion. Text =, System. DBNull. Value, txtRegion. Text) . Rows(0)(PostalCode) = IIf(txtPostalCode. Text =, System. DBNull. Value, txtPostalCode. Text) . Rows(0)(Country) = IIf(txtCountry. Text =, System. DB Null. Value, txtCountry.Text) . Rows(0)(Phone) = IIf(txtPhone. Text =, System. DBNull. Value, txtPhone. Text) . Rows(0)(Fax) = IIf(txtFax. Text =, System. DBNull. Value, txtFax. Text) daCustomers. Update(dsCustomers,Customers) MsgBox(Record successfully saved. , MsgBoxStyle. Information) EndWith CatchexAsOleDbException MsgBox(ex. ToString) EndTry EndSub The code for adding and updating a table is the same except that if you are in add mode you just simply add this command IfState = gModule.FormState. adStateAddModeThen add a row DimnewRowAsDataRow newRow = dt. NewRow() newRow(CustomerID) = txtCustomerID. Text dt. Rows. Add(newRow) End If This way you do not need to create a separate command to barge in and update a table. Delete a Record Let us go back tofrmCustomersListform and delete a record. The procedure onward will be fired after clicking a Delete button PrivateSubbtnDelete_Click(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesbtnDelete. Click DimCustomerID AsString ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. Text Next IfCustomerID Then Delete the ingested record DimstrDeletedAsBoolean strDeleted = ExecNonQuery(DELETE Customers. CustomerID FROM Customers WHERE CustomerID= & CustomerID &) IfstrDeleted =TrueThen MsgBox(Records deleted. , MsgBoxStyle. Information) CallFillList() Else MsgBox(strDeleted) EndIf Else MsgBox(Please select record to delete. , MsgBoxStyle. Critical) EndIf EndSub The important line here is the strDeleted = ExecNonQuery(DELETE Customers.CustomerID FROM Customers WHERE CustomerID= & CustomerID &) which call the functionExecNonQueryand deletes a record based on the SQL Statement. Final Thoughts The above tutorial will simply teach you on how to connect to a database and make some changes to the database table. It is very important that you read first some tutorials about programming before you dive into the source code if youre just starting out. If you really wanted to learn faster, then I recommend a book which is my reference also with this article. This book is calledBeginning VB 2008 Databases From Novice to Professional (Beginning