Accessing 2 tables from the database and outputting in HTML table

[Payal80]

Hi!
I have 2 tables:
Organization(Org_Id,Name,Address,Phone)
Rooms(Room_Idi,Organization_Idi,Category,Availabil
ity)
The category is a drop down menu which has 4 options, type1,type2,type3 and type4. Availabily has "yes" or "no" inputted into the database for each room in the organization.

I have to output a table which has the following details:
Organization Name,Address,No of rooms available in each category.

I cant seem to get it right. The details from the Organization table seems to be outputted properly but the Rooms table details are not being outputted right. This is my ASP codeI have written to output only type1. I thought if I can output type1, i can output the others easily):
<%
Dim myArray()
numRows = 0
numRows1= 0
Dim temp
Set Cm = Server.CreateObject(&quot;ADODB.Command&quot
Cm.ActiveConnection = &quot;sabesan&quot;

Cm.CommandText = &quot;select Org_Id,Name,Address,no_of_rooms from Organization&quot;
' Creating Connection Object and opening the database
Cm.CommandType = 1
Set RS = Cm.Execute


Do While NOT RS.EOF
numRows = numRows + 1
ReDim Preserve myArray(7, numRows)
temp=RS(0)
myArray(0, numRows - 1) = RS(0)
myArray(1, numRows - 1) = RS(1)
myArray(2, numRows - 1) = RS(2)
myArray(3, numRows - 1) = RS(3)


Cm.CommandText =&quot;SELECT count(*) from Rooms r,Organization o where r.Organization_Idi='temp' and r.room_availability='yes' and r.Category='roomtype1'&quot;
Cm.CommandType = 1
Set RS1 = Cm.Execute

numRows1 = numRows1 + 1
myArray(4, numRows1 - 1) = RS1(0)

RS.MoveNext
Loop


Response.Write(&quot;<table border=2>&quot
For i = 0 to UBound(myArray, 2)
' Response.Write(&quot;<tr><td>#&quot; & i & &quot;</td>&quot
'Response.Write(&quot;<td>&quot; & myArray(0,i) & &quot;</td>&quot
Response.Write(&quot;<td>&quot; & myArray(1,i) & &quot;</td>&quot
Response.Write(&quot;<td>&quot; & myArray(2,i) & &quot;</td>&quot
Response.Write(&quot;<td>&quot; & myArray(3,i) & &quot;</td>&quot
Response.Write(&quot;<td>&quot; & myArray(4,i) & &quot;</td></tr>&quot
Next

Response.Write(&quot;</table>&quot
RS.close
RS1.close
%>
please please help me out???

 

[rossno]

Hi,

Does this help?

Your SQL might look something like:

select Organization Name, Address, count(Category)
from Organization O , Rooms R
where O.Org_Id = R.Organization_Idi
and R.Availability = 'yes'
group by R.Category

Then you can pass the select statement to a subroutine that looks something like:

<% '--------------------------------------------------------
sub showTable (SQLcmd)

indx = InStr(1, SQLcmd, &quot; &quot;, 1)-1

cmdType = Left(SQLcmd, indx) '** parse for select, update, delete cmd

set cnnSuperDC = Server.CreateObject(&quot;ADODB.Connection&quot
cnnSuperDC.Open DB_CONNECTIONSTRING, DB_USERNAME, DB_PASSWORD

if cmdType = &quot;select&quot; then '** process the select.
set resultSet = cnnSuperDC.Execute(SQLcmd)

'Response.write (&quot;*** &quot; & SQLcmd & &quot;<BR>&quot

if NOT resultSet.EOF then %>
<DIV>
<TABLE border=1>
<TR bgColor=&quot;gray&quot;>
<% p_numcols = resultSet.Fields.Count
'Response.write (&quot;*** &quot; & SQLcmd & &quot;<BR>&quot & &quot;...numcols = &quot; & p_numcols

for j = 0 to p_numcols -1 %>
<TH><FONT size=&quot;2&quot; face=&quot;Arial Narrow&quot; color=&quot;white&quot;><%=resultSet.Fields(j).Name %></FONT></TH>
<% next %>
</TR>

</DIV>

<DIV>

<%while not resultSet.EOF %>
<TR bgcolor=&quot;white&quot;>

<% for each col in resultSet.Fields %>
<TD><FONT size=&quot;2&quot; face=&quot;Arial&quot; color=&quot;black&quot;><%= col.Value %></FONT> </TD>
<% next %>
</TR>
<% resultSet.MoveNext
wend %>
</TABLE>
</DIV>

<% resultSet.Close
set resultSet = nothing%>
<% end if %>
<% else %>
<% cnnSuperDC.Execute(SQLcmd) '** do update or delete. %>
<br><h4>DB has been modified ok</h4>
<% end if %>
<% cnnSuperDC.Close
set cnnSuperDC = nothing %>

<% end sub ' showTable %>

gook luck, .., lemme know

norm

 

[Payal80]

I couldnt figure out from where i have to pass SQLcmd. I am so new to ASP.
could u help me out?
Thanks.

 

[Payal80]

Hi again!
I was trying the query by it self. there seems to be an error.
this is the error i got:
You tried to execute a query that does not include the specified expression 'Name' as part of an aggregate function

Actually i am trying to get this as my output header:
Name,Address,Type1,Type2,Type3,Type4
and the values under each type1,type2...will be the number of vacant rooms which have room_availability=&quot;yes&quot;.
please help me out...i have this project deadline soon..

 

[rossno]

Hi again,

In order to show the table as the results of the SQLcmd string, just pass the string like:

...
<% showTable SQLcmd %>

Note that the showTable subroutine will accept any reasonable SQL command and try to build a HTML table by using the result set. Pretty nifty huh!!

As for the SQL, I don't have your database, so can't test it.. You should create the SQL using your DB BEFORE embedding the SQL in your ASP; then you know that your query is doing what you want.

select O.Organization_Name, O.Address, R.Category, count(R.Category) as Count_Type
from Organization O , Rooms R
where O.Org_Id = R.Organization_Idi
and R.Availability = 'yes'
group by R.Category

This should be close to what you want/need, but you may need to play with it a bit. Note that I added &quot;Category&quot; as a column in the resultset. When you use aggregate functions like count(*) you need the &quot;group by&quot; clause too.

good luck,

norm