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

How to Connect to a Database and furnish/ modify/ erase Record In this tutorial I will explain to you on how to pertain to an Access database and allow you to Add/Update/ blue-pencila testify. To fully understand these tutorials please transferthe seed codeHow to Add/Update/Delete Record using MS Access Database. This source code is part of theHotel Reservation Systemthat I am currently working. At the end of this tutorial you will notice the basic of database course of instruction. I would like, however, to emphasize peculiarly for beginners that one way to learn programming is to cut how to debug a program and devote some of your cartridge holder to reading.Dont be frightened on how short or long an article should be. The important is at the end of the tutorial you will learn something NEW If you already know the national, thusly dont bother to study this again. Table of contents 1. Introduction 2. allows 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 internet is on how to connect to the database and make some changes to the table. Although theres a lot of results, but I cannot find one that suit to my needs.Most of the tutorial is using tousle and drop features of vb. net abbreviateor. Well, this is okay in most cases but what if youd like to manipulate the data by code? So, I created this tutorial so that beginner programmer will learn from this. Lets get started It is very important that you use your common sense to understand the logic of database programming. in that respects a lot of features built-in to optic Basic Editor that most programmer especially beginner who overlook it. One of the favorite tools I usually used is theDEBUGGER. If you only knew how important a debugger is, then you do not even need to study this tutorial.Why? Because you can jump right onward to the source code and start firing the F8 command from your exposeboard and analyze every argumentation as you quality through the code. Anyway beginner is a beginner. You need to start from scratch. If you contract already downloaded the source code, then open it in the visual basic . net editor by simulacrum clicking the HowtoAddUpdateDeleteRecord. sln. If you want to know what is the determination that runs the first time you start the program (by pressing F5) then double click the My mould 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 Load display case similar under Private chockfrmCustomersList_Load(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesMyBase. Load sSql = take in CustomerID, CompanyName, ContactName, ContactTitle, Address FROM Customers ORDER BY CustomerID ASC CallFillList() FillListView(lvList, GetData(sSql)) End gun f or hire frmCustomersList_Load is the second procedure that runs when you hit the F5 underlying from your keyboard.If youd like to know how this code is kill then press F8. Believe it or not F8 is the answer to all your programming question. And I really mean it. When I started programming all I do is to search for free source code and start using the debugging tool. Thats why Visual Basic is being named as Rapid Application Development or RAD. If you follow the debugger the first filiation it executes is thePrivate gunfrmCustomersList_Resize(ByValsenderAsObject,ByValeAsSystem. EventArgs)then followed byfrmCustomersList_Loadwhich is actually the important procedure to tone of voice 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 do by pressing the F9 key or clicking the correct menu then Toggle Breakpoint. This tool is important if the mould is already loaded and you w ant to packet the execution of a code say within a command tone ending. For example. Open the formfrmCustomersListand double click the add button and move the up arrow 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 argon executing the program. Isnt it nice? Database Connection In order to connect to the database you need a fellowship string like this macrocosmConstcn draw offAsString=Provider=Microsoft. Jet. OLEDB. 4. 0Persist Security selective information=FalseData Source=.. /data/sample. mdb Then open it by using this command low-keycnHotelAsOleDbConnection cnHotel =NewOleDbConnection WithcnHotel If. State = ConnectionState.OpenThen. Close() .ConnectionString = cnString .Open() EndWith You need this whether you useOleDbDataReader, ExecuteNonQuery or OleDbCommandBuilderto read or w rite into the database table. To know more active this class just click this command and press F1 key to open the help files. Be sure you installed the MSDN. Since you have already open the union to your database this is now the time to fill the ListView with data. This can be done by calling a function like FillListView(lvList, GetData(sSql)) The line of code will then execute a function Fill ListView control with data Public fill outFillListView(ByReflvListAsListView,ByRefmyDataAsOleDbDataReader) wraithlikeitmListItemAsListViewItem DimstrValueAsString Do charmmyData. Read itmListItem =NewListViewItem() strValue = IIf(myData. IsDBNull(0),, myData. GetValue(0)) itmListItem. textual matter = strValue ForshtCntr = 1TomyData. FieldCount() 1 IfmyData. IsDBNull(shtCntr)Then itmListItem. SubItems. Add() Else itmListItem. SubItems. Add(myData. GetString(shtCntr)) EndIf undermentionedshtCntr lvList. Items. Add(itmListItem) Loop EndSub Again in order to see how this code is being exec uted just 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_Click(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesbtnAdd. Click DimCustomerIDAsString frmCustomers. State = gModule. FormState. adStateAddMode ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. textbook Next frmCustomers. CustomerID = CustomerID frmCustomers. ShowDialog() CallFillList() EndSub This code will open the formfrmCustomersin add room and will execute also 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 DimCustomerIDAsString ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. textual matter Nex t 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. Text =OrtxtCompanyName. Text =Then MsgBox(Please fill up Customer ID or Company Name information. , MsgBoxStyle.C ritical) 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)( area) = 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. DBNull. Value, txtCountry.Text) . Rows(0)(Phone) = IIf(txtPhone. Text =, System. DBNull. Value, txtPhon e. 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 update a table is the same except that if you are in add mode you just apparently 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 confine and update a table. Delete a Record Let us go back tofrmCustomersListform and delete a record. The procedure before will be fired after clicking a Delete button PrivateSubbtnDelete_Click(ByValsenderAsSystem. Object,ByValeAsSystem. EventArgs)HandlesbtnDelete. Click DimCustomerIDAsString ForEachsItemAsListViewItemInlvList.SelectedItems CustomerID = sItem. Text Next IfCustomerID Then Delete the selected 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 called commencement exe rcise VB 2008 Databases From Novice to Professional (Beginning