locked
data to array or dataset???? RRS feed

  • Question

  • I have a win form (visual basic) with a datagridview

     

    I fill the datagridview with the query filltoday

     

    Query filltoday

     

    SELECT     dbo.T_Employee.FirstName + " " + dbo.T_Employee.Name AS "Full name", dbo.T_TimeRegistration.EmpId, dbo.T_MachGrp.Description,

                          dbo.T_TimeRegistration.StartTime, dbo.T_TimeRegistration.EndTime

    FROM         dbo.T_MachGrp, dbo.T_ProdBillOfOper, dbo.T_ShopDoc, dbo.T_TimeRegistration, dbo.T_Employee

    WHERE     dbo.T_MachGrp.MachGrpCode = dbo.T_ProdBillOfOper.MachGrpCode AND

                          dbo.T_ProdBillOfOper.ProdHeaderDossierCode = dbo.T_ShopDoc.ProdHeaderDossierCode AND

                          dbo.T_ProdBillOfOper.ProdBOOLineNr = dbo.T_ShopDoc.ProdBOOLineNr AND

                          dbo.T_ShopDoc.ShopDocCode = dbo.T_TimeRegistration.ShopDocCode AND dbo.T_TimeRegistration.EmpId = dbo.T_Employee.EmpId

    GROUP BY dbo.T_TimeRegistration.EmpId, dbo.T_Employee.FirstName, dbo.T_TimeRegistration.ShopDocCode, dbo.T_TimeRegistration.HourCode,

                          dbo.T_TimeRegistration.RegDate, dbo.T_MachGrp.Description, dbo.T_Employee.Name, dbo.T_TimeRegistration.StartTime,

                          dbo.T_TimeRegistration.EndTime

    HAVING      (dbo.T_TimeRegistration.HourCode = 'PR') AND (dbo.T_TimeRegistration.ShopDocCode = ?) AND (dbo.T_TimeRegistration.RegDate = ?)

     

    using the following sentence:

            Me.T_TimeRegistration1TableAdapter.Filltoday(Me.Stinis.T_TimeRegistration1, shopdoc, datum)

     

     

    The result in the data grid is the following:

     

    full name

    id nr

    description

    starttime

    endtime

    name1

    153     

    construction

    26820

    0

    name2

    181     

    construction

    27600

    28140

    name2

    181     

    construction

    28140

    0

     

    Now I need to alter these data without changing the database. So I think I have to transfer the data to an array. The endtime where it is 0 must come the actual time of this moment, solved timeofday with the following:

     

            tijd = TimeOfDay

            Tijdconversie = (Hour(tijd) * 3600) + (Minute(tijd) * 60)

     

    And I have to add the column totaltime (being endtime-starttime/3600)

     

    After altering this I have to put the following columns full name, id nr, description, totaltime in a datagridview again

     

    Question 1 : How do I get my data from the query in an array

     

    Question 2: How do I get my data in a datagridview

    Tuesday, June 20, 2006 2:27 PM

Answers

  • You can change the local dataset as much as you want and the changes will not make it back to the database unless you execute a .Update method AND have the Insert/Update/Delete SQL command properties set in the tableadapter.

    To be especially safe if your paranoid about that you might meet both of these criteria.  Then create a dataview from the dataset and work with the

    dim dv as new dataview = dataset.tables(0).defaultview

    And to get this data in the datagridview you simply need to set the datasource on the datagridview to the datatable or dataview that you are wanting to display in the grid.

    datagridview1.datasource = dv

     

     

    Tuesday, June 20, 2006 3:34 PM
  • I came up with the following solution after 3 days of seeking for answers, see blue text

    SELECT     dbo.T_TimeRegistration.EmpId, dbo.T_Employee.FirstName + " " + dbo.T_Employee.Name AS "Full name", dbo.T_MachGrp.Description,
                          dbo.T_TimeRegistration.ShopDocCode, CASE dbo.T_TimeRegistration.EndTime WHEN 0 THEN ((DATEPART(hh, GETDATE()) * 3600) + (DATEPART(mi,
                          GETDATE()) * 60) - dbo.T_TimeRegistration.StartTime) / 3600 ELSE ((dbo.T_TimeRegistration.EndTime - dbo.T_TimeRegistration.StartTime) / 3600)
                          END AS Totaltime
    FROM         dbo.T_DossierMain, dbo.T_ProductionHeader, dbo.T_ProdBillOfOper, dbo.T_MachGrp, dbo.T_ShopDoc, dbo.T_TimeRegistration,
                          dbo.T_Employee
    WHERE     dbo.T_DossierMain.DossierCode = dbo.T_ProductionHeader.DossierCode AND
                          dbo.T_ProductionHeader.ProdHeaderDossierCode = dbo.T_ProdBillOfOper.ProdHeaderDossierCode AND
                          dbo.T_ProdBillOfOper.MachGrpCode = dbo.T_MachGrp.MachGrpCode AND
                          dbo.T_ProdBillOfOper.ProdHeaderDossierCode = dbo.T_ShopDoc.ProdHeaderDossierCode AND
                          dbo.T_ProdBillOfOper.ProdBOOLineNr = dbo.T_ShopDoc.ProdBOOLineNr AND
                          dbo.T_ShopDoc.ShopDocCode = dbo.T_TimeRegistration.ShopDocCode AND dbo.T_TimeRegistration.EmpId = dbo.T_Employee.EmpId
    GROUP BY dbo.T_TimeRegistration.EmpId, dbo.T_Employee.FirstName, dbo.T_TimeRegistration.ShopDocCode, dbo.T_TimeRegistration.HourCode,
                          dbo.T_TimeRegistration.RegDate, dbo.T_MachGrp.Description, dbo.T_Employee.Name, dbo.T_TimeRegistration.StartTime,
                          dbo.T_TimeRegistration.EndTime, dbo.T_DossierMain.OrdNr
    HAVING      (dbo.T_TimeRegistration.HourCode = 'PR') AND (dbo.T_TimeRegistration.RegDate = ?) AND (dbo.T_DossierMain.OrdNr = ?)
    ORDER BY dbo.T_TimeRegistration.EmpId, dbo.T_TimeRegistration.ShopDocCode

    Monday, June 26, 2006 6:37 AM

All replies

  • You can change the local dataset as much as you want and the changes will not make it back to the database unless you execute a .Update method AND have the Insert/Update/Delete SQL command properties set in the tableadapter.

    To be especially safe if your paranoid about that you might meet both of these criteria.  Then create a dataview from the dataset and work with the

    dim dv as new dataview = dataset.tables(0).defaultview

    And to get this data in the datagridview you simply need to set the datasource on the datagridview to the datatable or dataview that you are wanting to display in the grid.

    datagridview1.datasource = dv

     

     

    Tuesday, June 20, 2006 3:34 PM
  • Untill so far I have managed to get the following, Creating a copy of the datatable, adding a Column "totaltime" and counting how many rows there are. I did it with the following code:
     
     

    Dim copydatatable As DataTable = Me.Stinis.T_TimeRegistration1.Copy()

    copydatatable.Columns.Add("Totaltime", Type.GetType("System.String"))

    Dim teller As Integer

    teller = copydatatable.Rows.Count

     
     
    full name
    id nr description starttime endtime totaltime
    name1 153      construction 26820 0
    name2 181      construction 27600 28140
    name2 181      construction 28140 0

     

    How do I go further??

    1. Adding/changing  data in datatable construction should be repair

    2. Replace the 0 in the column endtime with the current time (tijdconversie)

    3. putting in the column totaltime the result of the function (endtime-starttime)

     

     we cannot change the laws of physics

    Thursday, June 22, 2006 2:35 PM
  • I came up with the following solution after 3 days of seeking for answers, see blue text

    SELECT     dbo.T_TimeRegistration.EmpId, dbo.T_Employee.FirstName + " " + dbo.T_Employee.Name AS "Full name", dbo.T_MachGrp.Description,
                          dbo.T_TimeRegistration.ShopDocCode, CASE dbo.T_TimeRegistration.EndTime WHEN 0 THEN ((DATEPART(hh, GETDATE()) * 3600) + (DATEPART(mi,
                          GETDATE()) * 60) - dbo.T_TimeRegistration.StartTime) / 3600 ELSE ((dbo.T_TimeRegistration.EndTime - dbo.T_TimeRegistration.StartTime) / 3600)
                          END AS Totaltime
    FROM         dbo.T_DossierMain, dbo.T_ProductionHeader, dbo.T_ProdBillOfOper, dbo.T_MachGrp, dbo.T_ShopDoc, dbo.T_TimeRegistration,
                          dbo.T_Employee
    WHERE     dbo.T_DossierMain.DossierCode = dbo.T_ProductionHeader.DossierCode AND
                          dbo.T_ProductionHeader.ProdHeaderDossierCode = dbo.T_ProdBillOfOper.ProdHeaderDossierCode AND
                          dbo.T_ProdBillOfOper.MachGrpCode = dbo.T_MachGrp.MachGrpCode AND
                          dbo.T_ProdBillOfOper.ProdHeaderDossierCode = dbo.T_ShopDoc.ProdHeaderDossierCode AND
                          dbo.T_ProdBillOfOper.ProdBOOLineNr = dbo.T_ShopDoc.ProdBOOLineNr AND
                          dbo.T_ShopDoc.ShopDocCode = dbo.T_TimeRegistration.ShopDocCode AND dbo.T_TimeRegistration.EmpId = dbo.T_Employee.EmpId
    GROUP BY dbo.T_TimeRegistration.EmpId, dbo.T_Employee.FirstName, dbo.T_TimeRegistration.ShopDocCode, dbo.T_TimeRegistration.HourCode,
                          dbo.T_TimeRegistration.RegDate, dbo.T_MachGrp.Description, dbo.T_Employee.Name, dbo.T_TimeRegistration.StartTime,
                          dbo.T_TimeRegistration.EndTime, dbo.T_DossierMain.OrdNr
    HAVING      (dbo.T_TimeRegistration.HourCode = 'PR') AND (dbo.T_TimeRegistration.RegDate = ?) AND (dbo.T_DossierMain.OrdNr = ?)
    ORDER BY dbo.T_TimeRegistration.EmpId, dbo.T_TimeRegistration.ShopDocCode

    Monday, June 26, 2006 6:37 AM