Using Multiple DB Tables (ASP)

[anonymous]

[Originally posted by Terry Ward]

How can I access data using multiple table using the 'WHERE' statement?

[anonymous]

[Originally posted by gonzalo garcia]

are you using sql sever or access???

[anonymous]

[Originally posted by Terry Ward]

I'm using SQL Server 2000.

[anonymous]

[Originally posted by gonzalo garcia]

ok
you have to first open a sql connection like this

Dim consql
Set consql = Server.CreateObject("ADODB.Connection")
consql.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = mydatabase; User Id = myloginid; Password=mypassword"

then you can start to open each table

dim Comtable1
dim Sqltable1
Set Comtable1=server.createobject("adodb.command")
set comtable1.activeconnection=consql
sqltable1="select * from table1"
comtable1.commandtext=sqltable1
set rstable1=comtable1.execute
if rstable1.recordcount=0 then
response.write("no records to show")
response.end
end if
rstable1.movefirst

and table2 and table 3 and table 4 etc


now, if you have 2 or 3 differents tables and you wont to show the tables like they are only one then you have to use the JOIN command

lets image that you have the table1 like this

id1,level
0,yellow
1,red
2,green
3,white

and the table2 like this

employee,level,name
a,0,me
b,3,me again
c,2,just me :)

if you wont to show witch level is 0,3,2 in the employee table then you have to join both tables like this employ

select table2.employee,table1.level,table2.name
from table2 inner join table1
on table2.level=table1.id1
where table2.level=table1.id1

by using this command the resultset will show some like this


a,yellow,me
b,white,me again
c,green,just me :)

if you need to join 2 or more tables then you have to add a inner join for each table
to use the inner join you have to have a common field on both tables
i.e. the field table2.level and table1.id1 they are the same type
if you try to join on table2.name=table1.id you will get an error or the computer
you HAVE TO use the where condition, this is very important, if you forget the where
condition you going to get a mixed table

be carefull with the where condition

hth

gonzalo

[anonymous]

[Originally posted by Terry Ward]

Everything worked well and thank you very much.

However, I would like to calculate hours for related items in the database as it is being displayed to screen. Such as:

Itemÿ Hours
=====ÿ ======
ABCÿ ÿ 2
ABCÿ ÿ 3.5
ÿ ÿ ÿ ------
Total 5.5

DEFÿ ÿ 8.2
ÿ ÿ ÿ ------
Total 8.2


Here's the code:


Dim conn, strSQL, rs
Dim vMsg, lckStatus, vqcpid, vStatus, vR1, vR2, vassignee, vcalc1, vcalc2
Dim vGetID, vSavedID

vassignee = Application("tassignee")
vStatus = request.QueryString("nStatus")
lckStatus = request.QueryString("lckStatus")
vqcpid = request.QueryString("id")
vR1 = request.QueryString("R1")
vR2 = request.QueryString("R2")

strSQL = "SELECT calllog.callid, calllog.dProbDesc, calllog.callstatus, asgnmnt.acthours, asgnmnt.dPlanviewId, asgnmnt.dPlanview, asgnmnt.dProjId, asgnmnt.dateassign,

asgnmnt.assignee, asgnmnt.groupname, asgnmnt.callid from calllog inner join asgnmnt on calllog.callid=asgnmnt.callid where calllog.callid=asgnmnt.callid and assignee=" & "'"

& vassignee & "'" & " and dateassign >= '" & vR1 & "' and dateassign <= '" &vR2 & "' order by dPlanviewID"

'response.write "ID: " & vqcpid & "
LckStatus: " & lckStatus & "
Status: " & vStatus & "
"
'response.write "R1: " & vR1 & "
R2: " & vR2 & "
"
'response.write "NAME: " & vassignee & "
"
'response.write "SQL String: " & strSQL & "
"
'response.end

set conn=server.createobject("adodb.connection")
conn.open Application("db_ConnectionString")

set rs = conn.execute(strSQL)




If (rs.BOF) AND (rs.EOF) then


<Table border=5 bgcolor="#330000" cellspacing=3 cellpadding=5>
<tr>
<td>
<font color=white size=6>HEAT CMS Actual Hours</font><p></p>

Response.Write("<font color=yellow><b>Results:</b> No record/data found.</font>")

<!--<p>Response.Write(sqlString)-->
<p>
Response.Write("<font color=yellow>Or, the database maybe currently under maintenance. Please contact the HEAT Call Management

System Administrator for further assistance if problems/issues persist.</font>")
<p>
<a href="mailto:tward@caremark.com">Terry Ward, HEAT Call Management System Enterprise Administrator</a><p>
</td>
</tr>
</Table>


ELSE


<Table border=0 align=center valign=left width="100%">
<tr>
<td width=38
ÿ ÿ <img src="Coordinator.gif">
<img src="Frontrange.bmp">
</td>
ÿ <td> ÿ
<strong><font size=8><i>A</i></font><Font size=5 face="Verdana, Arial, Helvetica">CTUAL HOURS</strong></Font>
</td>
<td>
<a href="http://www.rx-r-us.com/rxruspsd.asp"><img src="RXRUS.GIF"
ÿ ÿ ÿ ÿ alt="Caremark Rx-R-US Logo" width="57" height="66"></a>
</td>
</tr>
</Table>
<hr>

<center>
<b><font size=2 color=black>Includes all <b><i><u>Unclosed</u> </i></b>tickets and <u> <i><b>Tickets Closed</u> </i></b> during </font><font size=2

color=gray> =vR1 to =vR2</font><p>
<b><font size=4 color=black>Customer: </b></font><font size=4 color=magenta> =rs.fields("Assignee")</font>
</center>




<input type="button" name="print" value="Print Document" onclick="varitext()"></input>
<input type=button value="Previous Page" onclick="javascript:history.go(-1);" ></input>
<p>

On Error Resume Next
rs.MoveFirst


vSavedID=rs.fields("dPlanviewId")

Do While not rs.eof


if rs.fields("dPlanviewId") = vGetID then
vcalc1 = rs.fields("ActHours")
vcalc2 = vcalc2 + vcalc1
else
vcalc2=rs.fields("ActHours")
end if

'response.write "GET: " & vGetID & "ÿ ÿ ***ÿ ÿ ÿ SAVED:ÿ " & vSavedIDÿ & "ÿ ÿ ***ÿ " & vcalc2 & vbCrLf


<table>
<tr>
<td>
<b><font size=4 color=black>Project ID: </b></font>
<font size=3 color=blue> =rs.fields("dPlanviewId") -
</td>
<td>
=rs.fields("dProjId")</font>
</td>
</tr>
</table>
<table>
<tr>
<td>
ÿÿÿÿÿ
<b><font size=3 color=black><i>Planview Phase - </b></font>
<font size=3 color=gray> =rs.fields("dPlanview")</i>
</td>
</tr>
</table>


<table border=0 cellpadding=1 cellspacing=1 width=70
<tr>
<td></td><td></td><td></td><td></td><td></td>
<td width=10% valign=top>
<b><font size=2 color=black><u>Ticket #</b></u></font>

<font size=2 color=gray> =rs.fields("callid")
</td>
<td width=15% valign=top>
<b><font size=2 color=black><u>Call Status</b></u></font>

<font size=2 color=gray> =rs.fields("callstatus")
</td>
<td width=15% valign=top>
<b><font size=2 color=black><u>Group</b></u></font>

<font size=2 color=gray> =rs.fields("groupname")
</td>
<td valign=top bgcolor=gray>
<b><font size=2 color=black><u>Short Description</b></u></font>

<font size=2 color=white> =rs.fields("dProbDesc")
</td>
<td width=15% valign=top>
<center><b><font size=2 color=red><u>Hours</b></u></font>

<font size=3 color=black><b>=rs.fields("acthours")</b></font></center>
</td>
</tr>
</table>



vGetID=rs.fields("dPlanviewId")

rs.MoveNext

vSavedID=rs.fields("dPlanviewId")

loop


set rs=nothing
set conn=nothing
conn.close


End if