Solved

Creating an asp script or function to count duplicate string variables

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

728 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