1 / 36

Database Programming with Visual Basic .Net and MS Access

Database Programming with Visual Basic .Net and MS Access. 2009.4.29. Database Management and Analysis. IKE Lab. Yunho Song. Database Programming with Visual Basic .Net and MS Access. Contents. Installation VB .NET and Database The Database Wizard (Visual Studio users)

molimo
Download Presentation

Database Programming with Visual Basic .Net and MS Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Programming with Visual Basic .Net and MS Access 2009.4.29 Database Management and Analysis IKE Lab. Yunho Song

  2. Database Programming with Visual Basic .Net and MS Access Contents • Installation • VB .NET and Database • The Database Wizard (Visual Studio users) • Write your own VB .NET database code • Learn about DataSets and Data Adaptors • Display the Data in the DataSet • Navigate a Database with VB .NET • How to Move through the Database • Add, Update and Delete Records • Add a New Record using VB .NET • Delete a Record using VB .NET • A VB .NET Database Project

  3. 1. Installation

  4. Database Programming with Visual Basic .Net and MS Access Installation 1. Installation

  5. Database Programming with Visual Basic .Net and MS Access Installation 1. Installation

  6. Database Programming with Visual Basic .Net and MS Access Installation 1. Installation

  7. Database Programming with Visual Basic .Net and MS Access Installation 1. Installation • First run - choose the default environment settings

  8. 2. VB .NET and Database

  9. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.1. Database Wizard • Let's make a start on our Database project • Click File > New Project from the menu bar • Select Windows Application, and then give it the Name AddressBook. Click OK • Locate the Solution Explorer on the right hand side • Select Data Sources

  10. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.1. Database Wizard • The Wizard display all your tables, fields and objects

  11. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.1. Database Wizard • The Data Sources area displays information about your database • Click the plus symbol next to tblContacts

  12. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.1. Database Wizard • To add a Field to your Form • A textbox and a label will be added

  13. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.1. Database Wizard • Run your program by hitting the F5 key • Drag and Drop more Fields to your form

  14. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.2. Write your own Database code • The Connection Object • What you need if you want to connect to a database • OLE(Object Linking and Embedding) • allow you to connect to data sources in general, and not just databases. You can use it, for example, to connect to text files, SQL Server, email, and a whole lot more • Place a button on your form. Change the Name property to btnLoad • Double click your button to open up the code window • Add the following line: Dim con As New OleDb.OleDbConnection • At the top of your code window, before Public Class Form 1, type the following: • Imports System.Data

  15. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.2. Write your own Database code • Coding window will look like this • Get a pop up box from where you can select OleDbConnection • Use to connect to an Access database

  16. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.2. Write your own Database code • Setting a Connection String • The technology is called the Provider; and you use "Data Source" to specify where your database is con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\AddressBook.mdb" • The first part specifies which provider technology we want to use to do the connecting (JET) • The second part, typed after a semi-colon, points to where the database is • In the above code, the database is on the C drive, in the root folder. The name of the Access file we want to connect to is called AddressBook.mdb

  17. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.2. Write your own Database code • Opening the Connection • Open method of the Connection Object: • Close method of the Connection Object: con.Open( ) MsgBox("A Connection to the Database is now open“) con.Close() MsgBox("The Connection to the Database is now Closed“)

  18. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.2. Write your own Database code • Coding window • Test out our code

  19. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.3. Data Sets and Data Adapters • Data Sets • Hidden from you, and just stored in memory • Imagine a grid with rows and columns. Each imaginary row of the DataSet represents a Row of information in your Access database. And each imaginary column represents a Column of information in your Access database (called a Field in Access) • Data Adapter • The Connection Object and the DataSet can't see each other • They need a go-between so that they can communicate • This go-between is called a Data Adapter • The Data Adapter contacts your Connection Object, and then executes a query that you set up. The results of that query are then stored in the DataSet.

  20. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.3. Data Sets and Data Adapters • Data Adapter and DataSet are objects • OleDb.OleDbDataAdapter • Called da • Hold a reference to the Data Adapter • da = New OleDb.OleDbDataAdapter(sql, con) • creates a new Data Adapter object • Need to put two things in the round brackets of the Object declaration • SQL string (which we'll get to shortly), and connection object • Connection Object is stored in the variable called con • Data Adaptor acting as a go-between for the Connection Object and the Data Set Dim ds As New DataSetDim da As OleDb.OleDbDataAdapter da = New OleDb.OleDbDataAdapter(sql, con)

  21. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.3. Data Sets and Data Adapters • Structured Query Language(SQL) • A way to query and write to databases • Hold a reference to the Data Adapter • To select just the first name and surname columns from our database • To SELECT all (*) the records from the table called tblContacts Select * from Table_Name SELECT tblContacts.FirstName, tblContacts.Surname FROM tblContacts sql = "SELECT * FROM tblContacts”

  22. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.3. Data Sets and Data Adapters • Structured Query Language(SQL) Dim ds As New DataSet Dim da As OleDb.OleDbDataAdapter Dim sql As String sql = "SELECT * FROM tblContacts" da = New OleDb.OleDbDataAdapter(sql, con)

  23. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.3. Data Sets and Data Adapters • Filling the DataSet • Data Adapter can Fill a DataSet with records from a Table • The DataSet (ds) will now be filled with the records we selected from the table called tblContact • One slight problem - nobody can see the data yet! We'll tackle that in the next part da = New OleDb.OleDbDataAdapter(sql, con) da.Fill(ds, "AddressBook“)

  24. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.4. Displaying the Data in the DataSet • To display the records on a Form • Add two textboxes to your form • Change the Name properties of your textboxes to txtFirstName and txtSurname • Go back to your code window • Add the following two lines: txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1) txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)

  25. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.5. Navigate a Database • To see a more practical example • Add two Textboxes. Change the Name properties to txtFirstName and txtSurname • Add four Buttons. Change the Name and Text properties to these:

  26. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.5. Navigate a Database • To see a more practical example • Add the following code to the Form1 Declarations area: • When the Form Loads, we can connect to our database, use the data Adaptor to grab some records from the database and then put these records into the DataSet • So in the Form1 Load Event, add the following code:

  27. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.5. Navigate a Database • To see a more practical example • You've met all the code before, except for these two lines: • In the MaxRows variable, we can store how many rows are in the DataSet • Get how many rows are in DataSet with Rows.Count: • To navigate through the records • Use inc variable. We'll either add 1 to it, or take 1 away • Use the variable for the Rows in the DataSet • It's better to do this in a Subroutine of own • So add this Sub to code: MaxRows = ds.Tables("AddressBook").Rows.Count inc = -1 Private Sub NavigateRecords() txtFirstName.Text = ds.Tables("AddressBook").Rows(inc).Item(1) txtSurname.Text = ds.Tables("AddressBook").Rows(inc).Item(2) End Sub

  28. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.5. Navigate a Database • The important part is Rows(inc). This moves us through the Rows in the DataSet. Then placing the values into the two Textboxes

  29. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.6. How to Move through the Database • How to Move Forward One Record at a Time • Double click your Next Record button to access the code If inc <> MaxRows - 1 Then inc = inc + 1 NavigateRecords() Else MsgBox("No More Rows") End If • Move Back One Record at a Time • to add to your btnPrevious: If inc > 0 Then inc = inc - 1 NavigateRecords() ElseIf inc = -1 Then MsgBox("No Records Yet") ElseIf inc = 0 Then MsgBox("First Record") End If

  30. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.6. How to Move through the Database • Moving to the Last Record in the DataSet • to add to your btnLast: If inc <> MaxRows - 1 Then inc = MaxRows - 1 NavigateRecords() End If • Moving to the First Record in the DataSet • to add to your btnFirst: If inc <> 0 Then inc = 0 NavigateRecords() End If

  31. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.7. Add, Update and Delete Records • DataSet is disconnected from the database • Not adding the record to the database: adding it to the DataSet • How to add, delete, update new records • Add five more buttons to your form • Change the Name properties btnAddNewbtnCommitbtnUpdatebtnDeletebtnClear

  32. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.7. Add, Update and Delete Records • Updating a Record • The changes will just get made to the DataSet • Add the following code to btnUpdate: ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text MsgBox("Data updated“) • "Changes are made to the DataSet, and NOT to the Database“ • To update the database, Add following code Dim cb As New OleDb.OleDbCommandBuilder(da) ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text da.Update(ds, "AddressBook") MsgBox("Data updated“)

  33. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.8. How to Add a New Record • Add a New Record • Add New Record button: btnCommit.Enabled = True btnAddNew.Enabled = False btnUpdate.Enabled = False btnDelete.Enabled = False txtFirstName.Clear() txtSurname.Clear() • The Clear/Cancel button can be used to switch it back on again • Add this code to btnClear: btnCommit.Enabled = False btnAddNew.Enabled = True btnUpdate.Enabled = True btnDelete.Enabled = True inc = 0 NavigateRecords()

  34. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.8. How to Add a New Record • To add a new record to the database • Add this code to btnCommit • If inc <> -1 Then • Dim cb As New OleDb.OleDbCommandBuilder(da) • Dim dsNewRow As DataRow • dsNewRow = ds.Tables("AddressBook").NewRow() • dsNewRow.Item("FirstName") = txtFirstName.Text • dsNewRow.Item("Surname") = txtSurname.Text • ds.Tables("AddressBook").Rows.Add(dsNewRow) • da.Update(ds, "AddressBook“) • MsgBox("New Record added to the Database“) • btnCommit.Enabled = False • btnAddNew.Enabled = True • btnUpdate.Enabled = True • btnDelete.Enabled = True • End If

  35. Database Programming with Visual Basic .Net and MS Access VB .NET and Database 2.8. Delete a Record from a Database • Deleting Records from a Database • Add this code to btnDelete Dim cb As New OleDb.OleDbCommandBuilder(da) ds.Tables("AddressBook").Rows(inc).Delete() MaxRows = MaxRows - 1 inc = 0 NavigateRecords() da.Update(ds, "AddressBook“) • To display a message box asking users if they really want to delete this record If MessageBox.Show("Do you really want to Delete this Record?", _ "Delete", MessageBoxButtons.YesNo, _ MessageBoxIcon.Warning) = DialogResult.No Then MsgBox("Operation Cancelled") Exit Sub End If

  36. Thank You!!

More Related