0

If I have a column with different values called vStatus (member-staus with value 100 - 200 - 300 etc.) in MySQL with diferent values, and I want to place those users in 3 columns in a table like this:

<tr>
    <td><%=[user with value 200]%></td>
    <td><%=[user with value 300]%></td>
    <td><%=[user with value 400]%></td>
</tr>

it will work just fine. But if I loop through all users and place all users with the value 200 in the 1st column

<td><%=200%></td>

all users with the value 300 in the 2nd column

<td><%=300%></td>

and finally all users with the value 400 in the 3rd column

<td><%=400%></td>

it will look something like this:

<%
Set RScolumnA = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 200 ORDER BY username ASC")
Set RScolumnB = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 300 ORDER BY username ASC")
Set RScolumnC = Conn.Execute("SELECT username, firstname, middlename, lastname FROM users WHERE vStatus = 400 ORDER BY username ASC")

If Not RScolumnA.EOF OR RScolumnB.EOF OR RScolumnC.EOF Then
    Do Until RScolumnA.EOF AND RScolumnB.EOF AND RScolumnC.EOF
%>

<tr>
    <td><%=200%></td>
    <td><%=300%></td>
    <td><%=400%></td>
</tr>

<%
    If Not RScolumnA.EOF Then
        RScolumnA.MoveNext
    End If
    If Not RScolumnB.EOF Then
        RScolumnB.MoveNext
    End If
    If Not RScolumnC.EOF Then
        RScolumnC.MoveNext
    End If
    Loop
End If
%>

This works just fine, if there's at least just one value in 1st AND in the 2nd column

But if I remove the only user with

vStatus = 200,

and there's a user with

vStatus = 300

in the 2nd column, all of the

<tr>...</tr>

disapears.

If there's at least a vStatus = 200 or vStatus = 300 - or more - no problem. But if there's only 1 user with vStatus = 200, and only one user with vStatus = 300, and I remove one of them, everything disapears.

A friend of mine suggested using DB -> make an array -> place users in table with GetRows methods (res). But that doesn't work.

I hope you understand my problem, and I hope to have some help. Thanks in advance

Steen
  • 1
  • 1
  • 1
    You need parentheses: `If Not (RScolumnA.EOF OR RScolumnB.EOF OR RScolumnC.EOF)` because `Not` has higher precedence than `OR`. – Barmar Feb 03 '23 at 15:36
  • 1
    That's some weird logic, why three separate recordsets? – user692942 Feb 03 '23 at 17:08
  • @user692942 I need to list users, whom have chosen the value 200 in the first column, 300 in the middle column and 400 in the last column – Steen Feb 04 '23 at 10:19
  • So if the data is structured correctly you should be able to craft that using one query instead of three. – user692942 Feb 04 '23 at 15:25
  • @user692942 Can you give an example on how to? I'd appreciate much – Steen Feb 05 '23 at 09:47
  • It’s entirely dependent on what your database schema is, but it should be doable using a [pivot](https://dba.stackexchange.com/q/164711/35116) or a [cte](https://stackoverflow.com/q/1382573/692942). – user692942 Feb 05 '23 at 10:46
  • I really hope :) Database schema? I'm using MySQL, NUM ... I'm not sure how much and what you are asking ...? – Steen Feb 05 '23 at 14:08
  • @user692942 I hope you have some suggestions – Steen Feb 07 '23 at 12:48

2 Answers2

0
This is a little bit clunky but does what you want as far as 
I can work out from your question. I used Sql Server as my database

<%
    
dim id, username, firstname, middlename, lastname, vStatus
Set RScolumn = Conn.Execute("SELECT [id],[username],[firstname],[middlename],[lastname],[vStatus] FROM [users] where vStatus in ( 100, 200, 300, 400, 500) order by vStatus, id asc")
%>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<% 
If Not RScolumn.EOF Then
    Do Until RScolumn.EOF
        id = RScolumn(0)
        username = RScolumn(1)
        firstname = RScolumn(2)
        middlename = RScolumn(3)
        lastname = RScolumn(4)
        vStatus = RScolumn(5)
%>
<td align="left" valign="top" nowrap>
<%=id & " " & username& " " & firstname& " " & middlename& " " & lastname& " " & vStatus  & " "%> 
</td>
<%
    If Not RScolumn.EOF Then
        RScolumn.MoveNext
    End If
    Loop
else%>
 <td align="left" valign="top" nowrap>No Data</td>
 <%
End If
%>
</tr>
</table>
ClassicASF
  • 1
  • 2
0

I added a getrows solution too if you needed that

<%
Dim ARYRecords, cRecords, cFields, id, username, firstname, middlename, lastname, vStatus, vStatusSwapper

Set RScolumn = Conn.Execute("SELECT [id],[username],[firstname],[middlename],[lastname],[vStatus] FROM [users] where vStatus in (100, 200, 300, 400, 500) order by vStatus, id asc")

Response.Write "<table border=""1"" cellspacing=""1"" cellpadding=""1"">" & vbCrLf
Response.Write "<tr>" & vbCrLf
If Not RScolumn.EOF Then
    ARYRecords = RScolumn.GetRows()
    RScolumn.Close
    Conn.Close
    Set RScolumn = Nothing
    Set Conn = Nothing

    For cRecords = 0 to Ubound(ARYRecords, 2)
        id = ARYRecords(0, cRecords)
        username = ARYRecords(1, cRecords)
        firstname = ARYRecords(2, cRecords)
        middlename = ARYRecords(3, cRecords)
        lastname = ARYRecords(4, cRecords)
        vStatus = ARYRecords(5, cRecords)
        if vStatusSwapper = "" then 
            response.write  "<td nowrap>"
        elseif vStatusSwapper <> vStatus then
            response.write  "</td>" & vbCrLf & "<td nowrap>" 
        end if  
        response.write username & " " & firstname & " " & middlename & " " & lastname & " " 
        vStatusSwapper = vStatus
    Next
    response.write "</td>" & vbCrLf
    response.write "</tr>" & vbCrLf
Else
    Response.Write "<tr><td colspan=""6"" align=""center"">No Data</td></tr>" & vbCrLf
End If
Response.Write "</table>" & vbCrLf
%>
ClassicASF
  • 1
  • 2