Binding Buttons for Multiple Tables

Question

I'm designing a windows user form. You can see the form here, the issue is that the most of the text boxes are never populated with data. I'm trying to use the buttons on the top of the form to "browse" the database and add, modify, and delete records.

I don't seem to have the right SQL query to grab all the data from the different tables to that my DataAdapter can send it to the form. I'm looking for help in generating the right query and passing data to the form. You can see the makeup of the database here. If anyone could help me out that would be awesome.

The code for the form is below.

Imports System.Data.OleDb

Public Class DeviceEditorForm
Dim tabletDataAdapter As OleDbDataAdapter
Dim tabletDataSet As DataSet

Private Sub DeviceEditorForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Me.DeviceTableAdapter.Fill(Me.BIT_3444_DatabaseDataSet.Device)
    Dim sqlStatement As String = "select * from Device"
    Dim tabletConnectionStr As String = "provider=microsoft.ace.oledb.12.0;data source=" & Application.StartupPath & "\BIT 3444 Database.accdb"

    tabletDataAdapter = New OleDbDataAdapter(sqlStatement, tabletConnectionStr)

    tabletDataSet = New DataSet

    tabletDataAdapter.Fill(tabletDataSet, "Device")

    DeviceBindingSource.DataSource = tabletDataSet.Tables(0)
    BindingNavigator1.BindingSource = DeviceBindingSource

    Dim batterySizeBinding As New Binding("text", DeviceBindingSource, "Battery_Size")
    BatterySizeTextBox.DataBindings.Add(batterySizeBinding)

    Dim IDBinding As New Binding("text", DeviceBindingSource, "ID")
    DeviceIdTextBox.DataBindings.Add(IDBinding)

    Dim nameBinding As New Binding("text", DeviceBindingSource, "Device_Name")
    DeviceNameTextBox.DataBindings.Add(nameBinding)

    Dim manufacturerBinding As New Binding("text", DeviceBindingSource, "Manufacturer")
    ManufacturerTextBox.DataBindings.Add(manufacturerBinding)

    Dim modelYearBinding As New Binding("text", DeviceBindingSource, "Model_Year")
    ModelYearTextBox.DataBindings.Add(modelYearBinding)

    Dim osBinding As New Binding("text", DeviceBindingSource, "Operating_System")
    OperatingSystemTextBox.DataBindings.Add(osBinding)

    Dim osVersionBinding As New Binding("text", DeviceBindingSource, "Operating_System_Version")
    OsVersionTextBox.DataBindings.Add(osVersionBinding)

    Dim priceBinding As New Binding("text", DeviceBindingSource, "Price")
    PriceTextBox.DataBindings.Add(priceBinding)

End Sub

Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
    DeviceBindingSource.EndEdit()

    Dim tabletCommandBuilder As New OleDbCommandBuilder(tabletDataAdapter)
    tabletDataAdapter.InsertCommand = tabletCommandBuilder.GetInsertCommand()
    tabletDataAdapter.DeleteCommand = tabletCommandBuilder.GetDeleteCommand()
    tabletDataAdapter.UpdateCommand = tabletCommandBuilder.GetUpdateCommand()

    Dim numOfRecords As Integer = tabletDataAdapter.Update(tabletDataSet, "Device")
    MsgBox(numOfRecords & " records have been updated.")
End Sub
End Class

Gord Thompson · Accepted Answer · 2013-04-28 10:22:16Z

Although you've framed your question in terms of form design and data binding, you'll need to go back and make some changes to your data model first. There are some fundamental problems with your data model that will make it difficult to build your application. Once those are resolved you should find it a lot easier to build the form(s) and manipulate the data.

Currently all of the tables except [Device] have a [Device_ID] foreign key which points back to the related record in the [Device] table. That makes the [Device] table the Parent and the other table the Child, and the nature of that relationship is that a Mother (Parent) can have multiple Children but a Child can only have one Mother. In your case that relationship does not always make sense with your tables as they stand now.

Problem: [Physical_Specs] as a child table

Having [Physical_Specs] as a child table makes no sense. The attributes [Color], [Weight], etc., should be in the [Device] table because each device can only have one value for each of those attributes (e.g., a device can only have one [Weight]). Furthermore, saving all possible combinations of [Color], [Height], [Width], [Weight] ... in a separate reference table for possible re-use with other devices offers no real benefit.

You should think about whether it makes sense to incorporate the [Connectivity] and [Storage] attributes into the [Device] table as well.

Problem: [Screen] as a child table

When [Screen] is the child table you will have to maintain a separate [Screen] record for each [Device], and if several devices use the same screen then you'll have to duplicate that information in the [Screen] table. That is backwards. In reality a single screen (OEM part) can be used by many devices, but any given device will only have one screen. The way your current data model is set up is the opposite: a device (parent) can have multiple screens (children), but a screen record (child) can only have one parent record.

Instead of having a [Device_ID] foreign key in [Screen], the [Device] table should have a [Screen_ID] foreign key that points to the appropriate [Screen] record. That allows several devices to reference the same [Screen] record, and each device will only have one such reference (i.e., each device will only have one screen).

I've singled out [Screen] here but the same reasoning applies to the [Battery] and [Camera] tables.

Not a problem: [Pictures] and [Reviews] as child tables

As suggested by the plural names you've given to these tables, having them as child tables does make sense because you could have several pictures or reviews for a given product, and each of those pictures or reviews would refer to a single device.

Thanks I committed the changes you recommended but now my binding add item and delete buttons do not work I'm getting an SQL error

Collectives™ on Stack Overflow

Binding Buttons for Multiple Tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related