Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying Data from multiple tables

    Can someone please help me out with this, I need my case statement corrected as I'm having trouble displaing data from 3 tables. Please Please, this is very urgent.

    <%
    Response.expires = 0
    Response.expiresabsolute = Now() - 1
    Response.addHeader "pragma", "no-cache"
    Response.addHeader "cache-control", "private"
    Response.CacheControl = "no-cache"
    %>


    <%
    response.buffer = true

    'get key
    key = request.querystring("key")
    if key="" or isnull(key) then
    key=request.form("key")
    end if
    if key="" or isnull(key) then response.redirect "tblConsignmentslist.asp"

    'get action
    a=request.form("a")
    if a="" or isnull(a) then
    a="I" 'display with input box
    end if

    ' Open Connection to the database
    set conn = Server.CreateObject("ADODB.Connection")
    conn.Open xDb_Conn_Str

    Select Case a
    Case "I": ' Get a record to display

    tkey = "'" & replace(replace(key & "","'","''"),"[","[[]") & "'"
    strsql = "SELECT * FROM [tblConsignments] WHERE [ConsignmentID]=" & tkey

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strsql, conn
    If rs.EOF Then
    Response.Clear
    Response.Redirect "tblConsignmentslist.asp"
    Else
    rs.MoveFirst
    End If

    ' Get the field contents
    x_ConsignmentID = rs("ConsignmentID")
    x_Destination = rs("Destination")
    x_Origin = rs("Origin")
    x_Status = rs("Status")
    x_Type = rs("Type")
    x_Description = rs("Description")
    x_Remarks = rs("Remarks")
    x_ConsignDate = rs("ConsignDate")
    x_ConsignTime = rs("ConsignTime")
    x_DepartDate = rs("DepartDate")
    x_ArriveCentral = rs("ArriveCentral")
    x_DepartCentral = rs("DepartCentral")
    x_ArriveDest = rs("ArriveDest")
    x_SentOffDest = rs("SentOffDest")
    End Select
    Set rs = Nothing

    Select Case a
    Case "I": ' Get a record to display

    tkey = "'" & replace(replace(key & "","'","''"),"[","[[]") & "'"

    strsql = "SELECT * FROM [tblSender] WHERE [ConsignmentID]=" & tkey

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strsql, conn
    If rs.EOF Then
    Response.Clear
    Response.Redirect "tblConsignmentslist.asp"
    Else
    rs.MoveFirst
    End If

    ' Get the field contents
    x_ConsignmentID = rs("ConsignmentID")
    x_SenderName = rs("Name")
    x_SenderBusiness = rs("Business")
    x_SenderAddress = rs("Address")
    x_SenderWorkNo = rs("WorkNo")
    x_SenderHomeNo = rs("HomeNo")
    x_SenderMobileNo = rs("MobileNo")
    x_SenderEmail = rs("Email")

    End Select

    Set rs = Nothing

    Select Case a
    Case "I": ' Get a record to display

    tkey = "'" & replace(replace(key & "","'","''"),"[","[[]") & "'"

    strsql = "SELECT * FROM [tblReceiver] WHERE [ConsignmentID]=" & tkey

    set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strsql, conn
    If rs.EOF Then
    Response.Clear
    Response.Redirect "tblConsignmentslist.asp"
    Else
    rs.MoveFirst
    End If

    ' Get the field contents
    x_ConsignmentID = rs("ConsignmentID")
    x_ReceiverName = rs("Name")
    x_ReceiverBusiness = rs("Business")
    x_ReceiverAddress = rs("Address")
    x_ReceiverWorkNo = rs("WorkNo")
    x_ReceiverHomeNo = rs("HomeNo")
    x_ReceiverMobileNo = rs("MobileNo")
    x_ReceiverEmail = rs("Email")

    End Select

    Set rs = Nothing

    rs.Close
    %>





    <font size="5">View Consignment Details</font>

    Back to List</font></p>




    <form>
    <table border="0" cellspacing="1" cellpadding="5" bgcolor="#CCCCCC">
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Consignment ID</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ConsignmentID %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Destination</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><%

    Response.Write x_destination

    %>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Origin</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><%
    Response.Write x_origin

    %>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Status</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% Select Case x_Status
    Case "0" response.write "Departed for Central Office"
    Case "1" response.write "Arrived in Central Office"
    Case "2" response.write "Departed For Destination"
    Case "3" response.write "Arrived in Destination"
    Case "4" response.write "Sent off to Recepient"
    End Select %>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Type</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% Select Case x_Type
    Case "" response.write ""
    Case "0" response.write "Document"
    Case "1" response.write "Parcel"
    End Select %>
    </font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Description</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_Description %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Remarks</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_Remarks %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Depart Date</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_DepartDate %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Arrive Central</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ArriveCentral %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Depart Central</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_DepartCentral %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Arrive Dest</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ArriveDest %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sent Off Dest</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SentOffDest %></font>*</td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Name</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderName %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Business</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderBusiness %></font>*</td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Address</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderAddress %></font>*</td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Work No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderWorkNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Home No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderHomeNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Sender Mobile No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderMobileNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Email</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_SenderEmail %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Name</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverName %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Business</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverBusiness %></font>*</td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Address</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverAddress %></font>*</td>
    </tr>

    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Work No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverWorkNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Home No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverHomeNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Receiver Mobile No</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverMobileNo %></font>*</td>
    </tr>
    <tr>
    <td bgcolor="#0099CC"><font color="#FFFFFF"><font size="-1">Email</font>*</font></td>
    <td bgcolor="#FFFFFF"><font size="-1"><% response.write x_ReceiverEmail %></font>*</td>
    </tr>
    </table>
    </form>

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying Data from multiple tables

    Hi Mark,

    It's going to be hard to troubleshoot without a little more detail as to the nature of the problem.

    However, I will mention a few things that I noticed.

    I have never seen or used a Case statement with a colon ( : ) after the Case keyword. It's possible that it's not a problem, but I've certainly never seen that before. The common construction is:<pre>Select Case [Variable]
    Case [Value]
    Code Here
    Case [Value] (etc.)
    Code Here
    Case Else (if needed)
    Code Here
    End Select</pre>

    Also, you closed a recordset after setting it to nothing. Try moving the rs.Close line above set rs=Nothing.

    Please post back with any further error messages or specific line numbers that are not working.

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •