'How to Connect a Database and Add/Update/Delete/Record\r'

'How to Connect to a Database and institute/ modify/ call off enter In this tutorial I pull up stakes explain to you on how to tie to an Access selective in puddleationbase and allow you to Add/Update/ blue-pencil a usher. To fully understand these tutorials please  transfer the origination tag How to Add/Update/ erase Record using MS Access Database. This source code is part of the Hotel Reservation System that I am currently working. At the end of this tutorial you leave alone nobble the basic of database course of studying. I would like, however, to mark peculiarly for beginners that one way to adopt scheduleming is to sleep with how to debug a program and intent some of your conviction to reading.Dont be frightened on how short or long an article should be. The Copernican is at the end of the tutorial you w sorrow learn something NEW! If you already know the proposition, because fall apart’t bother to study this again. Table of co ntent 1. Introduction 2. let’s get started 3. Database inter-group communication 4. Add and Update a Record 5. wipe out a Record 6. Final Thoughts Introduction in the first place I started learning VB. NET one of the topic that I search for in the inter web is on how to merge to the database and make some changes to the table. Although there’s a lot of results, but I cannot bring forth one that suit to my motivations.Most of the tutorial is using allure and drop features of vb. net tailoror. Well, this is okay in just about cases but what if you’d like to manipulate the data by code? So, I created this tutorial so that beginner programmer go forth learn from this. Let’s get started It is very alpha that you use your common sense to understand the logic of database program. there’s a lot of features built-in to opthalmic Basic Editor that most programmer especially beginner who overlook it. One of the favorite tools I usually used is the DEBUGGER. If you only knew how serious a debugger is, thence you do not even motivation to study this tutorial.Why? Because you can jump right forward to the source code and start firing the F8 financial statement from your severalizeboard and analyze every suck up as you spirit through the code. Anyway beginner is a beginner. You contract to start from scratch. If you founder already downloaded the source code, then open it in the visual basic . net editor by two-fold clicking the â€Å"HowtoAddUpdateDeleteRecord. sln”. If you want to know what is the inclination that runs the first time you start the program (by pressing F5) then double click the â€Å"My stray” at the Solution Explorer. Look at the inaugural Form.You will see that the value is â€Å"frm nodesList”. Now, click this object in the Solution Explorer and click the keep an eye on Code at the toolbar. Look for the incumbrance military issue similar at a lower place: secret   molar frmCustomersList_Load(ByVal  vector As System. Object, ByVal e As System. EventArgs)Handles MyBase. Load         sSql = â€Å" assign CustomerID, CompanyName, ContactName, ContactTitle, Address FROM Customers ORDER BY CustomerID ASC”         Call FillList() FillListView(lvList, GetData(sSql)) destruction  stand in frmCustomersList_Load is the second unconscious process that runs when you hit the F5 winder from your keyboard.If you’d like to know how this code is penalise then press F8. Believe it or not F8 is the answer to all your programming question. And I rightfully mean it. When I started programming all I do is to search for free source code and start using the debugging tool. That’s why optic Basic is being named as Rapid finish Development or RAD. If you follow the debugger the first bank bill it executes is the Private  hitman frmCustomersList_Resi ze(ByVal senderAs Object, ByVal e As System. EventArgs) then followed by frmCustomersList_Load which is actually the important mapping to broadsheet here.Another important debugging tool is â€Å" on-off switch 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 rectify menu then Toggle Breakpoint. This tool is important if the form is already loaded and you want to piece of ground the execution of a code say inwardly a command sack. For ex angstromle. Open the form frmCustomersList and double click the make up button and move the up arrow key once and press F9. You willl be in possession of a picture as shown below: [inline:Toggle Breakpoint. jpg]Now, 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 ar executing the progra m. Isn’t it nice? Database Connection In order to connect to the database you need a society string like this: reality Const cn range As  pull in = â€Å"Provider=Microsoft. Jet. OLEDB. 4. 0;Persist Security data=False;Data Source=.. /data/s adeninele. mdb” accordingly open it by using this command: lightheaded cnHotel As OleDbConnection cnHotel = New OleDbConnection With cnHotel If . enjoin = ConnectionState.Open Then . Close() .ConnectionString = cnString .Open() kibosh With You need this whether you use OleDbDataReader, ExecuteNonQuery or OleDbCommandBuilder to read or write into the database table. To know more about this single out 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 contact to your database this is now the time to fill the ListView with data. This can be through by calling a function like: FillListView(lvList, GetData(sSql)) The line of code will then execute a function: Fill ListView control with data Public  step in FillListView(ByRef lvList As ListView, ByRef myData As OleDbDataReader)          dismal itmListItem As ListViewItem Dim strValue As String Do  eyepatch myData. Read itmListItem = New ListViewItem() strValue = IIf(myData. IsDBNull(0), â€Å"”, myData. GetValue(0))             itmListItem. school text = strValue For shtCntr = 1 To myData. FieldCount() †1                 If myData. IsDBNull(shtCntr) Then                     itmListItem. cuneusItems. Add(â€Å"”)                 Else itmListItem. SubItems. Add(myData. GetString(shtCntr))           à ‚ Â Â Â Â Â  finis If following shtCntr lvList. Items. Add(itmListItem) Loop End Sub Again in order to see how this code is being put to death 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. Private Sub btnAdd_ piffle(ByVal sender As System. Object, ByVal e As System. EventArgs) HandlesbtnAdd. Click         Dim CustomerID As String frmCustomers. State = gModule. FormState. adStateAddMode         For Each sItem As ListViewItem In lvList.SelectedItems             CustomerID = sItem. text edition beside frmCustomers. CustomerID = CustomerID frmCustomers. ShowDialog() Call FillList() End Sub This code will open the form frmCustomers in convey agency and will e xecute also its own Load Event. If you want to open the form frmCustomers in edit mode, then just double click the stage in a ListView. The code being executed are: Private Sub lvList_DoubleClick(ByVal sender As Object, ByVal e As System. EventArgs) HandleslvList. DoubleClick         Dim CustomerID As String For Each sItem As ListViewItem In lvList.SelectedItems             CustomerID = sItem. text withstand Next With frmCustomers .State = gModule. FormState. adStateEditMode             . CustomerID = CustomerID .ShowDialog() Call FillList() End With frmCustomers = Nothing End Sub 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 t he 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. Private Sub btnSave_Click(ByVal sender As System. Object, ByVal e As System. EventArgs) HandlesbtnSave. Click         Dim dt As DataTable = dsCustomers. Tables(â€Å"Customers”)         If txtCustomerID. textbook = â€Å"” Or txtCompanyName. school text = â€Å"” Then             MsgBox(â€Å" divert fill up Customer ID or Company Name information. â€Å", MsgBoxStyle.Critical)             Exit Sub End If Try If State = gModule. FormState. adStateAddMode Then   Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â â€˜ add a row Dim newRow As DataRow newRow = dt. NewRow() newRow(â€Å"CustomerID”) = txtCustomerID. Text                 dt. Rows. Add(newRow) End If With dt .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 = â€Å"”, Syste m. DBNull. Value, txtAddress. Text)                 . Rows(0)(â€Å"City”) = IIf(txtCity. Text = â€Å"”, System. DBNull. Value, txtCity. Text)                 . Rows(0)(â€Å" portion”) = 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)(â€Å"sound”) = 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)             End With Catch ex As OleDbException MsgBox(ex. ToString) End Try End Sub The code for adding and update a table is the same except that if you are in add mode you just evidently add this command: If State = gModule.FormState. adStateAddMode Then ‘ add a row Dim newRow As DataRow 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 cut-in and update a table. Delete a Record Let us go back to frmCustomersList form and delete a record. The procedure before will be fired after clicking a Delete button: Private Sub btnDelete_Click(ByVal sender As System. Object, ByVal e As System. EventArgs) HandlesbtnDelete. Click         Dim CustomerID As String For Each sItem As ListViewItem In lvList.SelectedItems             CustomerID = sItem. Text Next If CustomerID ;; â€Å"” Then ‘Delete the selected record Dim strDeleted As Boolean strDeleted = ExecNonQuery(â€Å"DELETE Customers. CustomerID FROM Customers WHERE CustomerID= ‘” & CustomerID & â€Å"‘”)             If strDeleted = â€Å"True” Then MsgBox(â€Å"Records deleted. â€Å", MsgBoxStyle. Information)                 Call FillList() Else MsgBox(strDeleted) End If Else MsgBox(â€Å" Please select record to delete. â€Å", MsgBoxStyle. Critical)         End If End Sub The important line here is the strDeleted = ExecNonQuery(â€Å"DELETE Customers.CustomerID FROM Customers WHERE CustomerID= ‘” & CustomerID & â€Å"‘”) which call the function ExecNonQuery and 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 you’re 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 Beginning VB 2008 Databases: From initiate to Professional (Beginning:\r\n'