none
How to retrieve ID and add rows to data table in the database

    Întrebare

  • Hi All!

    I am a newbie to VB.NET.

    I have an Access 2007 database which has 5 tables related to one another in a Parent-Child relationship. I have an application in which I enter details of a pastor into text boxes and 2 ComboBoxes, one for selecting District and the other for selecting Block (sub-district) and a CheckedListBox called VillageChkListBox1 on a form. 

    The user enters details of a pastor into the TextBoxes and chooses a District from the District ComboBox which filters the Block ComboBox to display only Blocks (sub-districts) based on the selection from the District ComboBox. Based on the selection made in the Block ComboBox, the CheckedListBox is populated with the list of villages which the user can check mark for the particular pastor. 

    I use the BindingNavigator bar at the top of the form to save the details entered into the database. The user presses the, '+' sign to do all the adds and the floppy sign to do all the saves. 

    The new pastor details entered into the textboxes are saved in the Pastor table of the database and the villages selected from the CheckedListBox goes into a separate table called the PastVillage table. 

    The PastVillage table has PastVillID(Auto-number field), PastorID, VillID among other fields in the database table.

    My question is, how can I retrieve the PastorID from the Pastor table and the checked villages from the CheckedListBox and save both of them by adding rows to the PastVillage table in Access ??

    Can anyone please help me out ?

    Regards,

    ~~Maneesh

    4 aprilie 2012 03:28

Toate mesajele

  • Hi Maneesh,

    Are you using typed dataset to include the 5 data tables?  If so, the auto-generated ID key from the database will be retrieved from the database side to the client app side automatically after the object is new created and saved in the database.

    If it is convenient for you, could you please create a demo project for us to do further investigation?  You can ping me at misun@microsoft.com

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    5 aprilie 2012 05:31
  • Hi Michael,

    Yes, I am using typed dataset to include the 5 tables. 

    I don't understand what you mean. I thought the @@Identity values don't work with Microsoft Access only with SQL. But on the contrary, I am using Access and not SQL. So is there a problem with retrieving the PastorID I am looking for ?? 

    Regarding the demo, the application I am creating is simple. All I have is a Windows form on which I dragged the Pastor table as Details view from the Data Sources Window. This gave me a BindingNavigator bar on top of the form and a bunch of compoments like DataSet, TableAdapterManager, BindingSource etc down below the components tray. Now I dragged 2 ComboBoxes and a CheckedListBox from the tools onto the form. I have attached the code below which I am using for saving whatever is entered into the TextBoxes and code for the 2 ComboBoxes and the CheckedListBox. Now, when I press the '+' button on the BindingNavigator to save the details of the pastor, this takes whatever was entered into the TextBoxes and dumps them into the Pastor table in Access. Now what I want to do is to enable BOTH the Pastor and the Checked Villages from the CheckedListBox (by making use of the 2 ComboBoxes, which 'filter' based on each other's selection) and dump them into another table in Access called the PastVillage table.

    Here's the code I use:

    Imports System.Data.OleDb
    
    Public Class Form1
    
        Dim IsFormLoad As Boolean = True
    
        Private Sub PastorBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PastorBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.PastorBindingSource.EndEdit()
            Me.DistrictBindingSource.EndEdit()
            Me.BlockBindingSource.EndEdit()
            Me.VillageBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.PastorsDataSet)
            'You can also save all checked villages in one go
            SaveCheckedVillages()
        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 'PastorsDataSet.Pastor' table. You can move, or remove it, as needed.
            Me.PastorTableAdapter.Fill(Me.PastorsDataSet.Pastor)
            'TODO: This line of code loads data into the 'PastorsDataSet.District' table. You can move, or remove it, as needed.
            Me.DistrictTableAdapter.Fill(Me.PastorsDataSet.District)
            'TODO: This line of code loads data into the 'PastorsDataSet.Block' table. You can move, or remove it, as needed.
            Me.BlockTableAdapter.Fill(Me.PastorsDataSet.Block)
            'TODO: This line of code loads data into the 'PastorsDataSet.Village' table. You can move, or remove it, as needed.
            Me.VillageTableAdapter.Fill(Me.PastorsDataSet.Village)
    
            With DistrictComboBox ' DISTRICTComboBox
                .DataSource = GetDataTable("SELECT  DistrictID, District FROM District") 'Changed the query here 
                .DisplayMember = "District"
                .ValueMember = "DistrictID" 'Changed the Value member
            End With
    
            With BLOCKComboBox 'BLOCKComboBox
                .DataSource = GetDataTable("SELECT BlockID, Block FROM Block") 'Changed the query here 
                .DisplayMember = "Block"
                .ValueMember = "BlockID" 'Changed the Value member
            End With
            IsFormLoad = False
        End Sub
    
        Private Sub BLOCKComboBox_SelectedIndexChanged(ByVal sender As System.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, VillName FROM Village WHERE BlockID = " & BLOCKComboBox.SelectedValue
                dtVill = GetDataTable(Str)
                VillageChkListBox1.Items.Clear() 'Clear the items before re-adding the new items
                For Each drVill In dtVill.Rows
                    VillageChkListBox1.Items.Add(Convert.ToInt32(drVill("VillID")) & " - " & Convert.ToString(drVill("VillName")))
                Next
            End If
        End Sub
    
        Private Sub DISTRICTComboBox_SelectedIndexChanged(ByVal sender As System.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 Block WHERE DistrictID = " & DISTRICTComboBox.SelectedValue 'Changed the query here 
                dtBlock = GetDataTable(Str)
                With BLOCKComboBox
                    .DataSource = dtBlock
                    .DisplayMember = "Block"
                    .ValueMember = "BlockID" 'Changed the Value member
                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\Pastors.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 SaveCheckedVillages()
    
            'Get all the checked village Id's ;
            Dim StrCheckedVillID As String = ""
            For Each row In VillageChkListBox1.CheckedItems
                StrCheckedVillID = StrCheckedVillID & IIf(StrCheckedVillID.Trim = "", "", ",") & Convert.ToInt16(Mid(row, 1, 2))
            Next
    
            If StrCheckedVillID <> "" Then
    
                StrCheckedVillID = "Update Village Set Column1 = 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\Pastors.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
    
    End Class

    Now, I can't figure out how to retrieve the PastorID that is being inserted into the Pastor table and add a row or rows for the Pastor AND the Villages where he works and put them both into the PastVillage table.

    Can you please help ??

    Thanks for you time.

    Best Regards,

    Maneesh

    5 aprilie 2012 05:50
  • Hi Maneesh,

    Please check whether the sample code here can solve the issue, http://www.vbforums.com/showthread.php?p=4060958.  The MVP provided the typed dataset version as well.   And the VB version is here, http://www.vbforums.com/showthread.php?t=659052.  

    Good day!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us


    5 aprilie 2012 06:46
  • HI Michael,

    Thanks for taking time and pointing me to a wonderful resource. Correct me if I am wrong but the MVP that wrote the thread is taking the value of the retrieved ID (Parent) and putting it into the database in a separate table and the child goes into a separate table. Am I correct in my understanding of this or not ?? Please help me understand.

    I also found a wonderful blog post by Beth Massi which deals with the @Identity crisis : http://blogs.msdn.com/b/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx 

    However when I used this, the I get the same thing. That is, the Parent data saves into a separate table and the Child data into a different table. This is NOT what I want. I want to retrieve the ParentID and the SELECTED villages from the CheckedListBox to be DUMPED into a NEW table, called the PastorVillage table. Is this possible ???

    Just to give you an idea of the table relationships I have set up, here's a screenshot in Access database relations:

    I hope this will give you a general idea of how the tables are related to each other.

    I hope you will point me to the right direction on how to do this.

    Best Regards,

    Maneesh

    5 aprilie 2012 11:53
  • Hi Maneesh,

    I tried to repro the issue at my side, but I don't quite understand how do you bind the data on the form.  Could you please share a deme project and testing Access db file?   You can send the project to me through misun@microsoft.com

    Have a nice weekend!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    6 aprilie 2012 06:33
  • Hi Maneesh,

    After I checked db schema and sample codes, I have a little confused.  Why the Village table has both DistrictID and BlockID.  If I understand it correctly, one District can have many Blocks and one Block can have many Villages, then the Village.DistrictID is not necessary.  

    Also, for the binding part, each Pastor can have many Districts but we can only select one District in the form.  It makes me think that one District should have many Pastors.  The relationship is opposite.  Could you please explain the logic more detailed?  

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    9 aprilie 2012 07:47
  • Hi Michael,

    Thanks for getting back with your questions.

    First question: Why does the village table have both the DistrictID and BlockID ?

    Ans: I made this purposefully like this. Though you are right, I could have made it more simple by just including the relevant fields and taking out the DistrictID and BlockID, but my purpose of doing this different. Once, when the user has made all the entries in the database using the front end application, I need to convert this file (.accdb) into a .dbf file and use it for other analysis work where I would be needing these two additional fields.

    Second quest: Binding part question.

    Ans: Yes, you are correct. A district has many blocks and a block has many villages inside. Also each District can have many Pastors who are working in that district. BUT generally speaking in REALITY, One pastor RARELY has work in more than one district. He has so much work in ONE district, that he is unable to even think of going on to the another district. In my country, there are about 1.6 million villages just in our entire State !!! Each block may have more than a thousand villages. So, generally for our purposes, we consider that a District has many Pastors but each pastor DOES NOT have MANY DISTRICTS or is not working in many districts.

    So I think I have set up the relationships correctly. If you still think that something is wrong or should be configured in another way, a better way, then please let me know.  

    As you may have known by now what I want to be able to do do is simply add a new Pastor from the textboxes on the form and then select the villages where he is working in and then, put all the Pastors in the Pastor table and also retrieve the PastorID from the Pastor table and then put both the Pastor and Villages selected from the CheckedListBox into the PastorVillage table.

    Some things I can understand in theory, like putting all the checked villages into a String and then saving that String of checked values into the database to reduce the number of round trips which leads to better memory management and performance. While other things like retrieving the @@Identity value of the PastorID from the Pastor table(Parent table) and keeping both the Parent and Child records together, I simply do not know how to go about.  These are the two things I am at a loss to understand how to do.

    If you need any further clarification, please let me know at the earliest so I can explain that more in detail.

    Awaiting your response.

    Best Regards,

    Maneesh

    10 aprilie 2012 13:45
  • Hi Maneesh,

    Thanks.  With the PastorVillage table, I can understand that each Village can have many Pastors while each Pastors can work in many Villages.  However, there is one-many relationship betwen Pastor and District, which means one Pastor can have many District, however, each District can have only one Pastor.  It makes me very confused. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    11 aprilie 2012 02:02
  • Hi Michael,

    I understand your point of confusion. It is just a matter of looking at things. Though you do find a one - to - many relationship between the Pastor and the District table, however, here's how I look at it. One PastorID from the Pastor table works in ONE district from the District table. The person doing the data entry knows well that one Pastor can have only one district assigned to him (there are rarely exceptions). So in this situation, when the person doing the data entry is filling in details for a single new pastor by pressing on '+' sign to add a new record, he/she will select only ONE district and ONE block from the 2 ComboBoxes on the form and can select multiple villages from the CheckedListBox and save the record to the database. The PastorID of the new Pastor just added will be unique as it will be given by ADO.NET on the application side(temporary place holders like -1, -2 -3 and so on) and when it goes into Access it will be allotted PastorID's by the Auto number field. Hence, what I mean to say is that there is no chance of the same PastorID being allotted another District or Many Districts from the application.

    However, as I said from the very beginning, I am always open to change and can reorder or restructure my database and the relationships if you strongly feel that something can go wrong with the application after it is deployed. 

    Hence, I am flexible and willing to change if it is so required.

    Thanks for taking your time.

    Appreciate you very much!

    ~~Maneesh

    11 aprilie 2012 02:56
  • Hi Maneesh,

    Thanks for the following up!  I will try to involve another senior engineer in this case. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us

    11 aprilie 2012 06:44
  • Hi Michael,

    Any news yet ? I am still waiting for your response.

    Have a nice day,

    Kind regards,

    Maneesh

    14 aprilie 2012 11:18
  • Hi,

    You could  visit the below link to see the various paid support options that are available to better meet your needs if you requires a more in-depth level of support.

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone
     
     
     
    Regards

    18 mai 2012 07:23