Using Multiple DB Tables (ASP)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Using Multiple DB Tables (ASP)

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Using Multiple DB Tables (ASP)

    [Originally posted by Terry Ward]

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

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Using Multiple DB Tables (ASP)

    [Originally posted by gonzalo garcia]

    are you using sql sever or access???

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Using Multiple DB Tables (ASP)

    [Originally posted by Terry Ward]

    I'm using SQL Server 2000.

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Using Multiple DB Tables (ASP)

    [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

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Using Multiple DB Tables (ASP)

    [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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center