Arrays and SQL Server table data

[onecorp]

Scenario one:
I have a variety of sql tables. The number of columns varys from 27 to 59. The columns headings are numbers, commencing with 1 and incremented by 1. (and the rows are dates)
The data in the columns is either 1 or null .
I have built a simplistic stored procedure that 'pairs' the columns when there is a '1' in each
ie It returns a count of 57 for column one, because there are 57 1's in that column
it reurns 15 as a count of column 1 and 2 (because there are 15 1's in each column on the same date)
it returns 11 as a count of column 1 and 3.
It does this for all 470 , or so, results.
I have tried to bind this to a datagrid but just get one huge line of results.
The results should fill a triangular shaped (jagged) array, but I have not been able to get the data into the array and then the array into a table.
Can anyone assist, please? Any suggestions at all?

Scenario 2:
Same sql tables as for scenario one. However, from the stored procedure, I have placed the results back into a sql table which I have then bound to a datagrid.
In order to create the jagged nature of the table, I have had to use 'null' values to blank out the boxes as needs be.
I have also had to apply a sort (by descending) to the datagrid as the stored procedure is not populating the SQL table in the order I expected.

Ideally, all I want to do, is to directly access the my original data table / iterate through each column/pair of columns to obtain my results set / fill a jagged array with the results and use the array to create a corresponding table.

Perhaps something along the following lines (which of course, doesn't work....).
Any replies in Visual Basic would be appreciated....

'Public Sub ColumnList(ByVal MyTestTable As Data.DataTable)

' Dim myConnection As New SqlConnection()

' myConnection.ConnectionString = "Data Source=.\MySQLServer;AttachDbFilename=|DataDirectory|\XXXXXXX.mdf;Integrated Security=YYYYYYY;User Instance=ZZZZZZ"


' Dim myColumns As Data.DataColumnCollection = MyTestTable.Columns
' Dim column As Data.DataColumn
' Dim row As Data.DataRow
' Dim r As Integer
' For Each row In MyTestTable.Rows
' For Each column In MyTestTable.Columns
' r = "COUNT(column(row) + column(row)) AS Expr"
' Next
' Next



' Dim table1()() As Integer = New Integer(29)() {}
' Dim myColumnHeaders As Integer
' 'Dim r As Integer
' For myColumnHeaders = 0 To 29
' table1(myColumnHeaders) = New Integer(r + 1) {}
' Next myColumnHeaders
'End Sub

Where XXXXXXXX represents the name of my database and MyTestTable represents the table being queried.

Can anyone help? Thank you.

 

[fpineda101]

I am assuming you are coming from VB6

Datatype Variant, as known in VB6 does not exist in VB.NET. So you CANNOT make an array of variants as you would in VB6/Classic ADO. The DataSet is the way to go.

Check out this link. It has everything you need to know about DataSets
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclasstopic.asp

Hope this helps

 

[onecorp]

The stored procedure is listed hereunder.

Perhaps someone can at least point me in the right direction to reduce the following stored procedure to a more manageable level of code ?

Thanks.

ALTER PROCEDURE ZZZZZZZ
/*
(
@IdNumber + 1
)
*/
AS
Begin
/* SET NOCOUNT ON */

SELECT COUNT([1]) AS Expr1, COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1] + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5, COUNT([1] + [6])
AS Expr6, COUNT([1] + [7]) AS Expr7, COUNT([1] + [8]) AS Expr8, COUNT([1] + [9]) AS Expr9, COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
COUNT([1] + [12]) AS Expr12, COUNT([1] + [13]) AS Expr13, COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15, COUNT([1] + [16]) AS Expr16,
COUNT([1] + [17]) AS Expr17, COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19, COUNT([1] + [20]) AS Expr20, COUNT([1] + [21]) AS Expr21,
COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23, COUNT([1] + [24]) AS Expr24, COUNT([1] + [25]) AS Expr25, COUNT([1] + [26]) AS Expr26,
COUNT([1] + [27]) AS Expr27, COUNT([1] + [28]) AS Expr28, COUNT([1] + [29]) AS Expr29, COUNT([1] + [30]) AS Expr30
INTO Table1
FROM XXXXXXXTable;


INSERT INTO Table1
SELECT NULL AS Expr31, COUNT([2] + [2]) AS Expr32, COUNT([2] + [3]) AS Expr33, COUNT([2] + [4]) AS Expr34, COUNT([2] + [5]) AS Expr35, COUNT([2] + [6])
AS Expr36, COUNT([2] + [7]) AS Expr37, COUNT([2] + [8]) AS Expr38, COUNT([2] + [9]) AS Expr39, COUNT([2] + [10]) AS Expr40, COUNT([2] + [11])
AS Expr41, COUNT([2] + [12]) AS Expr42, COUNT([2] + [13]) AS Expr43, COUNT([2] + [14]) AS Expr44, COUNT([2] + [15]) AS Expr45, COUNT([2] + [16])
AS Expr46, COUNT([2] + [17]) AS Expr47, COUNT([2] + [18]) AS Expr48, COUNT([2] + [19]) AS Expr49, COUNT([2] + [20]) AS Expr50, COUNT([2] + [21])
AS Expr51, COUNT([2] + [22]) AS Expr52, COUNT([2] + [23]) AS Expr53, COUNT([2] + [24]) AS Expr54, COUNT([2] + [25]) AS Expr55, COUNT([2] + [26])
AS Expr56, COUNT([2] + [27]) AS Expr57, COUNT([2] + [28]) AS Expr58, COUNT([2] + [29]) AS Expr59, COUNT([2] + [30]) AS Expr60
FROM XXXXXXXTable;
.
.
.
.
.

INSERT INTO Table1
SELECT NULL AS Expr461, NULL AS Expr462, COUNT([49] + [49]) etc