is2215 lecture8 relational_databases
DESCRIPTION
TRANSCRIPT
![Page 1: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/1.jpg)
RELATIONAL DATABASEDEALING WITH MORE THAN ONE TABLE
![Page 2: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/2.jpg)
Set up the objectsDim objConnection As New
OleDb.OleDbConnection( _ "Provider=Microsoft.Ace.OLEDB.12.0;Data
Source =C:\DataBase.accdb")Dim objTableDA As New _
OleDb.OleDbDataAdapter("Select * from Table", _ objConnection)
Dim objTableCB As New _ OleDb.OleDbCommandBuilder(objTableDA)
Dim objDataSet As New DataSet()
1. Setting up Objects
![Page 3: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/3.jpg)
2. Filling the DataSet
objDataSet.Clear()
objTableDA.FillSchema(objDataSet, _ SchemaType.Source, “Table")
objTableDA.Fill(objDataSet, “Table")
![Page 4: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/4.jpg)
3. Displaying Data on Form
Dim objRow As DataRow objRow = _ objDataSet.Tables(“Table").
Rows.Find _ (Insert Code for Primary Key Here)
lblField1Text = objRow.Item(“Field1") txtField2.Text = objRow.Item(“Field2") txtField3.Text = objRow.Item(“Field3") End Sub
![Page 5: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/5.jpg)
Row 0Row 1Row 2Row 3
Column 0
![Page 6: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/6.jpg)
4.Storing Details
Public Sub StoreDetails() Dim objRow As DataRow objRow = _
objDataSet.Tables(“Table").Rows.Find(PrimaryKey)
objRow.Item(“Field1") = txtField1.Text objRow.Item(“Field2") = txtField2.Text End Sub
![Page 7: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/7.jpg)
5. Update Changes to DataSet In the previous slide we updated our
database but…
objTableDA.Update(objDataSet, “Table”)
![Page 8: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/8.jpg)
6.Add a New Row to DataSet
Dim objRow As DataRowobjRow =
objDataSet.Tables(“Table”).NewRowobjRow.Item(“Field1”) = InputBox(“Field1?”)objRow.Item(“Field2”) = InputBox(“Field2?”)objRow.Item(“Field3”) = InputBox(“Field3?”)objDataSet.Tables(“Table”).Rows.Add(objRo
w)objTableDA.Update(objDataSet, “Table”)
![Page 9: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/9.jpg)
7. Deleting a Row
Dim objRow As DataRow objRow =
objDataSet.Tables(“Tables").Rows.Find(PrimaryKey)
objRow.Delete() objTableDA.Update(objDataSet, “Table")
Retrieve()
![Page 10: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/10.jpg)
![Page 11: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/11.jpg)
1.Setting up Objects
Dim objConnection As New OleDb.OleDbConnection( _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source _ =surgery.accdb")
Dim objOwnerDA As New OleDb.OleDbDataAdapter("Select * from Owners", _ objConnection)
Dim objOwnerCB As New OleDb.OleDbCommandBuilder(objOwnerDA)
Dim objDS As New DataSet()
![Page 12: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/12.jpg)
2.Fill the Data Set
objDS.Clear()
objOwnerDA.FillSchema(objDS, _ SchemaType.Source, "Owners")
objOwnerDA.Fill(objDataSet, "Owners")
![Page 13: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/13.jpg)
Row 0Row 1Row 2Row 3
![Page 14: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/14.jpg)
Row 0Row 1Row 2Row 3
‘objDS.Tables("Owners").Rows.Count = 4‘For i = 0 to 4 would cause a problem‘There is no row 4!
![Page 15: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/15.jpg)
3.Populate ComboBox cboOwners.Items.Clear()
Dim i As Integer, strCurrentID As String
For i = 0 To objDS.Tables("Owners").Rows.Count - 1
strCurrentID = _
objDS.Tables("Owners").Rows(i).Item("OwnerID")
cboOwners.Items.Add(strCurrentID)
Next
cboOwners.SelectedIndex = 0
![Page 16: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/16.jpg)
4.Display a Record
Public Sub FillOwnersDetails() Dim objRow As DataRow objRow = _ objDS.Tables("Owners").Rows.Find _ (cboOwners.SelectedItem.ToString)
lblOwnerID.Text = _ objRow.Item("OwnerID")
txtName.Text = objRow.Item("Name") txtAddress.Text = objRow.Item("Address") End Sub
![Page 17: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/17.jpg)
5.Storing Details
Public Sub StoreOwnerDetails()
Dim objRow As DataRowIf lblOwnerID.Text = "" Then Exit SubobjRow = _ objDS.Tables("Owners").Rows.Find _ (lblOwnerID.Text)
objRow.Item("Name") = txtName.Text objRow.Item("Address") = _ txtAddress.Text
End Sub
![Page 18: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/18.jpg)
6.ImplementProcedures
Call the Fill Owner Details behind the Combo Box
![Page 19: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/19.jpg)
7.Persisting Changes
Code to be placed behind the save button
If the user has made changes to a field we want to store the changes to the dataset and then persist those changes to the DB
1.Call the StoreOwnerDetails()2.objOwnerDA.Update(objDS, “Owners”)
![Page 20: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/20.jpg)
8.Add Row to our DataSetDim objRow As DataRowobjRow = objDS.Tables(“Owners”).NewRowobjRow.Item(“OwnerID”) = InputBox(“Owner
ID?”)objRow.Item(“Name”) = InputBox(“Name?”)objRow.Item(“Address”) =
InputBox(“Address?”)objDS.Tables(“Owners”).Rows.Add(objRow)StoreOwnerDetails()objOwnerDA.Update(objDS, “Owners”)
Remember to loop through records again !
![Page 21: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/21.jpg)
9.Deleting a Row
Dim objRow As DataRow objRow = _ objDS.Tables("Owners")._ Rows.Find(cboOwners.SelectedItem.ToString)
objRow.Delete() objOwnerDA.Update(objDS, "Owners")Retrieve()
![Page 22: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/22.jpg)
10.Creating Relationships
We have created two tables owners and pets.
What if we want to use both tables on our form?
Pull data from both tables into our dataset
Then set up an OwnerID relationship between the two.
![Page 23: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/23.jpg)
![Page 24: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/24.jpg)
11.Set up more Objects
Dim objPetDA as New _ OleDbDataAdapter(“Select * from Pets”,_ , objConnection)Dim objPetCB As New _OleDbCommandBuilder(objPetDA)
![Page 25: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/25.jpg)
lstPets
![Page 26: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/26.jpg)
Filling in Pet Details
Add the following code to your Retrieve button code or Retrieve Method
It should go in after you have filled the dataset with Owners data
objPetDA.FillSchema(objDataSet, _ SchemaType.Source, "Pets")objPetDA.Fill(objDS, "Pets")'Setup our RelationshipobjDS.Relations.Clear()objDS.Relations.Add("Owners2Pets", _objDS.Tables("Owners").Columns("OwnerID"), _objDS.Tables("Pets").Columns("OwnerID"))
![Page 27: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/27.jpg)
FillPetDetails()
Public Sub FillPetDetails()
Dim objOwner As DataRow, objPet As _ DataRowDim strPetEntry As StringlstPets.Items.Clear()
objOwner = objDS.Tables(“Owners”)._ Rows.Find(cboOwners.SelectedItem.ToString)
![Page 28: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/28.jpg)
Loop through records
For Each objPet in objOwner.GetChildRows(“Owners2Pets”)
strPetEntry = objItem(“PetID”) & “, “ ” & _
objPet.Item(“PetName”) & “, “ & objPet.Item(“Type”)
lstPets.Items.Add(strPetEntry)
Next
End Sub
![Page 29: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/29.jpg)
Finishing Touches
To end of your retrieve records code add the following line:
FillPetDetails() Add the following code to the
SelectedIndexChanged event of our combo box:
FillPetDetails()
![Page 30: Is2215 lecture8 relational_databases](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c2eca54a7959c43b8b457a/html5/thumbnails/30.jpg)