Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Remove items from GetRows array

Reply
Thread Tools

Remove items from GetRows array

 
 
Patrick G.
Guest
Posts: n/a
 
      02-27-2004
Greetings all:

ASP VB, SQL Svr 2000

I am pulling data from 3 tables.

table1 holds item details
table2 holds publication types and the item id from table1
table3 holds category types and the item id from table1

when I create a view on these tables joined together I end up getting
multiple rows for table1 items when they belong to more than 1 publication
or category.
What I am trying to do is find those "duplicate" rows and create a string or
the publication types and category types to essentially create 1 row... I
researched doing this with some T-SQL but no method seemed to be accurate
and quick on performance.

Looking for results like so:

ItemId ItemTitle ItemDescript ItemCategories
ItemPublicationtypes
1 2 This item1 cat1, cat2, cat3 pub1,
pub2

With my view I get 5 rows hence multiple search results when its really the
same record.

With my ASP I get an array that is populated by using the GetRows method of
a recordset.

I'm looping through the array to find duplicate records in the data I
pulled... when I find a duplicate id I then start another loop to go through
and pull out a column value to concatenate with the first duplicate's column
value...

What I am then left with is a useless item in the array... how do I then
remove that "row" and then redim the array after I'm done checking for dups
and creating my concatenated strings??

here's the code:

For iCounter = 0 To iLibResultsCount
If LibCountDups(arrLibResults(0, iCounter)) = True Then '** Use a
function to get the count of like ID values
For iSubCounter = 0 To iLibResultsCount
If arrLibResults(0, iCounter) = arrLibResults(0, iSubCounter) Then
If arrLibResults(9, iCounter) <> arrLibResults(9, iSubCounter) Then
strCatList = strCatList + ", " & arrLibResults(9, iSubCounter)
arrLibResults(0, iSubCounter) = ""
arrLibResults(1, iSubCounter) = ""
arrLibResults(2, iSubCounter) = ""
arrLibResults(3, iSubCounter) = ""
arrLibResults(4, iSubCounter) = ""
arrLibResults(5, iSubCounter) = ""
arrLibResults(6, iSubCounter) = ""
arrLibResults(7, iSubCounter) = ""
arrLibResults(8, iSubCounter) = ""
arrLibResults(9, iSubCounter) = ""
arrLibResults(10, iSubCounter) = ""
arrLibResults(11, iSubCounter) = ""
End If

If arrLibResults(11, iCounter) <> arrLibResults(11, iSubCounter) Then
strPubList = strPubList + ", " & arrLibResults(11, iSubCounter)
arrLibResults(0, iSubCounter) = ""
arrLibResults(1, iSubCounter) = ""
arrLibResults(2, iSubCounter) = ""
arrLibResults(3, iSubCounter) = ""
arrLibResults(4, iSubCounter) = ""
arrLibResults(5, iSubCounter) = ""
arrLibResults(6, iSubCounter) = ""
arrLibResults(7, iSubCounter) = ""
arrLibResults(8, iSubCounter) = ""
arrLibResults(9, iSubCounter) = ""
arrLibResults(10, iSubCounter) = ""
arrLibResults(11, iSubCounter) = ""
End If
End If
Next
arrLibResults(9, iCounter) = arrLibResults(9, iCounter) & strCatList
arrLibResults(11, iCounter) = arrLibResults(11, iCounter) & strPubList
End If
Next


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      02-27-2004
Patrick G. wrote (some snippage has occurred):
> Greetings all:
>
> ASP VB, SQL Svr 2000
>
> I am pulling data from 3 tables.
>
> table1 holds item details
> table2 holds publication types and the item id from table1
> table3 holds category types and the item id from table1
>


> Looking for results like so:
>
> ItemId ItemTitle ItemDescript ItemCategories
> ItemPublicationtypes
> 1 2 This item1 cat1, cat2, cat3
> pub1, pub2
>
> With my view I get 5 rows hence multiple search results when its
> really the same record.
>
> With my ASP I get an array that is populated by using the GetRows
> method of a recordset.
>
> I'm looping through the array to find duplicate records in the data I
> pulled... when I find a duplicate id I then start another loop to go
> through and pull out a column value to concatenate with the first
> duplicate's column value...
>
> What I am then left with is a useless item in the array... how do I
> then remove that "row" and then redim the array after I'm done
> checking for dups and creating my concatenated strings??
>

Don't bother. read the results into a new array that's been created with the
correct dimensions.

However, you may wish to try something like this:

Create two user-defined functions in SQL Server with these definitions:

Create Function dbo.ConcatCats (@id int)
Returns varchar(200)
AS
DECLARE @str varchar(200)
Set @str=''
Select @str = CASE @str WHEN '' THEN ItemCategories
ELSE @str + ', ' + ItemCategories END
FROM table3
WHERE ItemID = @id
Return @str

Create a similar function for ItemPublicationtypes. Then:

Select ItemID, Itemtitle, ItemDescript,
dbo.ConcatCats(ItemID), dbo.ConcatPubs(ItemID)
FROM table1

The technique used in the function has been referred to as "aggregate
concatenation"; but you should know that MS disavows the use of this
technique, saying that this behavior is undefined:
http://support.microsoft.com/default...;EN-US;q287515

Bottom line: I have used this technique with no ill effects, but do not try
to effect the order in which the values are concatenated to @str. Using an
ORDER BY clause will yield unpredictable results. If you need the items in a
particular order, go back to your array loop solution.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
 
Patrick G.
Guest
Posts: n/a
 
      02-27-2004
Bob:

Thank you very much for your input. I can't believe I've gotten to this
point in the process without thinking of simply putting the items I want to
keep into another array.

I read another post regarding the Function you highlighted, but I do require
ORDER BY in my sql.

Thanks again.
Patrick

"Bob Barrows" <(E-Mail Removed)> wrote in message
news:OYneNnS$(E-Mail Removed)...
> Patrick G. wrote (some snippage has occurred):
> > Greetings all:
> >
> > ASP VB, SQL Svr 2000
> >
> > I am pulling data from 3 tables.
> >
> > table1 holds item details
> > table2 holds publication types and the item id from table1
> > table3 holds category types and the item id from table1
> >

>
> > Looking for results like so:
> >
> > ItemId ItemTitle ItemDescript ItemCategories
> > ItemPublicationtypes
> > 1 2 This item1 cat1, cat2, cat3
> > pub1, pub2
> >
> > With my view I get 5 rows hence multiple search results when its
> > really the same record.
> >
> > With my ASP I get an array that is populated by using the GetRows
> > method of a recordset.
> >
> > I'm looping through the array to find duplicate records in the data I
> > pulled... when I find a duplicate id I then start another loop to go
> > through and pull out a column value to concatenate with the first
> > duplicate's column value...
> >
> > What I am then left with is a useless item in the array... how do I
> > then remove that "row" and then redim the array after I'm done
> > checking for dups and creating my concatenated strings??
> >

> Don't bother. read the results into a new array that's been created with

the
> correct dimensions.
>
> However, you may wish to try something like this:
>
> Create two user-defined functions in SQL Server with these definitions:
>
> Create Function dbo.ConcatCats (@id int)
> Returns varchar(200)
> AS
> DECLARE @str varchar(200)
> Set @str=''
> Select @str = CASE @str WHEN '' THEN ItemCategories
> ELSE @str + ', ' + ItemCategories END
> FROM table3
> WHERE ItemID = @id
> Return @str
>
> Create a similar function for ItemPublicationtypes. Then:
>
> Select ItemID, Itemtitle, ItemDescript,
> dbo.ConcatCats(ItemID), dbo.ConcatPubs(ItemID)
> FROM table1
>
> The technique used in the function has been referred to as "aggregate
> concatenation"; but you should know that MS disavows the use of this
> technique, saying that this behavior is undefined:
> http://support.microsoft.com/default...;EN-US;q287515
>
> Bottom line: I have used this technique with no ill effects, but do not

try
> to effect the order in which the values are concatenated to @str. Using an
> ORDER BY clause will yield unpredictable results. If you need the items in

a
> particular order, go back to your array loop solution.
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting a correctly formatted table from a GetRows array Laphan ASP General 0 11-29-2005 10:23 PM
How to remove items from add/remove list please Caractucus Potts Computer Support 5 07-03-2005 10:31 PM
IsArray doesn't work with array var populated with xxx.GetRows() Laphan ASP General 11 11-22-2004 10:33 AM
GetRows Mystery Moshe ASP General 8 08-28-2003 08:05 PM
Using GetRows() Croney69 ASP General 5 07-28-2003 08:50 PM



Advertisments