Dynamic list of users is not populating on older SQL 2005

snteran · October 20th, 2011, 07:44 PM

OK, I replaced with your code and here are the results:

Code:

<tr>
    <td>Is there a value and if so what is it :</td>
    <Td>
the c.server_owner value is not null or empty
    </td></tr>

   <tr>
    <td>Is there a value and if so what is it :</td>
    <Td>
the c.server_owner value is not null or empty
    </td></tr>
   <tr>
    <td>Is there a value and if so what is it :</td>
    <Td>
the c.server_owner value is not null or empty
    </td></tr>

Felt pretty good, there was a missing "then" statement and had to fix. Far from being good but feel like I'm learning.

So what now?

snteran · October 20th, 2011, 07:47 PM

Just so you know, I ran the query in the sql console and just get the Server_owner names.

mat41 · October 20th, 2011, 07:50 PM

ooopss I forgot something. Change:

elseif len(objRS2(0))
to
elseif len(objRS2(0)) > 0 then

which should make the last elseif fire...

mat41 · October 20th, 2011, 07:51 PM

also you havnt told me the datatype?

snteran · October 20th, 2011, 07:55 PM

What are the three data types of these three fields. 2 x varchars and one int I assume.

Data types:
server_owner - varchar(max)
server_name - varchar(max)

there are only two fields and the count(*) of each time a server_owner has a server assigned to them.

Hope this helps. I'll change the code to what you asked. I am about to leave for an hour but will be back to keep working on this. Thanks,

snteran · October 20th, 2011, 07:58 PM

Changed to: elseif len(objRS2(0)) > 0 then and there was no difference. Back in an hour.

snteran · October 20th, 2011, 09:49 PM

Hi Matt, I'm back and willing to trying any other ideas. Looking at the DB, not sure why it's not working.

I have a page that shows a bunch of different data including the server_owner and that works fine.

Code:

SELECT s.SERVER_NAME, s.STATUS, s.ITCO, c.server_owner FROM server s,serverreview c WHERE s.server_name = c.server_name and s.STATUS <> 'Decommissioned' and s.STATUS <> 'Decomissioned' and s.STATUS <> 'Cold Server' ORDER BY SERVER_NAME

It's just weird that we can't even just get the page to display the server_owner. Think I need to take this page back to just the basics and build up.
Let me know if you have any other ideas.

Thanks,

mat41 · October 20th, 2011, 10:04 PM

Yes mate you are correct, stripping back seems like the best option. I am out of ideas to be honest.

I would create a new page, pull just the query out, run it, then loop and show the results. Then add bits until it stops working...

One thing I do know - if the query runs inside SQL Server and gets results it will have the same result set in an ASP page UNLESS you are passig any values into the query which of course is not being passed into SQL Server. Looking at your code it does look like you have copied fromt the ASP page and not the browser so im sure.....

I will be interested to hear what the issue is....................you may even be lucky enought to get IMAR's attention, there is nothing he does not know

snteran · October 20th, 2011, 11:18 PM

Well, not sure what the problem might be, but I changed to a JOIN and removed the <a> from the TD and it shows the server_owner. I think the issue has to do with the <a> myself, but I'll keep building little by little.

Code:

Set objRS2 = objConn2.Execute("SELECT distinct server_owner FROM serverreview INNER JOIN Server ON SERVER.server_NAME = serverreview.SERVER_NAME WHERE (Server.Status <> 'Decommissioned' and serverreview.server_Owner is not NULL and serverreview.server_Owner != 'Please Select...')")

iRow=0
While Not objRS2.EOF
If iRow Mod 2 = 0 Then
response.write "<tr bgcolor=""#F7F7F7"">"
Else
response.write "<tr bgcolor=""#CCCCCC"">"
End If

Response.Write "<td class=clsBodyText>" & objRS2.Fields("server_owner") & "</td></tr>"

snteran · October 20th, 2011, 11:38 PM

Hey Matt, If you don't mind. Not sure how to use the Join to create my ultimate goal which is to show all the different server_owners and the amount of servers they review.

Should look like:

Bill Gates 13
John Gold 24
Mike Bud 30

In the table there are server_owner,server_name and then as you can tell we have a server table that shows status and other items. Server_name is the primary id on both serverreview tabel and server table. Any help on the query would be greatly appreciated.

Thanks,