Querying a database using vb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are labelled
[1],[2]...[30].
The numerical columns have a tinyint type and the data stored is either 1 or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:

eg count the number of times 1 appears in column [1] and 1 also appears in
column[15] in the same row:

Column [1] Column[15]
1 1 Count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count
1 Don't count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count,
etc.

The following has been suggested to me as a possible solution:


Imports MWFN

Partial Class MembersPages_MyPage

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter

Dim pairs As MWFN.MWFNDataTable

pairs = pairsAdapter.GetData

Dim myColumns As Data.DataColumnCollection = pairs.Columns
Dim columnA As Data.DataColumn
Dim columnB As Data.DataColumn
Dim num3 As Hashtable

For Each columnA In myColumns

If columnA.ColumnName <> "Id" Then

For Each columnB In myColumns

If columnB.ColumnName <> "Id" And columnB.Ordinal >
columnA.Ordinal Then

'if columnB's name doesn't equal Id and columnB's
position number is over columnA's, sum columnA and columnB

num3.Add(columnA.ColumnName & "," &
columnB.ColumnName, pairs.Compute("SUM(" & columnA.ColumnName & ") + SUM( " &
columnB.ColumnName & ")", ""))

End If
Next
End If
Next
End Sub
End Class

I have used a dataset to obtain the columns from the database datatable in
an effort to use a data access layer in my project. The dataset is MWFN.
The above code produces the following:
Warning: num3 is used before it has been assigned a value.
And when the programme is run:
Expecting a single column argument with possible child qualifier.

At this point in time , the code only sums/counts two columns. I will wish
to broaden that to sum/count 3 , 4 and possibly 5 columns, so the code needs
to be extensible. The number of columns will alos vary (probably from 27 to
59).

It doesn't matter if your suggestion is to use transact sql is a stored
procedure before the data gets to the project, or use vb as per the above
mentioned suggestion.
Any assistance would be appreciated.
Thank you

onecorp


Can anyone help point me in the right direction
 
Hi onecorp,

I don't get this. Are you saying that the datatable can have a variable
number of columns? Can you provide the table definition? If the number of
columns is going to change when you go looking for the data then maybe they
should be rows in an other table instead.

PGC

onecorp said:
I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are labelled
[1],[2]...[30].
The numerical columns have a tinyint type and the data stored is either 1
or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:

eg count the number of times 1 appears in column [1] and 1 also appears in
column[15] in the same row:

Column [1] Column[15]
1 1 Count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count
1 Don't count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count,
etc.

The following has been suggested to me as a possible solution:


Imports MWFN

Partial Class MembersPages_MyPage

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter

Dim pairs As MWFN.MWFNDataTable

pairs = pairsAdapter.GetData

Dim myColumns As Data.DataColumnCollection = pairs.Columns
Dim columnA As Data.DataColumn
Dim columnB As Data.DataColumn
Dim num3 As Hashtable

For Each columnA In myColumns

If columnA.ColumnName <> "Id" Then

For Each columnB In myColumns

If columnB.ColumnName <> "Id" And columnB.Ordinal >
columnA.Ordinal Then

'if columnB's name doesn't equal Id and columnB's
position number is over columnA's, sum columnA and columnB

num3.Add(columnA.ColumnName & "," &
columnB.ColumnName, pairs.Compute("SUM(" & columnA.ColumnName & ") +
SUM( " &
columnB.ColumnName & ")", ""))

End If
Next
End If
Next
End Sub
End Class

I have used a dataset to obtain the columns from the database datatable in
an effort to use a data access layer in my project. The dataset is MWFN.
The above code produces the following:
Warning: num3 is used before it has been assigned a value.
And when the programme is run:
Expecting a single column argument with possible child qualifier.

At this point in time , the code only sums/counts two columns. I will wish
to broaden that to sum/count 3 , 4 and possibly 5 columns, so the code
needs
to be extensible. The number of columns will alos vary (probably from 27
to
59).

It doesn't matter if your suggestion is to use transact sql is a stored
procedure before the data gets to the project, or use vb as per the above
mentioned suggestion.
Any assistance would be appreciated.
Thank you

onecorp


Can anyone help point me in the right direction
Click to expand...
 
Hi PGC,

Thank you for your reply.

No the datatable cannot have a variable number of columns. There are a
number of different datatables. The simplest has 27 columns, the most complex
has 59 columns.

Regards

Onecorp

PGC said:
Hi onecorp,

I don't get this. Are you saying that the datatable can have a variable
number of columns? Can you provide the table definition? If the number of
columns is going to change when you go looking for the data then maybe they
should be rows in an other table instead.

PGC

onecorp said:
I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are labelled
[1],[2]...[30].
The numerical columns have a tinyint type and the data stored is either 1
or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:

eg count the number of times 1 appears in column [1] and 1 also appears in
column[15] in the same row:

Column [1] Column[15]
1 1 Count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count
1 Don't count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count,
etc.

The following has been suggested to me as a possible solution:


Imports MWFN

Partial Class MembersPages_MyPage

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter

Dim pairs As MWFN.MWFNDataTable

pairs = pairsAdapter.GetData

Dim myColumns As Data.DataColumnCollection = pairs.Columns
Dim columnA As Data.DataColumn
Dim columnB As Data.DataColumn
Dim num3 As Hashtable

For Each columnA In myColumns

If columnA.ColumnName <> "Id" Then

For Each columnB In myColumns

If columnB.ColumnName <> "Id" And columnB.Ordinal >
columnA.Ordinal Then

'if columnB's name doesn't equal Id and columnB's
position number is over columnA's, sum columnA and columnB

num3.Add(columnA.ColumnName & "," &
columnB.ColumnName, pairs.Compute("SUM(" & columnA.ColumnName & ") +
SUM( " &
columnB.ColumnName & ")", ""))

End If
Next
End If
Next
End Sub
End Class

I have used a dataset to obtain the columns from the database datatable in
an effort to use a data access layer in my project. The dataset is MWFN.
The above code produces the following:
Warning: num3 is used before it has been assigned a value.
And when the programme is run:
Expecting a single column argument with possible child qualifier.

At this point in time , the code only sums/counts two columns. I will wish
to broaden that to sum/count 3 , 4 and possibly 5 columns, so the code
needs
to be extensible. The number of columns will alos vary (probably from 27
to
59).

It doesn't matter if your suggestion is to use transact sql is a stored
procedure before the data gets to the project, or use vb as per the above
mentioned suggestion.
Any assistance would be appreciated.
Thank you

onecorp


Can anyone help point me in the right direction
Click to expand...
Click to expand...
 
I have run a sql suggestion and had the following queries,:

A)
(I made one small adjustment to your first suggestion in order to exclude
the Id column from the query, as follows:

If @Cols <> 'Id'
Set @qry='Select Count(*) From SPF Where (' + @qry + ')>1'

--Print @qry
Execute(@qry)
)
I executed the query on a table of 30 columns (adjusted the count as
necessary) using columns labelled 1, 2, and 3 . It returned a value of 34,
however, the answer should have been 3. Three is the number of times that one
simultaneously appears in each of the aforementioned columns ie . I only
wish to count the number or times that '1' appears in the nominted columns
simultaneously, whether I am checking two columns at a time or three columns
( or even 4 columns....which is why I thought that the query should be built
in managed code(VB .net) using SQL CLR ?????).

B)When I tried to use a table with 38 columns , I received the following
error:

Msg 217, Level 16, State 1, Procedure spMyTest2, Line 45
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).

even after ensuring that I adjusted the count correctly....

C) This procedure appears to only return one value based on inputting the
paramters as indicated. For a table that contains 38 columns, there should be
8436 values. How do I return a list of all the combinations, not just one?

......Hence I thought managed code SQL CLR should be used. Does anyone have
any suggestions please ?

Any assistance would be appreciated.
Thank you
Onecorp

onecorp said:
Hi PGC,

Thank you for your reply.

No the datatable cannot have a variable number of columns. There are a
number of different datatables. The simplest has 27 columns, the most complex
has 59 columns.

Regards

Onecorp

PGC said:
Hi onecorp,

I don't get this. Are you saying that the datatable can have a variable
number of columns? Can you provide the table definition? If the number of
columns is going to change when you go looking for the data then maybe they
should be rows in an other table instead.

PGC

onecorp said:
I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are labelled
[1],[2]...[30].
The numerical columns have a tinyint type and the data stored is either 1
or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:

eg count the number of times 1 appears in column [1] and 1 also appears in
column[15] in the same row:

Column [1] Column[15]
1 1 Count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count
1 Don't count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count,
etc.

The following has been suggested to me as a possible solution:


Imports MWFN

Partial Class MembersPages_MyPage

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter

Dim pairs As MWFN.MWFNDataTable

pairs = pairsAdapter.GetData

Dim myColumns As Data.DataColumnCollection = pairs.Columns
Dim columnA As Data.DataColumn
Dim columnB As Data.DataColumn
Dim num3 As Hashtable

For Each columnA In myColumns

If columnA.ColumnName <> "Id" Then

For Each columnB In myColumns

If columnB.ColumnName <> "Id" And columnB.Ordinal >
columnA.Ordinal Then

'if columnB's name doesn't equal Id and columnB's
position number is over columnA's, sum columnA and columnB

num3.Add(columnA.ColumnName & "," &
columnB.ColumnName, pairs.Compute("SUM(" & columnA.ColumnName & ") +
SUM( " &
columnB.ColumnName & ")", ""))

End If
Next
End If
Next
End Sub
End Class

I have used a dataset to obtain the columns from the database datatable in
an effort to use a data access layer in my project. The dataset is MWFN.
The above code produces the following:
Warning: num3 is used before it has been assigned a value.
And when the programme is run:
Expecting a single column argument with possible child qualifier.

At this point in time , the code only sums/counts two columns. I will wish
to broaden that to sum/count 3 , 4 and possibly 5 columns, so the code
needs
to be extensible. The number of columns will alos vary (probably from 27
to
59).

It doesn't matter if your suggestion is to use transact sql is a stored
procedure before the data gets to the project, or use vb as per the above
mentioned suggestion.
Any assistance would be appreciated.
Thank you

onecorp


Can anyone help point me in the right direction
Click to expand...
Click to expand...
Click to expand...
 
There are a number of ways to solve your issue, here is one that I think
will help.

Create a temp (@MatchCounts) table for to hold values.

@MatchCounts
FieldList Varchar(255) Not Null
MatchCount Int Not Null

Loop over the combinations you want to retrieve

Insert into @MatchCount (FieldList, MatchCount)
Select "010203" as FieldList, Count(ID) as MatchCount
From SPF
Where SPF.Field1=1 and SPF.Field2 =1 and SPF.Field3 = 1

Return the @MatchCount resultset

dim sResults as string = string.empty
dim sField as string = string.empty
for each dr as datarow in ds.tables(1).rows

sResults = string.empty

for i = 1 to ctype(dr("FieldList"),string).length step 2
sField = mid(dr("FieldList"),i,2)
if sResults.length = 0 then
sResults =
string.format("Field{0}",ctytpe(ctype(sfield,integer),string))
else
sResults = string.format("{0},
Field{1}",sResults,ctytpe(ctype(sfield,integer),string))
end if
next i

console.writeline string.format("The match count for fields:{0} is
{1}",sresults,dr("MatchCount"))

next for


onecorp said:
I have run a sql suggestion and had the following queries,:

A)
(I made one small adjustment to your first suggestion in order to exclude
the Id column from the query, as follows:

If @Cols <> 'Id'
Set @qry='Select Count(*) From SPF Where (' + @qry + ')>1'

--Print @qry
Execute(@qry)
)
I executed the query on a table of 30 columns (adjusted the count as
necessary) using columns labelled 1, 2, and 3 . It returned a value of 34,
however, the answer should have been 3. Three is the number of times that
one
simultaneously appears in each of the aforementioned columns ie . I only
wish to count the number or times that '1' appears in the nominted columns
simultaneously, whether I am checking two columns at a time or three
columns
( or even 4 columns....which is why I thought that the query should be
built
in managed code(VB .net) using SQL CLR ?????).

B)When I tried to use a table with 38 columns , I received the following
error:

Msg 217, Level 16, State 1, Procedure spMyTest2, Line 45
Maximum stored procedure, function, trigger, or view nesting level
exceeded
(limit 32).

even after ensuring that I adjusted the count correctly....

C) This procedure appears to only return one value based on inputting the
paramters as indicated. For a table that contains 38 columns, there should
be
8436 values. How do I return a list of all the combinations, not just one?

.....Hence I thought managed code SQL CLR should be used. Does anyone have
any suggestions please ?

Any assistance would be appreciated.
Thank you
Onecorp

onecorp said:
Hi PGC,

Thank you for your reply.

No the datatable cannot have a variable number of columns. There are a
number of different datatables. The simplest has 27 columns, the most
complex
has 59 columns.

Regards

Onecorp

PGC said:
Hi onecorp,

I don't get this. Are you saying that the datatable can have a variable
number of columns? Can you provide the table definition? If the number
of
columns is going to change when you go looking for the data then maybe
they
should be rows in an other table instead.

PGC

I have a SQL table comprised of 31 columns.
The first column is simply an id column, the next 30 columns are
labelled
[1],[2]...[30].
The numerical columns have a tinyint type and the data stored is
either 1
or
null.
I wish to count the number of times a one appears in one column
simultaneously with another column:

eg count the number of times 1 appears in column [1] and 1 also
appears in
column[15] in the same row:

Column [1] Column[15]
1 1 Count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count
1 Don't count this
1 Don't count this
1 1 add 1 to the count
1 1 add 1 to the count,
etc.

The following has been suggested to me as a possible solution:


Imports MWFN

Partial Class MembersPages_MyPage

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim pairsAdapter As New MWFNTableAdapters.MWFNTableAdapter

Dim pairs As MWFN.MWFNDataTable

pairs = pairsAdapter.GetData

Dim myColumns As Data.DataColumnCollection = pairs.Columns
Dim columnA As Data.DataColumn
Dim columnB As Data.DataColumn
Dim num3 As Hashtable

For Each columnA In myColumns

If columnA.ColumnName <> "Id" Then

For Each columnB In myColumns

If columnB.ColumnName <> "Id" And columnB.Ordinal >
columnA.Ordinal Then

'if columnB's name doesn't equal Id and columnB's
position number is over columnA's, sum columnA and columnB

num3.Add(columnA.ColumnName & "," &
columnB.ColumnName, pairs.Compute("SUM(" & columnA.ColumnName & ") +
SUM( " &
columnB.ColumnName & ")", ""))

End If
Next
End If
Next
End Sub
End Class

I have used a dataset to obtain the columns from the database
datatable in
an effort to use a data access layer in my project. The dataset is
MWFN.
The above code produces the following:
Warning: num3 is used before it has been assigned a value.
And when the programme is run:
Expecting a single column argument with possible child qualifier.

At this point in time , the code only sums/counts two columns. I will
wish
to broaden that to sum/count 3 , 4 and possibly 5 columns, so the
code
needs
to be extensible. The number of columns will alos vary (probably from
27
to
59).

It doesn't matter if your suggestion is to use transact sql is a
stored
procedure before the data gets to the project, or use vb as per the
above
mentioned suggestion.
Any assistance would be appreciated.
Thank you

onecorp


Can anyone help point me in the right direction
Click to expand...
Click to expand...
Click to expand...
 
Back
Top