Solved

Creating an asp script or function to count duplicate string variables

Posted on 2009-07-10
5
233 Views
Last Modified: 2012-05-07
Hello, I need help on creating an asp script or function to count duplicate string variables and match them to a table variable for that record. I believe I should use the SQL COUNT function but not sure how to implement it! Example below&

I have a string: (1,2,1,5,5,3,3,3,7,4,6,6,3,3,3) these numbers representing record IDs in my SQL DB. I need to pull the records and count how many times its placed in the string and match that number to a table variable to do something with that record. For instants 3 is shown 6 time, so I need to show that record and check that string count agents a table of that record.

Help on this would be greatly appreciated..
0
Comment
Question by:inrworx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:rg20
ID: 24825055
something like this?

select count(*) from table group by recordID
0
 

Author Comment

by:inrworx
ID: 24827376
Hello rg20, yes i can use this in the SQL statement, but how do i count up the number in the string first to match it to the records table value?

thanks..
0
 

Author Comment

by:inrworx
ID: 24827459
To add to my last comment, I only have a single record in the DB say record id 3. so Im not really grouping in the SQL statement, I think. Im only trying to pull that single record and match the string count to a value in a table from that record. Hope that helps a bit&
0
 
LVL 9

Accepted Solution

by:
rg20 earned 500 total points
ID: 24827768
For instants 3 is shown 6 time, so I need to show that record and check that string count agents a table of that record

what you really are asking is
You have a counter for a record that counts how many times a record appears.  you then have a string (as above) in a datafield that you need to query that record.  I think

for example
record ID      counter  
2                     2        
3                     3

table 2
string
1,2,3,1,2,3,3

That would lead me to something like this
select string from table 2
you put this in ASP so I will use some of that here
dim arry()
Dim d
Set d=Server.CreateObject("Scripting.Dictionary")

arry = split(rst("string"),",")
'cycle through the array
for x = 0 to ubound(arry)
http://webcheatsheet.com/asp/dictionary_object.php
use a dictionary here to enter the string elements as the key and increment the counter if the key exists
something like
if key exists
    d.item(arry(x)) = d.item(array(x)) + 1
else
    d.add(arry(x),1)
end if
next
once done, you can get the sums in a string
myItem = allItems(i) 'This is the item value
myitem2 = join(myitem,",")
myitem 2 will be part of the where clause

select recordID from table1 where counter in (myitem2)
0
 

Author Comment

by:inrworx
ID: 24828078
humm, can i do something like this, tried to do it within the record set:

while not rs.eof
      arry = split(session("track_batchVals"),",")
      boxcount=0
      tracknumid=rs("f_tracknum")'from the record set
      for x = 0 to ubound(arry)
      if tracknumid=x then boxcount=boxcount+1
      next
rs.movenext
wend
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question