none
Data Relations RRS feed

  • Question

  • Hi:

    I have two tables. One with customers (SE) and one with customer types (ST). Instead of placing the customer type in each customer record, I want to place a key value for the customer type instead. In this way, the Customer Type is only found once in the Customer Type table. I want to avoid data redundancy. Also, if I set up a new customer type and I mispelled it but didn't find out right away, I would only have to correct the error once and it would then appear correctly in each customer record. 

    Getting the combobox to enter the key value is no problem. What I can't figure out is how to set up a datarelation that will display the customer type in a label based on the key value. I am not using a datagridview but labels and textboxes.

    There are multiple customers and multiple customer types so I guess that would be a many-to-many deal.

    This is the code behind the customer form.

            'Create A DataRelation.
            Dim data_relation As New _
            DataRelation("SE_ST", dsPSB.Tables("dtSE").Columns("SE_ST_KEY"),         dsPSB.Tables("dtST").Columns("ST_KEY"))
            dsPSB.Relations.Add(data_relation)
            'Child Table BindingSource.
            bsST.DataSource = bsSE
            bsST.DataMember = dsPSB.Relations(0).RelationName
            Label1.DataBindings.Add("Text", bsST, "ST_DESC")

    I would really appreciate some help.

    Thanks




    Monday, June 29, 2020 1:14 PM

All replies

  • Hi,
    which type of project? Windows Form? WPF? UWP? Core?

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, June 29, 2020 1:48 PM
  • Hi

    Win form


    Monday, June 29, 2020 1:54 PM
  • A many-to-many happens seldom. It can be that one customer can be of a more customer types.

    You can only fix that with an extra table, therefore is no one to one solution in SQL. 

    Mostly a customer is of one customer type. If that is not the case, then first investigate what the sense is of giving your customer a type. 

    In the first thinking process most start with thinking that the customer type is a child of a Customer. 

    But that is not the case. The Type is the master and the Customer a slave. 

     

    Success
    Cor


    Monday, June 29, 2020 2:20 PM
  • Hi,
    set "False" for Contraints! Try following demo:

    Public Class Form1
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {dgv, Label1})
        LoadData()
        dgv.DataSource = bsSE
        SetRelation()
      End Sub
    
      Private dsPSB As New DataSet
      Private bsST As New BindingSource
      Private bsSE As New BindingSource
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
      Private Label1 As New Label With {.Dock = DockStyle.Bottom}
    
      Private Sub LoadData()
        With dsPSB
          With .Tables.Add("dtSE")
            .Columns.Add("SE_KEY", GetType(Integer))
            .Columns.Add("SE_ST_KEY", GetType(Integer))
            .Columns.Add("SE_DESC", GetType(String))
            With .Rows
              For i = 1 To 100
                .Add(i, i Mod 10 + 1, $"customer {i}")
              Next
            End With
          End With
          With .Tables.Add("dtST")
            .Columns.Add("ST_KEY", GetType(Integer))
            .Columns.Add("ST_DESC", GetType(String))
            With .Rows
              For i = 1 To 10
                .Add(i, $"customer types {i}")
              Next
            End With
          End With
        End With
        bsSE.DataSource = dsPSB.Tables("dtSE")
      End Sub
    
    
      Private Sub SetRelation()
        'Create A DataRelation.
        Dim data_relation As New _
          DataRelation("SE_ST", dsPSB.Tables("dtSE").Columns("SE_ST_KEY"), dsPSB.Tables("dtST").Columns("ST_KEY"), False)
    
        dsPSB.Relations.Add(data_relation)
    
        'Child Table BindingSource.
        bsST.DataSource = bsSE
        bsST.DataMember = dsPSB.Relations(0).RelationName
    
        Label1.DataBindings.Add("Text", bsST, "ST_DESC")
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks



    Monday, June 29, 2020 2:21 PM
  • I keep telling you people the same thing. I am not using a datagridview! I am using textboxes and labels! This is garbage. If you can't answer the question just don't waste my time!




    Monday, June 29, 2020 2:28 PM
  • Hi,
    where's the problem to use TextBox instead if DataGrid? 

    Try following demo:

    Public Class Form1
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {Label1, lbl1a, tb2, lbl2a, btnNext})
        LoadData()
        SetRelation()
        tb2.DataBindings.Add("Text", bsSE, "SE_DESC")
      End Sub
    
      Private dsPSB As New DataSet
      Private bsST As New BindingSource
      Private bsSE As New BindingSource
    
      Private lbl2a As New Label With {.Dock = DockStyle.Top, .Text = "Selected customer:"}
      Private tb2 As New TextBox With {.Dock = DockStyle.Top}
      Private lbl1a As New Label With {.Dock = DockStyle.Top, .Text = "Type of selected customer:"}
      Private Label1 As New Label With {.Dock = DockStyle.Top}
    
      Private WithEvents btnNext As New Button With {.Dock = DockStyle.Top, .Text = "select next customer"}
    
      Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If bsSE.Position < bsSE.Count - 1 Then bsSE.MoveNext() Else bsSE.MoveFirst()
      End Sub
    
      Private Sub SetRelation()
        'Create A DataRelation.
        Dim data_relation As New _
          DataRelation("SE_ST", dsPSB.Tables("dtSE").Columns("SE_ST_KEY"), dsPSB.Tables("dtST").Columns("ST_KEY"), False)
    
        dsPSB.Relations.Add(data_relation)
    
        'Child Table BindingSource.
        bsST.DataSource = bsSE
        bsST.DataMember = dsPSB.Relations(0).RelationName
    
        Label1.DataBindings.Add("Text", bsST, "ST_DESC")
      End Sub
    
      Private Sub LoadData()
        With dsPSB
          With .Tables.Add("dtSE")
            .Columns.Add("SE_KEY", GetType(Integer))
            .Columns.Add("SE_ST_KEY", GetType(Integer))
            .Columns.Add("SE_DESC", GetType(String))
            With .Rows
              For i = 1 To 100
                .Add(i, i Mod 10 + 1, $"customer {i}")
              Next
            End With
          End With
          With .Tables.Add("dtST")
            .Columns.Add("ST_KEY", GetType(Integer))
            .Columns.Add("ST_DESC", GetType(String))
            With .Rows
              For i = 1 To 10
                .Add(i, $"customer type {i}")
              Next
            End With
          End With
        End With
        bsSE.DataSource = dsPSB.Tables("dtSE")
      End Sub
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, June 29, 2020 5:43 PM
  • Thanks.

    I modified your code to fit my situation but I get the following error. "Cannot bind to the property or column ST_DESC on the DataSource. Parameter name: dataMember".

    I have a feeling that the issue lies with the bindingsource somewhere.

    Option Explicit On
    Option Strict On

    Imports System.Data
    Imports System.Reflection
    Imports System.Data.OleDb

    Public Class Form5
        Private Sub Form5_Load(sender As Object, e As EventArgs) Handles MyBase.Load

            LoadData()
            SetRelation()
            TB2.DataBindings.Add("Text", bsSE, "SE_DESC")
        End Sub

        Private dsPSB As New DataSet
        Private bsST As New BindingSource
        Private bsSE As New BindingSource

        Private Sub SetRelation()
            'Create A DataRelation.
            Dim data_relation As New _
              DataRelation("SE_ST", dsPSB.Tables("dtSE").Columns("SE_ST_KEY"), dsPSB.Tables("dtST").Columns("ST_KEY"), False)

            dsPSB.Relations.Add(data_relation)

            'Child Table BindingSource.
            bsST.DataSource = bsSE
            bsST.DataMember = dsPSB.Relations(0).RelationName

            LBL1A.DataBindings.Add("Text", bsST, "ST_DESC")
        End Sub

        Private Sub LoadData()

            'Create The Service DataReader.
            Dim cmdSE As OleDbCommand = New OleDbCommand("SELECT SE_KEY, SE_DESC, SE_ST FROM SE", cnPSB)

            Dim drSE As OleDbDataReader

            'Load Data Into The Service DataTable.
            cnPSB.Open()
            drSE = cmdSE.ExecuteReader()
            dsPSB.Load(drSE, LoadOption.OverwriteChanges, "dtSE")
            cnPSB.Close()

            'Create The Service Type DataReader.
            Dim cmdST As OleDbCommand = New OleDbCommand("SELECT ST_KEY, ST_DESC FROM ST", cnPSB)

            Dim drST As OleDbDataReader

            'Load Data Into The Service Type DataTable.
            cnPSB.Open()
            drST = cmdST.ExecuteReader()
            dsPSB.Load(drST, LoadOption.OverwriteChanges, "dtST")
            cnPSB.Close()


            'bsSE.DataSource = dsPSB.Tables("dtSE")


            bsSE.DataSource = dsPSB
            bsSE.DataMember = "dtSE"

        End Sub

        Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
            bsSE.MoveFirst()
        End Sub

        Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
            bsSE.MoveNext()
        End Sub

        Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
            bsSE.MovePrevious()
        End Sub

        Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
            bsSE.MoveLast()
        End Sub
    End Class


    Monday, June 29, 2020 6:58 PM
  • Hi,
    your code is incorrect:

            Dim data_relation As New _
              DataRelation("SE_ST", dsPSB.Tables("dtSE").Columns("SE_ST_KEY"), dsPSB.Tables("dtST").Columns("ST_KEY"), False)
    ...
            Dim cmdSE As OleDbCommand = New OleDbCommand("SELECT SE_KEY, SE_DESC, SE_ST FROM SE", cnPSB)
    

    The best way to check data is to use DataGrid.

      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {Label1, lbl1a, tb2, lbl2a, btnNext})
        LoadData()
        'SetRelation()
        'tb2.DataBindings.Add("Text", bsSE, "SE_DESC")
    
        Dim frm2 As New Form
        Dim dg As New DataGrid With {.Dock = DockStyle.Fill}
        frm2.Controls.Add(dg)
        dg.DataSource = dsPSB
        frm2.Show()
      End Sub


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, June 29, 2020 8:10 PM
  • No my code is fine. In my table I have column "SE_ST" but in my thread I am using "SE_ST_KEY" just so that

    you can see that I am using a key value. When I copied the code to the thread I just forgot to change the one occurrence of "SE_ST" to "SE_ST_KEY". I intend to change the column name in the table if I can get this working.

    Why do you insist upon using the dgv when the whole purpose of this thread is to get it working with a textbox? I had it working with a dgv days ago. The problem is the textbox.


    Monday, June 29, 2020 8:45 PM
  • I would not use data relations, instead do something like this.

    Imports System.Data.SqlClient
    
    Public Class Operations
        Private Shared ConnectionString As String =
                           "Data Source=.\SQLEXPRESS;" &
                           "Initial Catalog=NorthWindAzureForInserts;" &
                           "Integrated Security=True"
    
        Public Shared CustomerBindingSource As New BindingSource
        Public Shared ContactTypeBindingSource As New BindingSource
        Public Shared Sub Load()
            Dim ds As New DataSet
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "SELECT CustomerIdentifier, CompanyName, ContactTypeIdentifier FROM Customers"
    
                    Dim dtCustomers As New DataTable
                    cn.Open()
                    dtCustomers.Load(cmd.ExecuteReader())
    
                    CustomerBindingSource.DataSource = dtCustomers
    
                    cmd.CommandText = "SELECT ContactTypeIdentifier, ContactTitle FROM dbo.ContactType;"
    
                    Dim dtContacts As New DataTable
                    dtContacts.Load(cmd.ExecuteReader())
                    ContactTypeBindingSource.DataSource = dtContacts
    
                End Using
            End Using
    
        End Sub
    End Class

    Form code

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Operations.Load()
    
            CustomerComboBox.DataSource = Operations.CustomerBindingSource
            CustomerComboBox.DisplayMember = "CompanyName"
    
            UpdateLabel()
    
            AddHandler Operations.CustomerBindingSource.PositionChanged, AddressOf PositionChanged
    
        End Sub
    
        Private Sub PositionChanged(sender As Object, e As EventArgs)
            UpdateLabel()
        End Sub
        Private Sub UpdateLabel()
            Dim contactTypeId = CType(Operations.CustomerBindingSource.Current,
                                      DataRowView).Row.Field(Of Integer)("ContactTypeIdentifier")
    
            lblContactType.Text = CType(Operations.ContactTypeBindingSource.Item(
                Operations.ContactTypeBindingSource.Find("ContactTypeIdentifier", contactTypeId)),
                                        DataRowView).Row.Field(Of String)("ContactTitle")
        End Sub
    End Class
    
     

    Otherwise you would need to carry unnecessary data.

    SELECT C.CustomerIdentifier, 
           C.CompanyName, 
           C.ContactId, 
           C.ContactTypeIdentifier, 
           CT.ContactTitle
    FROM Customers AS C
         INNER JOIN ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier
    
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 29, 2020 11:31 PM
    Moderator
  • Hi,
    I don't insist on using a dgv. I suggested using a DataGrid (not DataGridView!) To analyze the data that led to the error. The last error says that the column ST_DESC is missing. You can easily see that with a DataGrid (not DataGridView!).

    Please take a closer look at my answer.

      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {Label1, lbl1a, tb2, lbl2a, btnNext})
        LoadData()
        SetRelation()
        'tb2.DataBindings.Add("Text", bsSE, "SE_DESC")
    
        Dim frm2 As New Form
        Dim dg As New DataGrid With {.Dock = DockStyle.Fill}
        frm2.Controls.Add(dg)
        dg.DataSource = dsPSB
        frm2.Show()
      End Sub


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Tuesday, June 30, 2020 7:01 AM
  • ST_DESC is in the datatable with values and it is in the code so how can it be missing?

    My toolbox does not have a datagrid. Please don't respond to my threads as you don't know

    what you are talking about!


    Tuesday, June 30, 2020 2:45 PM
  • Hi,

    ST_DESC is in the datatable with values and it is in the code so how can it be missing?

    Please, show result in DataGrid (my postet code).

    If you don't include DataGrid in Toolbox (Chooce Items) Toolbox don't show DataGrid. DataGrid is declared in System.Windows.Forms and you can use it in code without including in Toolbox.


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks




    Tuesday, June 30, 2020 3:25 PM
  • Peter knows perfectly well about the datagrid.

    It is a fine tool to view the relations in a dataset. 

    You can add it to your toolbox just with selecting it. 

    It is a windowsform datagrid, does not look nice as end user grid but is perfect for what Peter is telling. 

    Maybe you would accept that some persons know more then you about this. 


    Success
    Cor


    Tuesday, June 30, 2020 5:00 PM
  • Hi Cor,
    unfortunately DataGrid class is not available in .NET Core 3.1 and later versions. How this feature (display DataSet) will be realized in new DataGridView I don't know.

    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Tuesday, June 30, 2020 6:22 PM
  • LOL.You keep thinking that.
    Tuesday, June 30, 2020 10:46 PM
  • Hi Developer Dude,
    I don't thinking that. I read it, see here.

    The deprecated controls were previously removed from designer toolboxes but were still available to be used. The following types are no longer available: DataGrid, ToolBar, ContextMenu, Menu, MainMenu and Menultem.


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    Wednesday, July 1, 2020 3:31 AM