?
Solved

Problem with Instr

Posted on 2009-05-13
17
Medium Priority
?
277 Views
Last Modified: 2012-05-06
Hello, Experts.

I'm using "if Instr(var1, var2)", to check if var2 it exists in var1
var1 = its a group of unique numbers separated by commas (Ex: 1, 3, 9, 21, 45, 53)
var2 = a simple number (the primary key of a table)

The problem is: when the var2 has an unique char as 2, for example, and the var1 is compose by 21, 22, 23... 29, it shows as if 2 was in the string, but in fact it is not.

Is there any way to compare exactly the numbers? How can I solve this problem?

Many thanks,
Drix
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td class="menu_admin">&nbsp;</td>
    <td class="menu_admin"><span style="font-weight: bold">Data</span></td>
    <td class="menu_admin"><span style="font-weight: bold">Titulo</span></td>
  </tr>
  <% 
     comando = "SELECT * FROM tbl_noticias ORDER by id_noticia ASC"
     call abrers(comando, "listar", RSNews)
     
     comando = "SELECT conteudo FROM tbl_Emails"
     call abrers(comando, "listar", RSCont)
     ' conteudo retuns numbers separated by commas. Example: 1, 3, 9, 27, 48
     
     DO WHILE NOT RSCont.EOF
         conteudo = conteudo & RSCont("conteudo") & ", "
     RSCont.MoveNext
     loop
  %>
  <%
    DO WHILE NOT RSNews.EOF
    id_new = RSNews("id_noticia")
  %>
  <tr <%If Instr(conteudo,id_new) Then Response.Write " bgcolor='ffffff'"%>>
    <td nowrap><%=id_new%> - <input name="conteudo" type="checkbox" id="checkbox" value="<%=id_new%>" <%if Instr(conteudo,id_new) Then Response.Write " checked"%>></td>
    <td <%If Instr(conteudo,id_new) Then Response.Write " class='num_pos'"%>><%=RSNews("dt_noticia")%>&nbsp;</td>
    <td <%If Instr(conteudo,id_new) Then Response.Write " class='num_pos'"%>><%=RSNews("titulo")%>&nbsp;</td>
  </tr>
<%
RSNews.MoveNext
loop
%>
</table>

Open in new window

0
Comment
Question by:drix76
[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
  • 7
  • 6
  • 4
17 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 24380842
append a comma to both and do the instr

instr(var1+',' , var2+',')
0
 

Author Comment

by:drix76
ID: 24380958
Thank you for the fast answer.

It worked, but now there's another issue:

When I put the - &"," - now whe I have in var conteudo a number as 32 or 42 it still showing checked the number 2.

Please, help!!!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24381053
same idea, put a comma in front of it

instr(',' & var1 &',' , ',' & var2 & ',')

this assumes there is no whitespace though.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 8

Expert Comment

by:Bobaran98
ID: 24381086
I suggest converting your string to an array instead, then looping through the values to see if any of them match.  I've created a function for you below called inArray that does this loop.

Notes about my code:
  • before I use Split() to break your string into array elements, I do a replace that takes all spaces and replaces them with nothing
  • for the search term, I add an empty string to your id_new variable... that's because the elements of your conteudoArr() are by default strings and won't match against integers; by adding the empty string to the search term, I force it to be a string
Hope this helps!


'declare function that will search through array looking for value:
 
Function inArray(ByRef arrName() As String, ByVal srchTerm As String)
	
	For i as Integer = 0 To Ubound(arrName)
		If arrName(i) = srchTerm Then
			inArray = True
			Exit Function
		End If
	Next
 
	inArray = False
 
End Function
 
 
 
'later on in your code:
 
Dim conteudoArr() As String
conteudoArr = Split(Replace(conteudo," ",""),",")
 
 
'now you just call our inArray() function as part of your conditional:
If inArray(conteudo, id_new & "") Then
 
	'stuff
	'more stuff
	'etc...
 
End If

Open in new window

0
 

Author Comment

by:drix76
ID: 24381110
Thanks for your replay, but it retunrs an erro

Tipo de erro:
Erro de compilação do Microsoft VBScript (0x800A03EE)
')' esperado
/esbr/admin/pop_emails.asp, line 67, column 33
Function inArray(ByRef arrName() As String, ByVal srchTerm As String)
--------------------------------^
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24381152
Hmm... I didn't have a chance to test this, just going by memory.  Try replacing the function declaration with either:

Function inArray(ByRef arrName As String, ByVal srchTerm As String)
or

Function inArray(ByRef arrName As String(), ByVal srchTerm As String)

or if all else fails:

Function inArray(arrName, srchTerm)

Sorry I can't be more definitive at the moment! :-)

0
 

Author Comment

by:drix76
ID: 24381200
It dindt work...
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24381548
how about...


if ubound(filter(split(replace(replace(searchstr," ","")+",",",","-,"),","),teststr+"-")) = -1 then document.write("teststr not found in searchstr")

you could wrap the above condition in your own function if you wanted as suggested above
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24381550
if the ubound(....) returns something other than -1 then the teststr was found
0
 

Author Comment

by:drix76
ID: 24381881
I think I'm very newbee for your answers. I apologize.
Could you print the code for me, please?!

Thanks in advance!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24381936
instead of...

If Instr(conteudo,id_new) Then

do

If ubound(filter(split(replace(replace(conteudo," ","")+",",",","-,"),","),id_new+"-")) = -1 Then

make similar substitutions anyplace you are using Instr
0
 

Author Comment

by:drix76
ID: 24384664
I did the substitutions, but it still showing checked the number 2, while the numbers in conteudo are (24, 25, 32).
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24385051
I'll ask the admins to move this question to a more appropriate zone since this is a VB Script question and has nothing to do with SQL syntax.

You'll get better help from people there than I'm able to give.
My filter method worked for my testing but I'm sure VB Script experts probably have better ideas than me.
0
 

Author Comment

by:drix76
ID: 24385329
Ok. Thank You!
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24385743
sdstuber was right on in his second post, and I think we should go back to that... we're getting a bit more complicated than is probably necessary, especially if drix76 is new to VB.
Try the following code.  I know it looks like there's a lot to it, but if it works properly for you, we can rewrite it as a function so that you don't have to add fifteen lines of code everytime you're looking for a value in contuedo.

Dim conteudo as string
Dim id_new as string
 
'assign conteudo values for testing:
contuedo = "24,25,32"
id_new = "2"
 
'I'm commenting out the following for testing, but
' ultimately, you'll want to add this back in:
contuedo = Replace(contuedo," ","") 'remove spaces
contuedo = Replace(contuedo,")","") 'remove close parentheses
contuedo = Replace(contuedo,"(","") 'remove open parentheses
contuedo = "-," & contuedo & ",-" 'add commas and dummy characters fore and aft
 
If instr(contuedo, "," & id_new & ",") > 0 Then
   Response.Write("ID " & id_new & " exists.")
Else
   Response.Write("ID " & id_new & " does not exist.")
End If

Open in new window

0
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 2000 total points
ID: 24385779
Sorry, I misspelled your variable name in places.  I fixed it in the code I'm posting now. :-)
By the way, in all of the examples either of us have provided, we were working under the assumption that id_new was a string, not an integer.  If that is not the case, this could be why our solutions weren't working for you.

Dim conteudo as string
Dim id_new as string
 
'assign conteudo values for testing:
conteudo = "24,25,32"
id_new = "2"
 
'I'm commenting out the following for testing, but
' ultimately, you'll want to add this back in:
conteudo = Replace(conteudo," ","") 'remove spaces
conteudo = Replace(conteudo,")","") 'remove close parentheses
conteudo = Replace(conteudo,"(","") 'remove open parentheses
conteudo = "-," & conteudo & ",-" 'add commas and dummy characters fore and aft
 
If instr(conteudo, "," & id_new & ",") > 0 Then
   Response.Write("ID " & id_new & " exists.")
Else
   Response.Write("ID " & id_new & " does not exist.")
End If

Open in new window

0
 

Author Comment

by:drix76
ID: 24389009
It worked!!! Thank you very very much!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

771 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