none
Working With A DataSet Across Multiple Windows Forms RRS feed

  • Question

  • Hi all,

    I have created an application using Visual Basic 2008 Express Edition along the lines of Beth Massi's blog, "Using Data Across Multiple Windows Forms", here's the link : http://blogs.msdn.com/b/bethmassi/archive/2007/10/01/using-data-across-multiple-windows-forms.aspx?PageIndex=1#comments

    This article presents how a DataSet can be shared across multiple forms. I have developed my application slightly different from Beth's, although closely following the basics. 

    Here's what I have. I have 2 forms, Form1 (Add New Pastor) and Form2 (Details). I created the database with 2 tables; Pastor table and VillCODES table using Access 2007 and dragged the Pastor table from the DataSources window as Details View. Then created a new constructor that accepts the DataSet on Form1 and the Primary Key (PastorID) that I want to edit. Then I set the VillCODEBindingSource's DataSource and Filter property on Form2 so that it displays the correct row. This should keep the Pastor and VillCODES table in sync. 

    In addition to this, I dragged the VillCODES table as DataGridView and created 2 parameterized query's using the VillCODESTableAdapter, one for District and one for Block(sub-district). What I want to do is Save the changes I make from Form2 into the VillCODES table in the Access Database for the PastorID in Form1. 

    But when I run the application, and make changes in the second form, Form2, none of the changes are being saved back into the database. Can anyone please help me out ?

    The code I am using:

    Public Class Form1
    
        Private Sub PastorBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
    
        End Sub
    
        Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.PastorBindingSource.EndEdit()
    
            If Me.PastorBindingSource.Position > -1 Then
    
                'Get the current PastorID row
                Dim row As TellASIADataSet.PastorRow
                row = CType(CType(Me.PastorBindingSource.Current, DataRowView).Row, TellASIADataSet.PastorRow)
    
                'Open the Ministry Detail form passing the dataset and the PastorID
    
                Dim frm As New Form2(Me.TellASIADataSet, row.PastorID)
                frm.Show()
            End If
        End Sub
    
        Private Sub PastorBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PastorBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub cmdAddMinistryDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMinistryDetails.Click
            Me.PastorBindingSource.EndEdit()
    
            If Me.PastorBindingSource.Position > -1 Then
    
                'Get the current Pastor row
                Dim row As TellASIADataSet.PastorRow
                row = CType(CType(Me.PastorBindingSource.Current, DataRowView).Row, TellASIADataSet.PastorRow)
    
                'Open the detail form passing the DataSet and PastorID
                Dim frm As New Form2(Me.TellASIADataSet, row.PastorID)
                frm.Show()
            End If
        End Sub
    End Class

    And for Form2 I use this code:

    Public Class Form2
    
        Sub New(ByVal ds As TellASIADataSet, ByVal id As Integer)
    
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            ' Set the DataSource of the BindingZSource and then set the Filter
            ' so that the correct row will be displayed on the details form.
    
            Me.VillCODESBindingSource.DataSource = ds
            Me.VillCODESBindingSource.Filter = "PastorID = " & id.ToString
        End Sub
    
        Private Sub VillCODESBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.VillCODESBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.VillCODES' table. You can move, or remove it, as needed.
            Me.VillCODESTableAdapter.Fill(Me.TellASIADataSet.VillCODES)
        End Sub
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Me.VillCODESBindingSource.CancelEdit()
        End Sub
      
        Private Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Me.VillCODESBindingSource.CancelEdit()
            Me.Close()
        End Sub
    
        Private Sub cmdOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdOK.Click
            Me.VillCODESBindingSource.EndEdit()
        End Sub
    

    Regards,

    ~~Maneesh

    Sunday, April 22, 2012 11:19 AM

All replies

  • Ah, my old friend Maneesh!

    I didn't look through your code extensively, but after a few minutes of scanning it, one thing does jump out at me. You should be passing the DataSet ByRef, not ByVal. You may have other issues too, but fix that one first and then see what happens.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, April 22, 2012 4:10 PM
  • Ah my good friend Bonnie ! Nice to see you after a long time.

    Nothing really changes when I pass the DataSet ByRef instead of ByVal. Any other suggestions ?

    ~~Maneesh

    Sunday, April 22, 2012 4:28 PM
  • Try this: setting Form2's DataSet property in it's constructor"

    Sub New(ByRef ds As TellASIADataSet, ByVal id As Integer)
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
            ' Add any initialization after the InitializeComponent() call.
            ' Set the DataSource of the BindingZSource and then set the Filter
            ' so that the correct row will be displayed on the details form.
            Me.TellASIADataSet = ds; 
            Me.VillCODESBindingSource.DataSource = ds
            Me.VillCODESBindingSource.Filter = "PastorID = " & id.ToString
        End Sub


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Sunday, April 22, 2012 11:35 PM
    Sunday, April 22, 2012 5:03 PM
  • Hi Bonnie,

    Did that as well, but without any success.

    By the way, you would want to definitely know what I'm upto... So in the second form as it opens up there is a related VillCODES table dragged from the DataSources Window which also has a PastorID column in it. So what I want to do is when the Next button is pressed on Form1 to go to Form2, the PastorID which is open for editing in Form1 should be passed to the PastorID column in the DGV on Form2. This way both the Parent and Child records will remain in sync.

    Hope I've clearly explained that.

    ~~Maneesh


    Monday, April 23, 2012 2:27 AM
  • The only odd thing I see Maneesh, is that on Form2, I think your BindingSource should be:

    Me.VillCODESBindingSource.DataSource = ds.VillCODES

    Also, I don't see that you've bound a DataGridView to your BindingSource, but I'm sure you just left that out of the code that you've posted.

    Other than the things I've mentioned, I don't see any glaring mistakes anywhere. 


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, April 23, 2012 5:01 AM
  • Hi Bonnie,

    I have made all changes you suggested, yet I do not get automatic filtering on the PastorID in Form2. The expected behavior of the app should be when run, Form1 loads with the first Pastor details filled in to the textboxes from the database. And this works fine in Form1. Then when the Next button is clicked to go on to Form2, it should fill the DGV with all the entries from the VillCODES table by default with the PastorID selected in Form1 so I can edit any changes for the Pastor and then press save to push the changes made back to the database. 

    I also have 2 parameterized queries setup on Form2 which I use to filter the Districts and blocks.

    Regarding your doubt whether the DGV is bound to its BindingSource, well it is bound to its BindingSource because you don't have to do anything special here as I dragged the VillCODES table as a DGV onto Form2. This automatically sets all the Bindings for me. 

    I've tried all approaches for making this app work, but somewhere, down the line, it still hasn't seen the light of the day ! No matter which approach I use, it always ends up stuck somewhere or the other !!! I am very disappointed and discouraged.
    ~~Maneesh

    Monday, April 23, 2012 7:49 AM
  • Maneesh,

    We miss something in your code. 

    This would mean that  Me.TableAdapterManager is a member from the class Form2, but I see it only as method, not created as member on Form2

    (If you pass it like the dataset then you can use it without that me)

    Sub New(ByVal TableAdapterManager as TheTableAdapterManagerClass, ByVal ds As TellASIADataSet, ByVal id As Integer)
    I'm not sure of that Type name so put the cursor on that in form1 and see what the type (class) is.

    and then not use the me in form2


    Success
    Cor


    Monday, April 23, 2012 7:50 AM
  • Hi Cor,

    Can you please explain all that in a little bit more detail ?? I can't comprehend what you mean to say. Putting the cursor on the TableAdapterManager gives the following in picture:

    Hope this gives an idea. 

    ~~Maneesh

    Monday, April 23, 2012 8:09 AM
  • Therefore you have at least to use this to pass the reference of the tableadapter in form2 (be aware from a reference type the byval passes a reference)

    Sub New(ByVal TableAdapterManager as TestASIADataSetTableAdapter.TableAdpterManager, ByVal ds As TellASIADataSet, ByVal id As Integer

    And then on form1

    Dim frm As New Form2(Me.TableAdapterManager.Me.TellASIADataSet, row.PastorID)

    If you next show the code it is easier, currently you have a chance that I made typos.


    Success
    Cor

    Monday, April 23, 2012 8:24 AM
  • Ok, I made some changes on the forms, yet still no success. When Form2 loads up, the PastorID is not seen inside the DGV in the PastorID column and the DGV does not fill with the VillCODES table either.

    Here's the full code I am using to make it easier for you to see:

    Public Class Form1
    
        Private Sub PastorBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
        End Sub
    
        Private Sub PastorBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PastorBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub cmdAddMinistryDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMinistryDetails.Click
            Me.PastorBindingSource.EndEdit()
    
            If Me.PastorBindingSource.Position > -1 Then
    
                'Get the current Pastor row
                Dim row As TellASIADataSet.PastorRow
                row = CType(CType(Me.PastorBindingSource.Current, DataRowView).Row, TellASIADataSet.PastorRow)
    
                'Open the detail form passing the DataSet and PastorID
                Dim frm As New Form2(Me.TableAdapterManager, Me.TellASIADataSet, row.PastorID)
                frm.Show()
            End If
        End Sub
    End Class
    Public Class Form2
    
        Sub New(ByVal TableAdapterManager As TellASIADataSetTableAdapters.TableAdapterManager, ByVal ds As TellASIADataSet, ByVal id As Integer)
    
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            ' Set the DataSource of the BindingSource and then set the Filter
            ' so that the correct row will be displayed on the details form.
            Me.TellASIADataSet = ds
            Me.VillCODESBindingSource.DataSource = ds.VillCODES
            Me.VillCODESBindingSource.Filter = "PastorID = " & id.ToString
        End Sub
    
        Private Sub VillCODESBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.VillCODESBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.VillCODES' table. You can move, or remove it, as needed.
            Me.VillCODESTableAdapter.Fill(Me.TellASIADataSet.VillCODES)
        End Sub
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Me.VillCODESBindingSource.CancelEdit()
        End Sub
      
        Private Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Me.VillCODESBindingSource.CancelEdit()
            Me.Close()
        End Sub
    
        Private Sub cmdOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdOK.Click
            Me.VillCODESBindingSource.EndEdit()
        End Sub
    
        Private Sub FillByDistrictToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByDistrictToolStripButton.Click
            Try
                Me.VillCODESTableAdapter.FillByDistrict(Me.TellASIADataSet.VillCODES, DistrictToolStripTextBox.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
    
        End Sub
    
        Private Sub FillByBlockToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByBlockToolStripButton.Click
            Try
                Me.VillCODESTableAdapter.FillByBlock(Me.TellASIADataSet.VillCODES, BlockToolStripTextBox.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
    
        End Sub
    End Class

    The TellASIADataSet.xsd code:

    Partial Class TellASIADataSet
        Partial Class PastorDataTable
    
            Private Sub PastorDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for the non-nullable fields
                Dim pastor As PastorRow = CType(e.Row, PastorRow)
    
                pastor.LName = "Enter a New Name"
            End Sub
        End Class
        Partial Class VillCODESDataTable
    
            Private Sub VillCODESDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for non-nullable fields
                Dim village As VillCODESRow = CType(e.Row, VillCODESRow)
                village.Village = "New Village"
            End Sub
        End Class
    End Class

    Now can you please guide me what the heck I am doing wrong here ????

    Regards,

    ~~Maneesh

    Monday, April 23, 2012 8:55 AM
  • Maneesh,

    What is the reason you try to use an extra form at all, I see you nowhere setting that bindingsource to whatever control.


    Success
    Cor

    Monday, April 23, 2012 9:43 AM
  • Cor,

    Ok, Cor, show me how to set the BindingSource of Form2 to the DGV. I thought I had already done that when I dragged the VillCODES table from the DataSources Window onto Form2 setting up the BindingSource for me.

    ~~Maneesh

    Monday, April 23, 2012 10:25 AM
  • I assume, be aware we see not your designer generated code (and at least I don't want to see it)

    YourDataGridView.DataSource =  Me.VillCODESBindingSource

    I assume in the form load method 


    Success
    Cor

    Monday, April 23, 2012 12:05 PM
  • Ok, now I've even bound the DGV to the VillCODESBindingSource in the Form load method on Form2. Still the DGV does not fill with the VillCODES table from the database.

    Just thought I'd mention this to you here, that I dragged the RELATED VillCODES table from underneath the Pastor table in the DataSources Window and not the one in the main node under the DataSet.

    Now what ?

    ~~Maneesh

    Monday, April 23, 2012 12:22 PM
  • Maneesh,

    Can you say what is working and what is not working, because that is something only you know. It is not working is very relative you know.

    To be honest, I never use multiple windows forms, it always fails and not only with me, i use tabpages, panels, instead of that.

    (or are you using MDI forms?) The only multiple form I use is a dialogform which I show with ShowDialog.

    If you think why I then replying, your question is in the AdoNet forum, the used form should not be important for that.


    Success
    Cor


    Monday, April 23, 2012 3:35 PM
  • This is all news to me, Maneesh. You originally said that you couldn't save the data to the database, not that you couldn't display the data on Form2.

    Anyway, in the last bit of code you posted, you're *still* not passing the DataSet ByRef and you said you had changed that as I had suggested. That might have something to do with the problem. I see that Cor suggested passing the TableAdapterManager too ... I don't think you should have to do that, but if are going to take Cor's suggestion, then you'll have to at least pass it correctly ... the TableAdapterManager should also be passed ByRef. Why does VB default to ByVal for everything? I'm assuming that ByVal and ByRef means the same in VB as it means in C# (although C# doesn't use those keywords).

    UPDATE:Also, please show us the code that you used to set the DataSource for the DGV. I suspect your problem might have something to do with that, but I'm just guessing really.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Monday, April 23, 2012 3:39 PM
    Monday, April 23, 2012 3:37 PM
  • Cor,

    I'll answer your questions tomorrow morning as its getting late in the night here. I'm real tired and drained out.

    Thanks anyway for your replies.

    Get back to you tomorrow.

    ~~Maneesh

    Monday, April 23, 2012 4:11 PM
  • Hi Bonnie,

    I will answer your queries tomorrow as its late her already. I've had a very tiring day today and feel a little drained out and discouraged.

    Thanks anyway. See you tomorrow.

    ~~Maneesh

    Monday, April 23, 2012 4:13 PM
  • Hi Cor,

    Ok, I will let you know what is working and what is not. That way you will be able to figure out what needs to be done, if it ever gets done. 

    Here is a brief introduction: 

    There are 2 tables in the Access database, Pastor and VillCODES. Obviously the Pastor table should only have information pertaining to a Pastor like name, age, sex, address etc. The VillCODES table is a little bit complicated and has fields such Districts, Blocks, Villages, VillCODES along with a few others. These two are in a one to many relationship with each other.  

    What I want to do is to have 2 Windows Forms. On one form, I would allow the user to enter details of a pastor. One the next form, I would like to have details about his ministry. So on this form something like which districts and blocks (sub-districts) and villages he works in besides other stuff. 

    Now, coming to the part which is working and which is not.

    The form for adding new Pastors is very easy, just a drag and drop or could be done with programming. That is not the issue. This works just fine. No problem here.

    The problem creeps in when I want to add details about his ministry in the second form. So in the second form, I use a DGV to populate from the database with the VillCODES table and then have 2 ToolStrips for 2 queries on the top of the form, one for the District and one for the Block. When the use clicks on the Next button on Form1 to go to Form2, he then writes the name of the district in the District ToolStrip and presses enter to filter the DGV for only the Districts where the details need to be filled in. Next he can query the Block ToolStrip with a name of a Block, and the DGV would display only block names along with the villages in a separate column of the DGV. Then the user could easily place the cursor in the row of the village and just add in other details in other fields. After the user has filled in all the details, he presses the Save button on Form2 and all the details for the Pastor added in Form1 and details added in Form2 (basically Parent-Child relationship) would be saved back to the database. It is this functionality that is not working. When the Next button is clicked on Form1, Form2 is displayed which is working. But the DGV is neither being populated with the VillCODES table nor is the correct PastorID (Parent) being displayed when I click inside the DGV.

    Now coming to the other ways and methods you have mentioned as workarounds. I have been looking at Tabbed Pages and Panels the whole day yet I have found nothing useful that fits my scenario and what I am looking for. 

    Regarding you not preferring to use multiple windows forms for programming is something entirely individualistic and according to choice. Surely, there must be a way of how to do achieve the functionality I am looking for with multiple Windows Forms. I think others have surely gone through this before I have and if I can find out a way through help from you guys, this will immensely benefit the community, because I feel there are a dearth of good resources out there that really teach you on this subject. Its either a paid service or you have to delve into many heavy books.

    I would now like to know what you think about all this and how I can go about doing it ??

    Regards,

    ~~Maneesh 

    Tuesday, April 24, 2012 12:45 PM
  • Hi Bonnie,

    Yes, I did use ByRef as you suggested, but then changed it when Cor suggested otherwise to have a look at its effect. But anyway, both are not working. And for the code for the Binding part, I give you the entire code I am working on currently. By the way, please read my reply above to Cor.

    Code:

    First, the DataSet code:

    Partial Class TellASIADataSet
        Partial Class PastorDataTable
    
            Private Sub PastorDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for the non-nullable fields
                Dim pastor As PastorRow = CType(e.Row, PastorRow)
    
                pastor.LName = "Enter a New Name"
            End Sub
        End Class
        Partial Class VillCODESDataTable
    
            Private Sub VillCODESDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for non-nullable fields
                Dim village As VillCODESRow = CType(e.Row, VillCODESRow)
                village.Village = "New Village"
            End Sub
        End Class
    End Class
    

    Now, Form1:

    Public Class Form1
    
        Private Sub PastorBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
        End Sub
    
        Private Sub PastorBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PastorBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub cmdAddMinistryDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMinistryDetails.Click
            Me.PastorBindingSource.EndEdit()
    
            If Me.PastorBindingSource.Position > -1 Then
    
                'Get the current Pastor row
                Dim row As TellASIADataSet.PastorRow
                row = CType(CType(Me.PastorBindingSource.Current, DataRowView).Row, TellASIADataSet.PastorRow)
    
                'Open the detail form passing the DataSet and PastorID
                Dim frm As New Form2(Me.TableAdapterManager, Me.TellASIADataSet, row.PastorID)
                frm.Show()
            End If
        End Sub
    End Class
    


    And now Form2:

    Public Class Form2
    
        Sub New(ByRef TableAdapterManager As TellASIADataSetTableAdapters.TableAdapterManager, ByRef ds As TellASIADataSet, ByVal id As Integer)
    
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            ' Set the DataSource of the BindingSource and then set the Filter
            ' so that the correct row will be displayed on the details form.
            Me.TellASIADataSet = ds
            Me.VillCODESBindingSource.DataSource = ds.VillCODES
            Me.VillCODESBindingSource.Filter = "PastorID = " & id.ToString
        End Sub
    
        Private Sub VillCODESBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.VillCODESBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            ''TODO: This line of code loads data into the 'TellASIADataSet.VillCODES' table. You can move, or remove it, as needed.
            Me.VillCODESTableAdapter.Fill(Me.TellASIADataSet.VillCODES)
            Me.VillCODESDataGridView.DataSource = Me.VillCODESBindingSource
    
        End Sub
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Me.VillCODESBindingSource.CancelEdit()
        End Sub
      
        Private Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Me.VillCODESBindingSource.CancelEdit()
            Me.Close()
        End Sub
    
        Private Sub cmdOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdOK.Click
            Me.VillCODESBindingSource.EndEdit()
        End Sub
    
        Private Sub FillByDistrictToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Try
                Me.VillCODESTableAdapter.FillByDistrict(Me.TellASIADataSet.VillCODES, DistrictToolStripTextBox.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
    
        End Sub
    
       
    
        Private Sub FillByBlockToolStripButton_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByBlockToolStripButton.Click
            Try
                Me.VillCODESTableAdapter.FillByBlock(Me.TellASIADataSet.VillCODES, BlockToolStripTextBox.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
    
        End Sub
    End Class

    Any leads, suggestions today ???

    Regards,

    ~~Maneesh

    Tuesday, April 24, 2012 12:51 PM
  • A couple of things, Maneesh:

    1) Have you tried debugging? Do you know if you're actually getting any data? Put a breakpoint in Form2 on the TableAdapter.Fill statements (in the _Load and  _Click event handler methods). You have not totally showed us everything, because we do not know what your TableAdapter's various Select Commands look like. That's why I wanted you to check to see if you're actually getting any data.

    2) In one of our previous conversations in other threads over the last few weeks/months, I had mentioned that you needed to have a third table, I think I referred to it as PastorVilli or VilliPastor or something like that. I see that you have not taken that advice. Perhaps you did not understand why I thought you needed it? The VilliCODES table should only contain Village, Block and District. The PastorVilli table should contain the PastorID, the VillageID, and whatever other information you need. The PastorID should be a Foreign Key to the Pastor table and the VillageID should be a Foreign Key to the VilliCODES table. You used ComboBoxes in earlier code for District and Block. I guess you've given up that idea (don't know why ... I thought you had it all working).

    Anyway ... let's deal with #1 first ... are you getting any data?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, April 24, 2012 3:41 PM
  • Hi Bonnie,

    1) Although you did not mention anything on what to look for when putting the break point in Form2 on TableAdapter.Fill statement, I looked through it myself. There are hundreds, maybe thousands of properties to look into. I can just make a guess as to which property I should inspect. However I have attached a snapshot of the TableAdapter.Fill at the break point. Please mention which property we are interested here in looking at. 

    Regarding the various Select Commands of the TableAdapter and what they look like, I have them below:

    FillByDistrict: 

    SELECT        ID, PastorID, District, Block, VillCODE, Village, [Zone], .............. etc.
    FROM            VillCODES
    WHERE        (District = ?)

    FillByBlock:

    SELECT        ID, PastorID, District, Block, VillCODE, Village, [Zone], ............. etc.
    FROM            VillCODES
    WHERE        (Block = ?)

    But I think I won't be needing them any more, since the demands of the application have changed.

    2) During the course of creation of this application, I have learned a lot regardless of whether it has worked or not ! But it has taught me some very good fundamentals of programming. I have become more of a researcher than a programmer !! I've looked at hundreds, possibly thousands of web pages and websites to find possible design solutions and ideas and see what others are doing and how they are doing it. Besides this, I have also looked at some of the best practices in Visual Basic.NET programming. I think, that an application is always *evolving* and undergoes *metamorphosis* during its creation, so to speak. What this means, is that one should always be on the look out to find and research what the End- Users will be comfortable with. Because it is ultimately they who get to use the program more than the one who has programmed it. So during the several past days/weeks, I have spent time talking to several staff/end-users, made notes on suggestions for improvements that could be made to enhance the application. Hence, I have given way to designs which end-users would not be comfortable with and made way for those that would be more user-friendly, and convenient. 

    I am not averse to using a third table for dumping all the data into. The key here is not the third table, rather, the way that the user interface is designed for convenience. Hence, previously, I was working on the 2 ToolStrip Queries; one for Districts and the other for Blocks, which I have just shunned away! The reason, again, users didn't like the idea. They are more comfortable with using the ComboBoxes for these two. Hence, ComboBoxes are here to stay !!!

    Besides this major change, I found out users were more comfortable in double-clicking the Pastor row which they are currently editing/adding in Form1 to open the exact row into a DataGridView control on Form2 for adding details pertaining to the PastorID they are adding in Form1. 

    Hence, my end-users now require me to have Form1 to add a new pastor from (comfortable with using a DataGridView control here). And then, while they are still adding a new pastor, double-clicking the current row, to open Form2 with also a DataGridView control. Now, they would just make use of the District ComboBox to select a District which filters the Block ComboBox with Blocks pertaining to the District chosen. Then using the Block ComboBox to populate the DGV down below with all Villages pertaining to the Block chosen.

    THEN, as soon as the user places the cursor into the DGV, the PastorID of the new pastor being added should be displayed in a row of the DGV on Form2. Users would then be able to add other details pertaining to the Pastor and click a Save button to DUMP all the data into the THIRD table (could be of any name) with the appropriate columns. 

    This design sounds more realistic and better than thought previously, for now. But like I said, an application or the needs of an application may evolve or change during the course of time, I may come back 2-3 years later and shun away with the current one and find out even a better way of doing it !

    But as of now, I think I'll stick with the current design concept. Now, how to implement this idea, any thoughts please ??

    Regards,

    ~~Maneesh


    Thursday, April 26, 2012 12:57 PM
  •  Although you did not mention anything on what to look for when putting the break point in Form2 on TableAdapter.Fill statement

    Sorry Maneesh, I thought that was obvious from what I said, which was: Do you know if you're actually getting any data? Put a breakpoint in Form2 on the TableAdapter.Fill statements (in the _Load and  _Click event handler methods). To me that implies that you should execute the .Fill() statement and then look at the DataSet/DataTable in the debugger to see if you've got any data!!

    The reason for the third table is this: You need a table to hold information for each pastor that contains the villages they're assigned to, along with whatever other information you collect for that particular village/pastor combination. This table has got  to be a separate table than the one where you're getting your codes from (the code table is simply a list of district/block/village combinations). The DGV that you're displaying on Form2 should be getting/adding/saving data to this third table. Originally you had check-boxes to indicate which village the pastor was assigned to ... are you still doing that?

    In fact, for a new pastor, you wouldn't actually have any data yet in the third table for that pastor. The user would first need to use the check-boxes to select villages, and then you'd populate that third table in your DataSet with the rows for each village seleted, each new row would contain the PastorID and the VillageID and then the user would fill out the rest of the data needed for each village/pastor combination in the DGV.

    For an existing pastor, the CheckBoxes would already be checked for the villages a pastor is currently assigned to and the DGV would be populated with the villages that the pastor was already assigned to. Then the user can make changes (for example, checking a new village, which gets added to the DGV, etc. or simply changing info for already entered villages).


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, April 26, 2012 1:51 PM
  • Hi Bonnie,

    Thanks for the prompt feedback.

    Yes, I have checked several times looking for data in the DataSet/DataTable. Seems to me that the DataTable returned for PastorID 3 has a count value of 3, meaning to say that when the user clicks on the button to go to Form2, it has a value count of 3. IOW, there seems to be data for the Pastor table in Form1 when the TableAdapter.Fill is being executed. But for the VillCODES table, there appears to be no data. The count value in the Locals window down below has a value of 0, which I think means it is not filling with any data at all. If I Step Over the code, it has no action and the break point does not move from the TableAdapter.Fill Statement and nothing happens further with the application. It has to be ended by pushing the Stop debugging button by me. This could possibly mean, for some reason, the VillCODES table isn't being populated from the database. Why could this be happening ???

    Yes, originally I had check boxes in a CheckedListBox on Form2, which I removed as this was out of my bounds to program. You also mentioned to me in a previous post that this would be harder to implement than thought. Putting a CheckedListBox for the Villages would require to get all the Checked Villages into a String variable and then pushing that variable into the third table, which simply I could not find how to do. Hence I got away with that design idea. And besides, if you look at it, putting a CheckedListBox on Form2 with the other 2 ComboBoxes was not cool enough. Meaning to say, how and where would you display the PastorID currently being edited ??? I didn't like the design so I am not using it. 

    A better way would be the way I've described it by a DGV and adding/editing data inside the DGV and then saving it to the third table.

    How do you think about this ?? Would a DGV be better or a DGV along with a CheckedListBox for villages be better ? Lets first discuss this design and then once for all get over with the design interface and work on the programming part next.

    ~~Maneesh


    Thursday, April 26, 2012 3:04 PM
  • First, a little debugging lesson:

    1) In order to execute the line that the breakpoint is set on, and only execute that line, you need to Step Over it ... I simply hit F10, it's much easier. Or, you could have put the breakpoint on the line *after* the .Fill() ... that would have accomplished the same thing.

    2) If you look at the Me.TellASIADataSet in the locals or watch window, you'll see a little magnifying glass icon on the right side of the Value column. Clicking on this brings up the DataSet Viewer so you can easily "browse" your DataTables. Do this after your .Fill() method has run.

    I still think you should go with the CheckedListBox and the DGV. If you didn't use the CheckedListBox, and only used the DGV, you'd have to have a row in the grid for every village, a checkbox to indicate whether or not the pastor was assigned to that village and all the rest of the data needed. IOW, if the district/block combination had 20 villages in it, but your pastor was only assigned to 3 of them, you'd have 17 useless rows of villages in your DGV. That's not very user-friendly, I don't think.  I think you should only be displaying the 3 that he's assigned to.

    Putting a CheckedListBox for the Villages would require to get all the Checked Villages into a String variable and then pushing that variable into the third table, which simply I could not find how to do

    Did you ask any questions here on the forums as to how to accomplish this? If so, I missed it; I can't spend hours a day browsing the forums ... ;0) If  not, maybe you should. I don't remember where the thread is where we discussed this in the past.

     And besides, if you look at it, putting a CheckedListBox on Form2 with the other 2 ComboBoxes was not cool enough. Meaning to say, how and where would you display the PastorID currently being edited ???

    Ummm .... maybe a TextBox or a Label? That'a not rocket-science. ;0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, April 26, 2012 3:33 PM
  • Hi Bonnie,

    Well, first of all I begin by saying, I have run into problems with the database tables themselves. Lets leave debugging part for a little while. I want to take you back to your post above where you mention about a third table. You had said there, that the third table, call it PastorVill table, should have PastorID and VillID plus any other fields I want. Then, PastorID should be a foreign key to the Pastor table and VillID should be foreign key to VillCODES table. But this is more easy to say than to do !!!

    I am working with Access 2007, and I receive an error message which says there is a Referential Integrity problem with the third table when I drag the VillID field from the VillCODES table over to the PastorVill (third new table) in order to establish a One to Many relationship. IOW, the VillCODES table contains hundreds and thousands of VillCODES, which act as Parent and when I drag this field over to the PastorVill table (New Third table) which contains no data as of now, I run into referential integrity problems. This means that there exits no Child records or Children in the PastorVill table for the Parent records for the VillCODES table. And this is rightly so, because a Parent record should have a Child record. So, in no way will this be possible, because  there are no records yet for the brand new PastorVill table. I tried adding a few fictitious records to the new PastorVill table and again tried to establish a One to Many relationship, but it gives me the same error !!

    So now, I cannot proceed, unless this problem is resolved !

    ~~Maneesh


    Sunday, April 29, 2012 2:52 PM
  • I'm sorry, but I don't know how to do anything in Access and so I don't know how you're supposed to create relationships in it. However, you have the relationship backwards. VillCODES is just a code table ... consequently, it's not ever going to be a parent table. PastorVill is the parent table and it has relationships  to both the VillCODES and Pastor tables.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, April 29, 2012 3:31 PM
  • Hi Bonnie,

    I don't understand the relationship that you are describing here ? How can the PastorVill table, which is a new table, with no data in it yet, become the Parent table to the VillCODES table which has so many Child records in it ??? This would be fundamentally wrong in itself ! This would mean that there are lots of Child records for no Parent records, IOW, Children without Parents, that is, orphan records and that's not allowed in Access or any other database for that matter. And this is the reason why I getting the error. And also this relationship would be wrong with the Pastor table too. PastorVill table can only be a Child to Pastor table. This works well, but does not work for the VillCODES table. 

    Please help me to understand if I am missing something here or unable to understand the concept.

    ~~Maneesh

    Monday, April 30, 2012 2:44 AM
  • Here's what the table schema should look like, along with the definitions for the PK and the FKs. I don't know how you do this in Access, sorry ... but here's the T-SQL code for SQL Server:

    CREATE TABLE PastorVill
    (
        PastorVillID bigint NOT NULL,
        PastorID bigint         NULL,
        VillID bigint           NULL,
        OtherData varchar(100)  NULL
    )
    GO
    ALTER TABLE PastorVill ADD CONSTRAINT
        PK_PastorVillID PRIMARY KEY CLUSTERED 
        ( PastorVillID ) WITH( IGNORE_DUP_KEY = OFF) 
    GO
    ALTER TABLE PastorVill ADD CONSTRAINT
        FK_PastorVill_Pastor FOREIGN KEY
        ( PastorID ) 
        REFERENCES Pastor ( PastorID )
    	
    GO
    ALTER TABLE PastorVill ADD CONSTRAINT
        FK_PastorVill_VillCODES FOREIGN KEY
        ( VillID ) 
        REFERENCES VillCODES ( VillCODESID )
    	
    GO


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, April 30, 2012 4:53 AM
  • Though I am not able to understand the syntax of the T-SQL code, I think the above code logically means, that PastorVill table is acting as a Parent table which has a Primary Key PastorVillID and this table has Foreign Key relationships with both the Pastor and VillCODES table. Am I correct ??

    This means that Pastor table is not a Parent table as thought previously ??

    I haven't had a chance to create this relationship in Access yet, but I will let you know how it goes.

    ~~Maneesh

    Monday, April 30, 2012 7:18 AM
  • Hi Bonnie,

    I tried my best to restructure the database having the third table act as a Parent table for both the Pastor and VillCODES table, yet this has some issues I thought I might discuss with you.

    First of all, the let me tell you that the VillCODES table has a field (column) called CODE (or could be any name for the village codes), that are 20-digit code numbers. So, in Access, in order to store that big of a number I have to give it a Field Size of Decimal format with a Precision of 20 digits so it can store it. I cannot give it a field size of Long Integer and select a Precision of either Auto or choose from the drop down which only offers numbers up to 15-digits. So how am I going to go about this issue here ??? I have to store these village codes which are 20-digits long and have no way of doing it !!!

    Secondly, in Access in order to have a Primary Key-Foreign Key relationship, the field sizes and field types should be the same size and type. So, for example, if Primary Key is an Auto Number, then the Foreign Key has to be of Field Size Long Integer and Data Type Number. Selecting this option would again not allow me to store that big of a number. I do not know what to do ?

    Thirdly, I have two options before me right now. Either change the way the application behaves altogether and change the user-interface OR think of  some other way the application should be used and change the Parent-Child relations of the tables themselves.

    I know what you will say of this, that you do not know how to use Access. That's very simply said, but do I have a way out of this ??? What are your thoughts about how to go about all this and ultimately see this problem getting solved ??

    Thanks.

    ~~Maneesh


    Monday, April 30, 2012 1:52 PM
  • Hi Maneesh,

    I think describing the relationship as a PK-FK relationship (as you did above) is a more intuitive description than calling it a parent/child relationship. I say that because I often do not consider Code tables to be parent tables.

    Anyway, to solve your problem of the different field types for the FK, use the correct field ... my example should have taken that into consideration I suppose, but I didn't think of it at the time. IOW, the actual CODE field for the Village codes could be used in your PastorVill table as additional information, but should not be used for the FK. I assume that your VillCODES table has a PK ... the PK of the VillCODES table is what you should be using as the FK in your new PastorVill table.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, April 30, 2012 5:07 PM
  • HI Bonnie,

    Thought I would just attach a screen shot of the tables and their relations in Access to confirm everything's ok and on track before we move on from this discussion to actual programming steps.


    As you can see above, the PastorID is the Foreign Key with the PastorVillCODES table. The Primary Key of the PastorVillCODES table is PastorVillID. These two have a one to many relationship. Now, on to the next relationship, between the PastorVillID and CODE table, as seen above, the PastorVillCODES table has only PastorVillID, PastorID and CODE fields (I did'nt put any other fields for now), and the CODE table has VillID (Auto Number field), CODE, VillName, BLOCK, DISTRICT fields.

    The primary key of the PastorVillCODES table is PastorVillID and the Primary Key of the CODE is VillID. Please let me know how to relate these two tables.

    Thanks.

    ~~Maneesh

    Tuesday, May 1, 2012 9:04 AM
  • Sorry for some of the mixup, Maneesh. I assumed that PastorID was the PK of the Pastor table, whereas your actual PK of that table is just called ID. You should be using PK's from the Pastor and VillCODES table as the FKs in the PastorVill table. I would NOT call that new table PastorVillCODES ... that is way too confusing, plus it's not a "Code" table. In fact, I don't think these kinds of things should be abbreviated at all and I would call it PastorVillage table, but maybe that's just me.  ;0)

    So your PastorVillage table should have a PK (you don't seem to have a naming convention for PKs, which you really should do ... my naming convention has always been tablenamekey). It should also have two FKs. Maybe since you've called your Pastor table PK ID but you also have a  PastorID (as a user-defined field?), perhaps we should do away with naming the FKs as I typically do and just use the letters "FK" as part of the column name.

    So, in the PastorVillage table then, you'd have a PastorFK column (its FK constraint is on the Pastor table's ID column), and you'd have a VillCodesFK column (its FK constraint is on the VillCODES table's VillID column).

    Is this starting to make any sense yet?  ;0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, May 1, 2012 3:34 PM
  • Hello Bonnie,

    Just recreated the tables following your guidelines. Wanted to show you a snapshot of the tables.

    I closely tried to mimic the relationships as told by you. I made a One to Many relationship with the Pastor and PastorVillage tables using the PK-FK terminology. This relation works Great ! BUT, when I come to the VillCodes table and the PastorVillage table, they would keep giving me this error. This error is due to the fact that the VillCodes and PastorVillage tables are not being related to each other on the SAME data types. IOW, both should have the same data types. As mentioned earlier, in Access, you've got to have a Data Type of Auto Number on the One Side (PK-side) of the table and a Number Data Type on the Many Side (FK-side) in order to make a relationship possible.

    Now, as said before, (this is what I have been trying to tell you from the previous post), here's where the problem comes in:

    The PK in VillCodes table (being VillID) is an Auto Number field which is what I don't need !! Why ?? I cannot have VillCodes (PK) as an Auto Number field because the actual VillCodes of the villages are 20-digit numbers and cannot be Auto Numbers. It's as simple as that. And this is the reason why when I try to make a One to Many join with the PastorVillage, I get this error.

    I have deliberately taken the VillID as PK in the VillCodes table as an Auto Number so that I could make a join with the PastorVillage table on its FK, VillCodesFK field. But as soon as I do that, I get this error because, the two have different Data Types. Remember, for a join to be possible in Access, the VillID (PK) from the VillCodes table should be a Data Type of Auto Number and the VillCodesFK column (FK) from the PastorVillage table should be a Number Data Type. Again the problem is, VillCodesFK column has to be of Number Data Type with a Field Size of Decimal and Format of 20 digits, otherwise if I make the VillCodesFK column of Data Type Number with a field Size of Long Integer and Format of Auto, I would loose some of the digits from the 20-digit village codes. Meaning to simply say, I would then not be able to store that big of a number within the Long Integer restriction.

    For a join to be possible in Access, PK of VillCodes table should be of Data Type Auto Number and FK of the PastorVillage table should be of Data Type Number of Field Size Long Integer and Format of Auto.

    Sounds confusing ?? Well, at the moment I am really in a fix what to do and how to move on, I'm stuck !!??? 

    Though I know by now your're not an Access expert, anyway, do you know what should be done now in order to overcome this issue and move on ???

    I await your response.

    ~~Maneesh

    Wednesday, May 2, 2012 12:37 PM
  • Maneesh, I really don't understand your dilemma. So you're saying that all of your PKs have to be of type Auto Number. And all of your FKs need to be of type Number with a Format of Auto. OK, so why can't you do that?

    In your original Pastor table, was the PK an Auto Number? And in your original VillCODES table, was the PK an Auto Number? (If not, I assume you changed them so they were).  So, in your new PastorVillage table, you'll have a PK (Auto Number), then you'll have two FKs, PastorFK and VillageFK ... which should both be of type Number with a Format of Auto. As you explained above, that is what you need to set up the relationships. Now, you can put additional info in the new PastorVillage table if you wish to, such as the Code of the village from the VillCODES table (this would be that 20-digit code). You don't have to duplicate some of that data in the new table, because you could retrieve it with a SELECT with JOINs, but that's up to you.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Wednesday, May 2, 2012 3:31 PM
  • Hello,

    This is turning out to be more of a database-centered discussion than the original topic of the thread !

    Well, anyway, finally I've got the database tables restructured and the relationships in the correct order as they should be. I have also included the Code column in the PastorVillage table for storing the 20-digit village codes in that table. 

    Hopefully, now I'll begin concentrating on the interface design and programming part. Think I'm in the right track now as everything's sorted out and looks great. I'll work on the interface design in the afternoon today and let you know how it went and then get back to you by the evening. 

    Thanks a lot for your patience and time. Really appreciate you !!!

    ~~Maneesh

    Thursday, May 3, 2012 6:17 AM
  • Hello,

    Ok, I got the database tables all fixed and designed the interface as well. Then I added the Pastor table onto Form1 by dragging from the Data Sources window. This sets all the BindingSources and TableAdapters to the Component Tray down below. Next I added a button called Add Ministry Details to Form1. This button is used to move on to Form2 and add additional details for the Pastor in Form1. In Form2, I drag a DataGirdView from the Tools Window and then used the smart-tag to add a Data Source to the DGV and I select the VillCodesBindingSource from the options given. 

    Here's the full coding that I use for the DataSet, Form1 and Form2:

    DataSet:

    Partial Class TellASIADataSet
        Partial Class PastorDataTable
    
            Private Sub PastorDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for the non-nullable fields
                Dim pastor As PastorRow = CType(e.Row, PastorRow)
    
                pastor.LName = "Enter a New Name"
            End Sub
        End Class
        Partial Class VillCODESDataTable
    
            Private Sub VillCODESDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
    
                'Set defaults for non-nullable fields
                Dim village As VillCODESRow = CType(e.Row, VillCODESRow)
                village.Village = "New Village"
            End Sub
        End Class
    
    End Class
    


    Form1:

    Public Class Form1
    
        Private Sub PastorBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
            'TODO: This line of code loads data into the 'TellASIADataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.TellASIADataSet.Pastor)
        End Sub
    
        Private Sub PastorBindingNavigatorSaveItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    
        Private Sub cmdAddMinistryDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAddMinistryDetails.Click
            Me.PastorBindingSource.EndEdit()
    
            If Me.PastorBindingSource.Position > -1 Then
    
                'Get the current Pastor row
                Dim row As TellASIADataSet.PastorRow
                row = CType(CType(Me.PastorBindingSource.Current, DataRowView).Row, TellASIADataSet.PastorRow)
    
                'Open the detail form passing the DataSet and PastorID
                Dim frm As New Form2(Me.TableAdapterManager, Me.TellASIADataSet, row.PastorPK)
                frm.Show()
            End If
        End Sub
    
        Private Sub PastorBindingNavigatorSaveItem_Click_2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PastorBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    End Class
    


    Form2:

    Public Class Form2
    
        Dim IsFormLoad As Boolean = True
        Sub New(ByRef TableAdapterManager As TellASIADataSetTableAdapters.TableAdapterManager, ByRef ds As TellASIADataSet, ByVal id As Integer)
    
            ' This call is required by the Windows Form Designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            ' Set the DataSource of the BindingSource and then set the Filter
            ' so that the correct row will be displayed on the details form.
            Me.TellASIADataSet = ds
            Me.VillCodesBindingSource.DataSource = ds.VillCodes
            Me.VillCodesBindingSource.Filter = "PastorPK = " & id.ToString
        End Sub
    
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'TellASIADataSet.VillCodes' table. You can move, or remove it, as needed.
            Me.VillCodesTableAdapter.Fill(Me.TellASIADataSet.VillCodes)
            ''TODO: This line of code loads data into the 'TellASIADataSet.VillCODES' table. You can move, or remove it, as needed.
            Me.VillCodesTableAdapter.Fill(Me.TellASIADataSet.VillCodes)
            Me.VillCodesDataGridView.DataSource = Me.VillCodesBindingSource
    
            'DistrictComboBox
            With DistrictComboBox
                .DataSource = GetDataTable("SELECT DistrictID, District FROM VillCodes")
                .DisplayMember = "District"
                .ValueMember = "DistrictID"
            End With
    
            'BlockComboBox
            With BlockComboBox
                .DataSource = GetDataTable("SELECT BlockID, Block FROM VillCodes")
                .DisplayMember = "Block"
                .ValueMember = "BlockID"
            End With
            IsFormLoad = False
        End Sub
    
        Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            Me.VillCodesBindingSource.CancelEdit()
        End Sub
    
        Private Sub cmdCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Me.VillCodesBindingSource.CancelEdit()
            Me.Close()
        End Sub
    
        Private Sub cmdOK_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdOK.Click
            Me.VillCodesBindingSource.EndEdit()
            SaveCheckedVillages()
        End Sub
    
        Private Sub SaveCheckedVillages()
    
            'Get all the checked villages
            Dim StrCheckedVillID As String = ""
            For i = 0 To VillageChkListBox1.CheckedItems.Count - 1
                StrCheckedVillID = StrCheckedVillID & IIf(StrCheckedVillID.Trim = "", "", ",") & Convert.ToInt16(Mid(VillageChkListBox1.CheckedItems(i).ToString, 1, 2))
            Next
    
            If StrCheckedVillID <> "" Then
    
                StrCheckedVillID = "UPDATE PastorVillage Set Code = 1 Where VillID IN (" & StrCheckedVillID & ")"
                ExecuteCommand(StrCheckedVillID)
                MsgBox("Saved", MsgBoxStyle.Exclamation)
            End If
        End Sub
    
        Public Sub ExecuteCommand(ByVal Strcommand As String)
            Try
                Dim objResult As Object
    
                objResult = DBNull.Value
                Dim ConnetionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Joy\Documents\TellASIADatabase.accdb"
    
                Using connectionOledb As New OleDbConnection(ConnetionString)
                    connectionOledb.Open()
    
                    Using commandOledb As New OleDbCommand(Strcommand, connectionOledb)
                        objResult = commandOledb.ExecuteNonQuery
                        commandOledb.CommandTimeout = 20000
                    End Using
                End Using
    
            Catch ex As Exception
                Throw ex
            End Try
    
        End Sub
    
        Private Sub BlockComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles BlockComboBox.SelectedIndexChanged
            If IsFormLoad Then Exit Sub
            If BlockComboBox.SelectedIndex >= 0 Then
                Dim Str As String
                Dim dtVill As DataTable = New DataTable
    
                Str = "SELECT distinct VillID, Village FROM VillCodes WHERE BlockID = " & BlockComboBox.SelectedValue
                dtVill = GetDataTable(Str)
    
                'Clear the Items before re-adding the new items
                VillageChkListBox1.Items.Clear()
    
                For Each drVill In dtVill.Rows
                    VillageChkListBox1.Items.Add(Convert.ToInt32(drVill("VillID")) & "-" & Convert.ToString(drVill("Village")))
                Next
            End If
        End Sub
    
        Private Sub DistrictComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DistrictComboBox.SelectedIndexChanged
            If IsFormLoad Then Exit Sub
    
            If DistrictComboBox.SelectedIndex >= 0 Then
                Dim Str As String
                Dim dtBlock As DataTable = New DataTable
    
                Str = "SELECT BlockID, Block FROM VillCodes WHERE DistrictID = " & DistrictComboBox.SelectedValue
                dtBlock = GetDataTable(Str)
    
                With BlockComboBox
                    .DataSource = dtBlock
                    .DisplayMember = "Block"
                    .ValueMember = "BlockID"
                End With
            End If
        End Sub
    
        Private Function GetDataTable(ByVal commandStr As String) As DataTable
            Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Joy\Documents\TellASIADatabase.accdb"
            Dim conn As OleDbConnection = New OleDbConnection(connStr)
            Dim comm As OleDbCommand = New OleDbCommand(commandStr, conn)
            Dim dataadapter As OleDbDataAdapter = New OleDbDataAdapter(comm)
    
            Dim dtResult As DataTable = New DataTable()
    
            '---Open the connection---
    
            conn.Open()
    
            '---Fill the DataTable---
    
            dataadapter.Fill(dtResult)
    
            '---Close the connection---
    
            conn.Close()
    
            Return dtResult
        End Function
    
        Private Sub VillCodesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles VillCodesBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.VillCodesBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.TellASIADataSet)
    
        End Sub
    End Class
    

    The application loads and displays information in TextBoxes from the Database on Form1. But as soon as Add Ministry Details button is clicked, it starts giving me an error. I tried to look into the error and debug it, nothing seemed to work. Take a look at the error here:

    I assume this error is because it cannot find PastorPK column in the VillCodes table. I tried adding that column to VillCodes table and then again creating the DataSet, deleting the old one, and re-running the application. This time the application does find the PastorPK column but starts giving me another error.

    Is the PastorPK column really necessary in VillCodes table ???

    What should I do to remove the error in the program ?

    ~~Maneesh

    Thursday, May 3, 2012 12:43 PM
  • Maneesh,

    Instead of blindly adding an extra column to the VillCODES table just because you get an error that says it can't find PastorPK there, think about *why* that error occurred in the first place. No, obviously the VillCODES table does NOT need the PastorFK column. Why would it? So then, the question becomes *why* does the app *think* it should be there? Obviously something is wrong somewhere, but it's not solved by adding that column (as you subsequently found out) because that column does not need to be in that table.

    The problem is that Form2 should *not* be binding its grid to the VillCodesBindingSource. It should be binding to the PastorVillageBindingSource. This was the whole point of adding that new PastorVillage table.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, May 3, 2012 2:52 PM
  • Adding another post rather than updating the previous one:

    Everything else on Form2 (your ComboBoxes and your CheckedListBox) should be binding to VillCODES, it's only the grid that should be bound to PastorVillage.

    You may be missing the point of what we're trying to do:

    For a new Pastor, there will be nothing displayed in the grid when the user first brings up Form2. Once the user chooses a District and Block from those two ComboBoxes, then the CheckedListBox gets populated with all the villages from that District/Block. It looks like you already have the code to do that (I just glanced at it quickly, didn't really study it). Once Villages have been chosen by checking them, then you add rows to the PastorVillage DataTable for each checked Village. You can do this either as the Village is checked, or after focus leaves the CheckedListBox or have a button that does it. Once you've added the rows to the DataTable, they'll show up in the grid and the user can then fill in the rest of the data.

    For an existing Pastor, it's a little trickier, because there will already be some data in the PastorVillage table (which will display in the grid), but there will need to be some coordination with the ComboBoxes and the CheckedListBox.


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Thursday, May 3, 2012 3:48 PM
  • HI Bonnie,

    Great! I think both your posts above are solid and give a clear understanding why the error is being raised. I have made the relevant changes regarding the BindingSource and added PastorVillageBindingSource instead to the DGV. 

    I am going through the code debugging it step by step as much as I can, trying to see the what's wrong to the best of my ability. Will let you know where I'm stuck, but let me make effort first.

    Thanks for your patience, understanding and willingness to help.

    ~~Maneesh

    Friday, May 4, 2012 4:55 AM
  • Hi Bonnie,

    Still working on it, will get back to you. Allow me a couple more days then I will need your help.

    Appreciate all your help.

    ~~Maneesh

    Sunday, May 6, 2012 2:57 AM
  • Hello,

    Just wanted to let you know, I am still working on the application, I have a lot of things to add to the VillCODES table and so its taking some time. Once this is done, I will need your help though.

    This thread is still alive, its not dead yet.

    Thanks.

    ~~Maneesh

    Tuesday, May 8, 2012 2:28 AM
  • Hi Maneesh,

    Just a quick question before I turn in for the night ... what kinds of things are you adding to your VillCODES table?


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, May 8, 2012 4:55 AM
  • Hi Bonnie,

    Well, in order for the ComboBoxes to work correctly, I need a DistrictID, District name, BlockID and Block name in the VillCODES table. Now this table is a huge table as we have thousands of villages in hundreds of blocks. So for example, adding a .ValueMember would require both the DistrictID and BlockID with DisplayMember of the names of Districts and Blocks.

    So this is what is taking time. Then after this I will work on the code, which is when I would need your help.

    Thanks for asking.

    ~~Maneesh


    Tuesday, May 8, 2012 6:17 AM
  • Sorry, I went to bed for you replied.

    You should probably normalize your tables a bit more. The way it is now, with the VillCODES table, you're probably repeating every District name and Block name in that table, thousands of times. What you really need is a District table, a Block table and then the VillCODES table. The VillCODES table will, of course, need FKs to those two tables.

    I'm sorry that I didn't mention that before ... I guess I kind of assumed that you would be using the District ID and Block ID for your Combos. After all, the way you had it previously, the user had to know the ID anyway because you had them typing it in TextBoxes.

    UPDATE -- the VillCODES table doesn't actually need to have FKs to those other tables. You've already got DistrictID and BlockID and that's good enough to filter on, based on ComboBox selections. Forget that I mentioned FKs!!  ;0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Tuesday, May 8, 2012 3:42 PM
    Tuesday, May 8, 2012 1:26 PM
  • Hi Bonnie,

    Sorry, I had other important things to look into. 

    Ok, so I've stopped what I was trying to do with the VillCODES table and now in order for both the ComboBoxes to work correctly, I've got to get the District and Block tables inside Access. That I can easily import from Excel where I have them into Access. So, I'll be working on this aspect in the afternoon today and then I'll get back to you when I'm finished to go to the next level in this application which already has taken so long to build.

    Appreciate your time and help.

    ~~Maneesh

    Friday, May 11, 2012 5:15 AM