Solved

Creating an asp script or function to count duplicate string variables

Posted on 2009-07-10
5
226 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now