Dataset Help

i have a datat with a table called "report"
i aslo have 12 other tables named after the months of the finacial year starting with "may" and ending in "April" i need to add the second column for each of the 12 month tables to the "report"

this is the code i'm using...

VB Code:

For Each drtable In dsMonth.Tables
dsReport.Tables("Report").Columns.Add(drtable.Columns(1))
next

when it runs it errors saying "Column 'June' already belongs to another DataTable."


Can anyone help me as this is a very important report.

What you are trying to do is to add the "exact" same column to another table, that is the framework thinks you are trying to make one object to be part of 2 tables. You have to make a copy of the column in a new object and add that.

/Nisse

how do i do that any ideas??????

i'm at a loss

The way to do this with objects in .NET is to use the clone method, unfortunately the datacolumn object does not implement the IClonable interface. So the object oriented way to solve this would be to create a derived datacolumn class that implement that interface.

This is tough though, and I guess you wouldn't want to go with that.

An other way to do it is to make a "property-copy" function then create a new column object and use the function to copy all properties from the original column to the new. And then add the new column.

/Nisse

any chance you could show me some code to do that as i 'm still new to .net and programming.

thanks in advance

Code:

---

    Private Function PropertyCopier(ByVal oldColumn As DataColumn) As DataColumn
        Dim newColumn As New DataColumn
        newColumn.AllowDBNull = oldColumn.AllowDBNull
        newColumn.AutoIncrement = oldColumn.AutoIncrement
        '.
        '.
        '.
        Return newColumn
    End Function

---

/Nisse

i take it i need to do that for each property in the column will this bring all the data across

You will have to do this for every property that you have set to anything different than the default value. Properties set to default value get the same value and properties not set will not be of any importance offcourse.

The second question if I understand you correctly will turn this question into something totally different though. A datacolumn in a datatable does as a column in SQL NOT contain any data itself.

What we have discussed so far is how to copy the column as a column object.

A database tabla and also a .NET datatable stores data in rows, NOT column, so if it is the data you want to copy we are talking about different matters.

/Nisse

ok i think i need to explain what i need to do in full this might help


i'm working on a program that has a report section

one of the reports needs to list all the types down one side and the finanical year (may - April) along the top.

against each month i want a list of the number of each type.


e.g.

Type of Claim May June July August
RTA 1 3 10 0
Slip 7 5 9 20

and so on.....

i have tried a sql statement to do this but it either doesn't work or i brings back the wrong data. i will post the sql query to see if you know if i'm doing something wrong.


i had a tought to query the database 1 per month then add all the data into a table in a dataset then i could print it off.


can this be done and what would the best way be..

I take it that it is a cross-tab report you want to do then:

Taken from books online:
Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data.

Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:

Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4

A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:

Year 1990 1991
Q1 1.1 2.1
Q2 1.2 2.2
Q3 1.3 2.3
Q4 1.4 2.4

These are the statements used to create the Pivot table and populate it with the data from the first table:

USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

This is the SELECT statement used to create the rotated results:

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.

If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO

Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.

/Nisse