|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Querying a database using vbThe 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 Show quoteHide quote "onecorp" <onecorp@community.nospam> wrote in message news:1DE2BF15-C469-4695-8DFE-E938339DBAC5@microsoft.com... >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 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 Show quoteHide quote "PGC" wrote: > 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" <onecorp@community.nospam> wrote in message > news:1DE2BF15-C469-4695-8DFE-E938339DBAC5@microsoft.com... > >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 > > > 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 Show quoteHide quote "onecorp" wrote: > 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" wrote: > > > 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" <onecorp@community.nospam> wrote in message > > news:1DE2BF15-C469-4695-8DFE-E938339DBAC5@microsoft.com... > > >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 > > > > > > 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 Show quoteHide quote "onecorp" <onecorp@community.nospam> wrote in message news:58EADE93-BA42-4793-AF72-0B17B69D74A4@microsoft.com... > > > 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" wrote: > >> 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" wrote: >> >> > 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" <onecorp@community.nospam> wrote in message >> > news:1DE2BF15-C469-4695-8DFE-E938339DBAC5@microsoft.com... >> > >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 >> > >> > >> > 
Other interesting topics
|
|||||||||||||||||||||||
