none
Parent and Child Database and DataGridView RRS feed

  • Question

  • Hello,

    Following Karen's advice to create one parent table and one child table to make it easier to add new projects in my company instead create new table for each project, I was able to do it, having them in this way:

    TABLEPARENT

    =========

    ID_PROYECTO              NUM    PK

    NOMBRE_PROYECTO    VARCHAR50)

    ----

    TABLECHILD

    ==========

    ID_PROYECTO    NUMERIC     PK & FK

    REGISTRO         NUMERIC      PK

    NOMBRE

    FECHA

    FACTURA

    ----

    Til now, I can make the datagridview show data according to combobox which have

    DataSource: TABLEPARENTbindingsource

    DisplayMember: NOMBRE_PROYECTO

    ValueMember: ID_PROYECTO

    SelectedValue: TABLECHILD.ID_PROYECTO

    And the datagridview's datasource is FK_NAMEOFRELATIONSHIP

    With the new year coming, I want to add a year field to those tables, so when the 2018 begin, all the values start from 0. How can I add this to another combobox and use the same datagridview showing all the records from 2017 and then start from 0 when I select 2018 in the combobox2?

    Thanks!

    Monday, November 20, 2017 6:16 PM

Answers

  • In regards to a 3 parent/child relationship, the following code sample shows how to construct one. Disregard the controls used, instead focus on the relationship construction.

    Note the language extension in DataRelationsExtensions.vb is used to keep the operation class code cleaner than if the DataRelation stuff was done in that class.

    Note the data diagram showing the three tables. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by joyi6388 Thursday, December 14, 2017 12:40 PM
    Monday, November 20, 2017 8:24 PM
    Moderator
  • Hi joyi6388,

    According to your description, you want to save combobox valuemembers into database. The combobox value members is the selectedvalue for combobox

    Dim value As String=ComboBox1.SelectedValue

    Best Regards,

    Cherry


    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.

    • Marked as answer by joyi6388 Thursday, December 14, 2017 12:39 PM
    Monday, November 27, 2017 2:17 AM
    Moderator

All replies

  • Hello,

    I understand the part about parent/child data relations but not following in regards to 

    I want to add a year field to those tables, so when the 2018 begin, all the values start from 0

    How does "year field" get to be 0 as years  I would think would be e.g. 2015, 2016, 2017, 2018? 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 20, 2017 6:25 PM
    Moderator
  • Yes I explained it wrong, sorry,

    Registro is independent for each project. Let's say that Project1 close the Dec.31 with Registro No. 321, and Project2 close with Registro 145. When new year starts, Project1 and Project2 continues but Registro field has to be reset to 0.

    Thanks!

    Monday, November 20, 2017 6:33 PM
  • Been thinking about this.

    Well high level you would do this in the loading of data using a where condition for getting the end of year. 

    Here are some starting points for using T-SQL methods to worth this dates and years. I did this in SQL-Server Management Studio.

    SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS [StartOfYear],
           DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS [EndOfYear];
    
    SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS [StartOfYear],
           DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS [LastDayOfYear],
           DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS [FirstOfNextYear],
           DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS [LastTimeOfYear];
    
    SELECT YEAR(GETDATE()) AS [CurrentYear];
    
    SELECT YEAR(GETDATE()) - 1 AS [PriorYear];

    Results so you can see what we get

    I don't have time (busy day at work) to put something together try it out myself or even figure out the best code logic so this is all I have right now.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 20, 2017 7:32 PM
    Moderator
  • Thank you Karen but I really don't understand what to do with that. I think it can be like 3 parent child relationship, with TABLECHILD linked to TABLEPARENT, and tableparent linked to TABLEYEAR. So when selecting a year, e.g. 2017 in a combobox and selecting a project, e.g. project1 in another combobox, the datagridview shows the records from 2017 for project1

    I actually have the simple parent/child example working, but I want to add the year.

    Thank you again, and don't worry for your time, just when you can, I'll appreciate your answer.

    Monday, November 20, 2017 8:04 PM
  • In regards to a 3 parent/child relationship, the following code sample shows how to construct one. Disregard the controls used, instead focus on the relationship construction.

    Note the language extension in DataRelationsExtensions.vb is used to keep the operation class code cleaner than if the DataRelation stuff was done in that class.

    Note the data diagram showing the three tables. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by joyi6388 Thursday, December 14, 2017 12:40 PM
    Monday, November 20, 2017 8:24 PM
    Moderator
  • Thank you Karen but I really don't understand what to do with that. I think it can be like 3 parent child relationship, with TABLECHILD linked to TABLEPARENT, and tableparent linked to TABLEYEAR. So when selecting a year, e.g. 2017 in a combobox and selecting a project, e.g. project1 in another combobox, the datagridview shows the records from 2017 for project1

    I actually have the simple parent/child example working, but I want to add the year.

    Thank you again, and don't worry for your time, just when you can, I'll appreciate your answer.

    Hi joyi6388,

    According to your description, you want to add year field in another table called TABLEYEAR, you can add YearID(PK), ID_PROYECTO(FK), YearTime field in the table. Now you can query all unduplicated yeartime from this table to fill into datatable to bind combobox datasource.

    Please note: you just need to query year and don't need to query Month and day from table.

    Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Sample\Data1.mdf;Integrated Security=True"
            Dim sql As String = "select distinct CONVERT(varchar(4),Yeartime,120) as YearTime from Test13"
            Dim con As New SqlConnection(str)
            con.Open()
            Dim dt As New DataTable
            Dim com As New SqlCommand(sql, con)
            Dim adapter As New SqlDataAdapter(com)
            adapter.Fill(dt)
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "YearTime"
            ComboBox1.ValueMember = "YearTime"
            con.Close()

    Best Regards,

    Cherry


    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.

    Wednesday, November 22, 2017 6:16 AM
    Moderator
  • Hi all,

    Thanks for your answer.

    I was busy doing the thing that Karen an kunmo proposed. I succeeded making the datagridview shows the selected project on combobox1 from the year selected on combobox2. I've added the Year field to TableChild and TableParent, and created the TableYear, so TableChild.ID_Proyecto FK to TableParent.ID_Proyecto, and TableParent.Year FK to TableYear.ID_Year

    I'll like now to save the data I've wrote in textboxes into database, everything is fine but I don't know how to insert into the database the Combobox1.ValueMember from TableParent.ID_Proyecto, so when I select a project from Combobox1 the ID_Project keep saving right.

    Best regards.

    Thursday, November 23, 2017 1:19 PM
  • Hi joyi6388,

    According to your description, you want to save combobox valuemembers into database. The combobox value members is the selectedvalue for combobox

    Dim value As String=ComboBox1.SelectedValue

    Best Regards,

    Cherry


    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.

    • Marked as answer by joyi6388 Thursday, December 14, 2017 12:39 PM
    Monday, November 27, 2017 2:17 AM
    Moderator