473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Remove items from GetRows array

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
Jul 19 '05 #1
2 2440
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"
Jul 19 '05 #2
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" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@TK2MSFTNGP12.phx.gbl...
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"

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Croney69 | last post by:
I am getting information out of a table to place into to an Array. rs=DataConn.Execute(strSQL) At this point I place it into the array objArray=rs.GetRows() But how do I handle things if...
8
by: Moshe | last post by:
I'm facing an odd behavior in using the GetRows Method. I'm not sure what's causing it because it has been working fine until now. I have a sproc that returns 1 row. I display the RS info on the...
11
by: Crane Linkledder | last post by:
What is the difference, and when to use one over the other? Thanks
12
by: Sam Collett | last post by:
How do I remove an item with a specified value from an array? i.e. array values 1,2,2,5,7,12,15,21 remove 2 from array would return 1,5,7,12,15,21 (12 and 21 are NOT removed, duplicates are...
11
by: Laphan | last post by:
Hi All I'm using .getRows() with a local var array instead of doing a recursive loop so that I'm being a good ASP newvbie and closing my object i/o's (the recordset in this case) as quick as...
9
by: bajopalabra | last post by:
hi session("myVar") = rs.getRows( ) don't work when number of records is greater than 10 does anybody know WHY ??? is it a Session object limitation ??? thanks
10
by: Hank | last post by:
Hello, I'm having trouble reading from a table directly into an array using Access 2000. Dim db As Database Dim rsTime As Recordset Dim TimeArray As Variant Set db = CurrentDb Set rsTime =...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
3
ADezii
by: ADezii | last post by:
Last Tip, we demonstrated the technique for retrieving data from a DAO Recordset, and placing it into a 2-dimensional Array using the GetRows() Method. This week, we will cover the same exact Method...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.