none
dataGridView with Foreign KEY RRS feed

  • Question

  • Hi all

    I a simple Dataset with two tables  Region and Nations.

    For each Region there is a Foreign KEY related to Primary Key of Nations Table

    Then I have a Main Form with a  DGV to show all Regions with Region Name, Nation ID Nation Description

    Dim QueryS As String = <sql> SELECT [RegioneID] ,[RegioneDes] ,[RegioneNazioneID] ,[NazioneDes] FROM Tabel.TabRegioni INNER JOIN Tabel.TabNazioni ON Tabel.TabRegioni.RegioneNazioneID = Tabel.TabNazioni.NazioneID

    ORDER BY [RegioneID] ASC

    </sql>.Value Try Using CONN As New SqlConnection With {.ConnectionString = VG.FullCS} Using CMD = New SqlCommand() With {.Connection = CONN, .CommandType = CommandType.Text} Using DAL_DA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = CONN}} CONN.Open() DAL_DA.SelectCommand.CommandText = QueryS DAL_DA.Fill(DAL_DS, "TabRegioni") Return DAL_DS End Using End Using End Using


    The DGV bound to a BindingSource bound to TabRegioni

    This works well, but when I try to modify a row with another form, or insert a new row in TabRegioni,

    the operation goes well (both in TabRegioni and Database) but the Nation Description in the DGV is not update.

    What is my mistake ?

    Saturday, December 1, 2018 9:31 AM

All replies

  • Maybe it because you use a different dataset in another form, so if you want your update also seen in first form then make dataset as global.
    • Edited by Xan To Saturday, December 1, 2018 10:23 AM
    Saturday, December 1, 2018 10:19 AM
  • No, I pass ByRef the Dataset to the second Form when I start the project after I fill the DataSet from DB

    Saturday, December 1, 2018 11:00 AM
  • How do you pass dataset byref? Using property?
    Saturday, December 1, 2018 12:05 PM
  • In the Main Form with DGV I pass the DataSet in this way

      
    Frm2 = New FormAnagraficaRegioniSK(UI_DS)

    where UI_DS is the Dataset and 

    FormAnagraficaRegioniSK

    in the second Form where I change value of a single row of TabRegioni

    Saturday, December 1, 2018 2:55 PM
  • Do you declare inside FormAnagraficaRegioniSK class like this?

    Public Sub New(ByRef DataSet As DataSet)
      LocalDataSet=DataSet
    End Sub

    Best Regard

    Xan To

    Sunday, December 2, 2018 6:20 AM
  • Hi Xan

    I use to following code

    In the Main Form Load Event I fill the Typed Dataset form DB. The DataSet has the TabRegione Table that is a DataView of two Tables since the Select has an INEER JOIN for the foreign KEY

    Private UI_DS As DSTabRegioni
    Private Sub Form_Load(sender As Object, e As EventArgs) Handles Me.Load
    .......
    UI_DS = BLL.TabRegioniGetDataSet()
    .......
    
    FrmAnaRegioniSK = New FormAnagraficaRegioniSK(UI_DS) 
    
    ......
    end sub

    The Second form costructors is 

    Public Sub New(ByRef pDS As DSTabRegioni) InitializeComponent() UIS_DS = pDS

    .......

    end sub

    Then I click on "Modifica" Button on Main Form to modify a row

      Private Sub CmdModifica_Click(sender As Object, e As EventArgs) Handles CmdModifica.Click
    ....
    ....
     FrmAnaRegioniSK.DoOperation(PimaryKeyOfCurrentRow, "U")
     FrmAnaRegioniSK.ShowDialog()
    ...
    End Sub

    In this way I modify a row in second Form, update Dataset and DataBase succefully.

    When I go back to Main Form , the ForignKey ID is Updated but the ForeignKey Value is not Updated.

    I try to  Fill Again the Dataset fro DataBase after a row modification and the DatagridViewaon Main Form is update well,  but it seems a strange solution to me.


    • Edited by Claudio111 Sunday, December 2, 2018 8:03 AM
    Sunday, December 2, 2018 8:01 AM
  • I believe the problem is within Adapter.UpdateCommand Or Relation Rule

    where you put button save of any update, in form1 or form2??


    • Edited by Xan To Tuesday, December 4, 2018 5:24 AM
    Monday, December 3, 2018 6:36 AM
  • I update Data TabRegioni row and DB in Form2 then go back to Main Form

    Monday, December 3, 2018 8:06 AM
  • take a look

    Monday, December 3, 2018 10:37 AM
  • ok ,  and what is the solution ?
    Monday, December 3, 2018 11:26 AM
  • Maybe some kind of binding for the DGV Column NazioneDes cell.

    I found this solution for now

    In the cell_formatting event of DGV I set

    DGV.row(BS.Position).cell(NazioneDesColumn).value = NazioneDesGotfrom table

    Now the question is :

    Is it possible to bind a DGV cell to a Object Property ?

    Monday, December 3, 2018 5:10 PM
  • ok ,  and what is the solution ?

    Hi,

    You need to bind two DataGridviews to one data source.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim sda As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private Bind As New BindingSource
        Dim dt As DataTable = New DataTable
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student INNER JOIN Class on Student.Uid=Class.ClassId "
            sda = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            'Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(sda)
            sda.Fill(dt)
            Bind.DataSource = dt
            DataGridView1.DataSource = Bind
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim form2 As Form2 = New Form2
            form2.DataGridView1.DataSource = Bind
            form2.Show()
        End Sub
    
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 4, 2018 3:46 AM
  • I don't know what method you use and even you didn't say that what I have shown to you does fit for your situation. But this is pieces of my code and for more information about my code you can read in MSDN.

    Private Sub InitializeDataBinding()
      DGVSatuanBarang.AutoGenerateColumns = False
      DGVSatuanBarang.Columns(0).DataPropertyName = "IDBARANG"
      DGVSatuanBarang.Columns(1).DataPropertyName = "SATUAN"
      DGVSatuanBarang.Columns(2).DataPropertyName = "MINIMALPERSEDIAAN"
      DGVSatuanBarang.Columns(2).DefaultCellStyle.Format = "#,##0.00"
      DGVSatuanBarang.Columns(3).DataPropertyName = "MINIMALPEMESANAN"
      DGVSatuanBarang.Columns(3).DefaultCellStyle.Format = "#,##0.00"
      DGVSatuanBarang.Columns(4).DataPropertyName = "HARGAJUAL"
      DGVSatuanBarang.Columns(4).DefaultCellStyle.Format = "#,##0.00"
      DGVSatuanBarang.Columns(5).DataPropertyName = "POTONGANHARGA"
      DGVSatuanBarang.Columns(6).DataPropertyName = "JUMLAHBARANG"
      DGVSatuanBarang.Columns(6).DefaultCellStyle.Format = "#,##0.00"
      DGVSatuanBarang.Columns(7).DataPropertyName = "URUTAN"
      DGVSatuanBarang.DataSource = DataSet.DETAILBARANG
    
      DGVMultiHargaJual.AutoGenerateColumns = False
      DGVMultiHargaJual.Columns(0).DataPropertyName = "IDBARANG"
      DGVMultiHargaJual.Columns(1).DataPropertyName = "SATUAN"
      DGVMultiHargaJual.Columns(2).DataPropertyName = "HARGAJUAL"
      DGVMultiHargaJual.Columns(2).DefaultCellStyle.Format = "#,##0.00"
      DGVMultiHargaJual.Columns(3).DataPropertyName = "KETERANGAN"  
      DGVMultiHargaJual.DataSource = DataSet.MULTIHARGAJUAL
    End Sub
    
    Private Sub FillDataTable()
      Adapter.SelectCommand.CommandText = "SELECT IDBARANG, SATUAN, MINIMALPERSEDIAAN, MINIMALPEMESANAN, HARGAJUAL, POTONGANHARGA, JUMLAHBARANG, URUTAN " &
                                          "FROM DETAILBARANG " &
                                          "WHERE IDBARANG=202 ORDER BY URUTAN ASC"
      Adapter.Fill(DataSet.DETAILBARANG)
      
      Adapter.SelectCommand.CommandText = "SELECT IDBARANG, SATUAN, HARGAJUAL, KETERANGAN " &
                                          "FROM MULTIHARGAJUAL " &
                                          "WHERE IDBARANG=202 ORDER BY SATUAN ASC"
      Adapter.Fill(DataSet.MULTIHARGAJUAL)
    End Sub
          
    Private Sub BtnSimpan_Click(sender As Object, e As EventArgs) Handles BtnSimpan.Click   
      DGVSatuanBarang.BindingContext(DGVSatuanBarang.DataSource).EndCurrentEdit()
      DeleteCommandSatuanBarang()
      UpdateCommandSatuanBarang()
      InsertCommandSatuanBarang()
      Adapter.Update(DataSet.DETAILBARANG)
      
      DGVMultiHargaJual.BindingContext(DGVMultiHargaJual.DataSource).EndCurrentEdit()
      DeleteCommandMultiHargaJual()
      UpdateCommandMultiHargaJual()
      InsertCommandMultiHargaJual()
      Adapter.Update(DataSet.MULTIHARGAJUAL)
    End Sub

    Can you screenshot your second form?

    Best Regard

    Xan To


    • Edited by Xan To Wednesday, December 5, 2018 4:21 AM
    Tuesday, December 4, 2018 5:17 AM
  • Hi Xan

    your example does'n fix my problem

    Following the two form I use

    The Main Form Use DGV bound to BindingSource bound to TabRegioni.

    Pay Attention to the SELECT(above in my question) to fill the TabRegioni. It uses a INNER JOIN to get value of FK (nazioniID) 

    If I modify a row with second Form, the column Nazione is not Update

    So. what kind of binding should I use for Column Nazioni that is not presente in DB Column (set DATASET above) ?


    Tuesday, December 4, 2018 2:33 PM
  • Hello Claudio,

    I just noticed that there is a problem with your TabRegioni, there is no NazioneDes DataColumn in TabRegioni, even in your query there is NazioneDes field. So Adapter will create new DataColumn for NazioneDes field, but NazioneDes DataColumn did not relate to NazioneDes on TabNazioni only NazioneID

    Best Regard

    Xan To

    • Proposed as answer by Xan To Wednesday, December 5, 2018 4:45 AM
    Wednesday, December 5, 2018 4:45 AM
  • Hi Xan

    The DGV has a NazioniDes Column set with Designer and it has the DatapropertyName NazioneDes.

    With this, when a Fill the DataAdapter the DGV is filled with alla data including NazioneDes.

    The problem I have is when I insert a New row with the second Form.

    Now the solution for me are two

    Or after an Insert fill the bindingsource column with  NazioneDes 

    DGV.DataSource.Current.item(DgvRegioneNazioneDes.Index) = NazioneDesTextBox

    or in the DATASET I add NazioneDes column to TabRegioni.

    So which is the best solution you suggest ? Are there other solutions ?

    Wednesday, December 5, 2018 7:57 AM
  • Hi Xan

    The DGV has a NazioniDes Column set with Designer and it has the DatapropertyName NazioneDes.

    With this, when a Fill the DataAdapter the DGV is filled with alla data including NazioneDes.

    The problem I have is when I insert a New row with the second Form.

    Now the solution for me are two

    Or after an Insert fill the bindingsource column with  NazioneDes 

    DGV.DataSource.Current.item(DgvRegioneNazioneDes.Index) = NazioneDesTextBox

    or in the DATASET I add NazioneDes column to TabRegioni.

    So which is the best solution you suggest ? Are there other solutions ?

    First, is your DataGridView create automatically by Drag and Drop from Data Source?

    Or you design manually? If you create manually, of cause like I said when you do Adapter.Fill then Adapter will create AUTOMATICALLY for you new DataColumn for NazioneDes field, that's why you can set like this DGV.Columns("CNazioneDes").DataPropertyName = "NazioneDes" without any error.

    forget what I said about "NazioneDes DataColumn did not relate to NazioneDes on TabNazioni only NazioneID" it has nothing to do with the problem you are asking.

    here do this:

    Private Sub New(ByRef pDS As DSTabRegioni)
    ...
      UIS_DS = pDS
      CBoxNazione.DataSource = DataSet.TabNazioni
      CBoxNazione.ValueMember = "NazioneID"
      CBoxNazione.DisplayMember = "NazioneDes"
      
      CBoxNazione.DataBindings.Add("SelectedValue", UIS_DS, "NazioneID", True, DataSourceUpdateMode.OnValidation, -1)
      CBoxNazione.DataBindings.Add("SelectedItem", UIS_DS, "NazioneDes", True, DataSourceUpdateMode.OnValidation)
    ...
    End Sub
    
    Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
      CBoxNazione.DataBindings.Item("SelectedValue").WriteValue()
      CBoxNazione.DataBindings.Item("SelectedItem").WriteValue()
      
      Adapter.Update(UIS_DS)
      UIS_DS.AcceptChanges
    End Sub

    Xan To
    • Proposed as answer by Xan To Wednesday, December 5, 2018 9:01 AM
    • Edited by Xan To Wednesday, December 5, 2018 9:07 AM
    Wednesday, December 5, 2018 8:58 AM
  • Hi Xan

    Ok for Binding Xan . But the problem now is that my Combobox bound already to the BusinessObject  "BORegione" that has a property AnaRegioneNazioneDes

    This BO property is well updated in the second form.

    So I try to bind the BO property AnaRegioneNazioneDes to TabRegioni that has inside a Column Name "NazioneDes" 

    This is the bind I use but it does'n work

    BORegione.DataBindings.Add("AnaRegioneNazioneDes", UI_DS.TabRegioni, "NazioneDes", False)

    Wednesday, December 5, 2018 4:38 PM
  • Sorry I made a mistake, I guest UI_DS is a table name but it's not. I fix my code

    Private Sub New(ByRef pDS As DSTabRegioni)
      ...
      UIS_DS = pDS
      CBoxNazione.DataSource = UIS_DS.TabNazioni
      CBoxNazione.ValueMember = "NazioneID"
      CBoxNazione.DisplayMember = "NazioneDes"
      ...
      CBoxNazione.DataBindings.Add("SelectedValue", UIS_DS.TabRegioni, "NazioneID", True, DataSourceUpdateMode.OnValidation, -1)
      CBoxNazione.DataBindings.Add("SelectedItem", UIS_DS.TabRegioni, "NazioneDes", True, DataSourceUpdateMode.OnValidation)
      ...
    End Sub
    
    Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
      CBoxNazione.DataBindings.Item("SelectedValue").WriteValue()
      CBoxNazione.DataBindings.Item("SelectedItem").WriteValue()
      ...
      Adapter.Update(UIS_DS.TabRegioni)
      UIS_DS.TabRegioni.AcceptChanges
    End Sub

    Because I don't know what code is in your BORegione, so I give you a clue, 

    SelectedValue and SelectedItem is a combobox property, SelectedValue is a property which is return ComboBox.ValueMember.

    Can you do this BORegione.SelectedValue?



    • Edited by Xan To Thursday, December 6, 2018 6:57 AM
    Thursday, December 6, 2018 6:53 AM
  • Hi Xan

    don't worry I understood.

    To answer to your question, up to now my Business Class  BORegione is very simple (just properies for each TabRegioni Column)

    Imports System.Windows.Forms
    Imports JFATT_DATASET
    Imports JFATT_Libreria_Moduli
    
    Public Class ClasseAnaRegione
    
        Private myAnaRegioneID As Integer
        Public Property AnaRegioneID() As Integer
            Get
                Return myAnaRegioneID
            End Get
            Set(ByVal value As Integer)
                myAnaRegioneID = value
            End Set
        End Property
    
        Private myAnaRegioneDes As String
        Public Property AnaRegioneDes() As String
            Get
                Return myAnaRegioneDes
            End Get
            Set(ByVal value As String)
                myAnaRegioneDes = value
            End Set
        End Property
        Private myAnaRegioneNazioneID As String
        Public Property AnaRegioneNazioneiD() As String
            Get
                Return myAnaRegioneNazioneID
            End Get
            Set(ByVal value As String)
                myAnaRegioneNazioneID = value
    
            End Set
        End Property
        Private myAnaRegioneNazioneDes As String
        Public Property AnaRegioneNazioneDes() As String
            Get
                Return myAnaRegioneNazioneDes
            End Get
            Set(ByVal value As String)
                myAnaRegioneNazioneDes = value
            End Set
        End Property


    The BO Regioni is not a control so i cannot bind it to UI_DS.TabRegioni  DataSource but I can only use BORegioni as DataSource for a TextBox after having filled manually the BORegioni with data form datatable UI_DS.TAbRegioni.

    Now a big question :

    My application is an N-Tier applcation and in the DAL I use DataAdapter (No Table Adatper)

    Which is the best solution of the following two considering that I have a DGV bound to a BS(bindingsource) bound to UI_DS.TAbRegioni

    1 solution) To bind TextBoxs of second form to UI_DS.TabRegioni columns  and than manually fill BORegion with ThexBoxs value and then with BORegion properties update BS(bindingsource) columns column manually

    2 solution) Bind TextBox of second form to BORegioni (filled manually from dataset) so they are syncronized and then update manually BS columns with BORegion Properties 

    which is the most used way ?

    Thank you for your patience

    Thursday, December 6, 2018 1:11 PM
  • I suggest you use no 1
    Sunday, December 9, 2018 8:52 AM