comparing loop

[PhatH]

Hi all,
I have this code here that collects rs1("ProdTypeID") columns which contain a varchar such as (1, 2, 3, ...) and assigns each line to a var strProdTid, then break the string down to bit by using Split, and use each number in the string to locate info by using sql on the below
<%
do while not rs1.eof
strProdTid = rs1("ProdTypeID")
response.Write(strProdTid)
arrNums = Split(strProdTid, ", ", -1, 1)
For Each strOneNum in arrNums
sql = "SELECT * FROM tblProductType WHERE ProdTypeID = " & strOneNum
Set rs5 = objConn.Execute(sql)
%>
<a href="default.asp?printer=1&ManuID=<% = request.querystring("ManuID") %>&ProdTypeID=<% = rs5("ProdTypeID") %>"><% =rs5("ProdTypeName") %></a><br>
<%
Next
rs1.movenext
loop
%>

My problem is whatever in the strings and all columns, they will be all shown in the output. ie:
1
2
2, 3

My question is how can I only allow different data on a screen, ie:
1
2
3

Thanks!!!

 

[bastienk]

sure, you can change the sql to select DISTINCT items

or do it in code

old_val = ""
For Each strOneNum in arrNums
  if old_val <> strOneNum then
       sql = "SELECT * FROM tblProductType WHERE ProdTypeID = " & strOneNum
       Set rs5 = objConn.Execute(sql)
%>
       <a href="default.asp?printer=1&ManuID=<% = request.querystring("ManuID") %>&ProdTypeID=<% = rs5("ProdTypeID") %>"><% =rs5("ProdTypeName") %></a><br>
<%
      old_val = strOneNum
  end if
Next
rs1.movenext
loop    
[code]




Bastien

Cat, the other other white meat

 

[PhatH]

thank bastienk,

The first choice to DISTINCT, I don't think it's possible because I'm dealing with a string in a column that found. So the second choice should be more specific to the problem.

And the second choice is the exact way I found in a book and successfully used before; however, it doesn't work in this case for some weird reasons.

The outputs are still duplicated displayed.

Is there another way???

 

[PhatH]

By the way,

If I change the code:

sql = "SELECT * FROM tblProductType WHERE ProdTypeID = " & old_val

and move old_val = strOneNum

2 possible outputs:

- no output will be found and display will be empty
- ERROR:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

 

[amorous]

If the variable old_val is a string then use

sql = "SELECT * FROM tblProductType WHERE ProdTypeID = '"&old_val&"' "


-VJ

 

[PhatH]

thank amorous,

I tried that too, but outputs are still duplicated.

 

[bastienk]

try this one

old_val = ""
For Each strOneNum in arrNums
  if old_val <> strOneNum then
       sql = "SELECT * FROM tblProductType WHERE ProdTypeID = " & strOneNum
       Set rs5 = objConn.Execute(sql)
%>
       <a href="default.asp?printer=1&ManuID=<% = request.querystring("ManuID") %>&ProdTypeID=<% = rs5("ProdTypeID") %>"><% =rs5("ProdTypeName") %></a><br>
<%
      
  end if
  old_val = strOneNum
Next
rs1.movenext
loop

Bastien

Cat, the other other white meat

 

[PhatH]

I'm sorry Bastien,

No change after I moved as the instruction.

 

[bastienk]

sorry forgot it was a string

sql = "SELECT * FROM tblProductType WHERE ProdTypeID = '" & strOneNum & "'"

Bastien

Cat, the other other white meat

 

[PhatH]

That's ok Bastien b/c I tried both int and string before I agree it's not changing.

 

[bastienk]

what error are you getting?

can you response.write the sql before execution to see what it looks like? Does the sql look ok?


Bastien

Cat, the other other white meat

 

[PhatH]

Maybe the requirement of mine is out of hand. I have another idea to display my results, hope this will symplify the situation a little bit.

Instead of break the string down, get each number's Name by using SQL, then display ea Name onto the screen... by differentiating every output.

Now, I just want to break the string down, get each number's according Name by using SQL, then display every output to the screen, ie:
1 Test1
2 Test2
1, 2 Test1 Test2

Just ignore the number in front of the outputs.

Do you think we can try this?

 

[PhatH]

To Bastien,

There's no ERROR.

The outputs are just duplicated, ie:

1 Test1
2 Test2
2 Test2
3 Test3

P.S. first 1 and 2 are individual number in there own columns, while the last 2 and 3 is actually (2, 3) in 1 column.

 

[PhatH]

Output strName: Test1, Test2,

I'm trying to TRIM the last comma in the output strName

<%
strProdTid = request.querystring("ProdTypeID")
arrNums = Split(strProdTid, ", ", -1, 1)
strName = ""
For Each strOneNum in arrNums
sql = "SELECT * FROM tblProductType WHERE ProdTypeID = " & strOneNum
Set rs4 = objConn.Execute(sql)
strName = rs4("ProdTypeName") + ", " + strName
Next
strName = Right(strName, 2) *** this is not helping ***
%>