Joining multiple tables 1

[gmagerr]

Hi guys, i'm stumped... I have a form that inserts info into 3 tables. then it goes to a master list where you can edit the info. here's the code that inserts into the tables after the form is submitted.


<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<%
Option Explicit
Response.Buffer = True
Response.ContentType = &quot;text/html; charSet=iso-8859-1&quot;
'--------------------------------------------------------------
'--- Module: SubmitPSS.asp
'---
'--- Version: 1.0
'---
'--- Purpose: This will take the values inputted on the PSS form,
'--- and insert them into the correct database tables.
'---
'--- Dependencies:
'---
'--- Notes:
'---
'--- Author: Gene Magerr genemagerr@hotmail.com
'--- Copyright 2001 Magerr Media all rights reserved
'--------------------------------------------------------------
%><!--#include file=&quot;../../adovbs.inc&quot;--><%
'--------------------------------------------------------------
Dim cnTelmon, rsCust, rsSite, rsJob, rsOrderDetails
Dim strSQL, JobID, CustID, JobCode, SiteID
'--------------------------------------------------------------
'--- Declarations
'--------------------------------------------------------------
Set cnTelmon = Server.CreateObject(&quot;ADODB.Connection&quot
cnTelmon.Open &quot;DSN=Telmon&quot;

Set rsCust = Server.CreateObject(&quot;ADODB.Recordset&quot
rsCust.Open &quot;Customers&quot;, cnTelmon, adOpenStatic, adLockOptimistic
rsCust.addnew

rsCust(&quot;CustCompanyName&quot = Request.Form(&quot;custCompanyName&quot
rsCust(&quot;CustContactName&quot = Request.Form(&quot;custContactName&quot
rsCust(&quot;CustContactTitle&quot = Request.Form(&quot;custContactTitle&quot
rsCust(&quot;CustAddress&quot = Request.Form(&quot;custAddress&quot
rsCust(&quot;CustCity&quot = Request.Form(&quot;custCity&quot
rsCust(&quot;CustState&quot = Request.Form(&quot;custState&quot
rsCust(&quot;CustZip&quot = Request.Form(&quot;custZip&quot
rsCust(&quot;CustEmail&quot = Request.Form(&quot;custEmail&quot
rsCust(&quot;CustPhone&quot = Request.Form(&quot;custPhone&quot

rsCust.Update
rsCust.MoveLast


Session(&quot;CustomerID&quot = rsCust(&quot;CustomerID&quot
CustID = Session(&quot;CustomerID&quot

rsCust.Close
Set rsCust = Nothing


Set rsSite = Server.CreateObject(&quot;ADODB.Recordset&quot
rsSite.Open &quot;JobSite&quot;, cnTelmon, adOpenStatic, adLockOptimistic
rsSite.addnew

rsSite(&quot;SiteCustomerID&quot = CustID
rsSite(&quot;SiteCompanyName&quot = Request.Form(&quot;siteCompanyName&quot
rsSite(&quot;SiteContactName&quot = Request.Form(&quot;siteContactName&quot
rsSite(&quot;SiteContactTitle&quot = Request.Form(&quot;siteContactTitle&quot
rsSite(&quot;SiteAddress&quot = Request.Form(&quot;siteAddress&quot
rsSite(&quot;SiteCity&quot = Request.Form(&quot;siteCity&quot
rsSite(&quot;SiteState&quot = Request.Form(&quot;siteState&quot
rsSite(&quot;SiteZip&quot = Request.Form(&quot;siteZip&quot
rsSite(&quot;SiteEmail&quot = Request.Form(&quot;siteEmail&quot
rsSite(&quot;SitePhone&quot = Request.Form(&quot;sitePhone&quot

rsSite.Update
rsSite.MoveLast

Session(&quot;JobSiteID&quot = rsSite(&quot;JobSiteID&quot
SiteID = Session(&quot;JobSiteID&quot

rsSite.Close
Set rsSite = Nothing

Set rsJob = Server.CreateObject(&quot;ADODB.Recordset&quot
rsJob.Open &quot;Jobs&quot;, cnTelmon, adOpenStatic, adLockOptimistic
rsJob.AddNew

rsJob(&quot;JobSiteID&quot = SiteID
rsJob(&quot;CustomerID&quot = CustID
rsJob(&quot;JobCode&quot = Request.Form(&quot;JobCode&quot
rsJob(&quot;PONumber&quot = Request.Form(&quot;PONumber&quot
rsJob(&quot;OpenedBy&quot = Request.Form(&quot;OpenedBy&quot
rsJob(&quot;LeadTech&quot = Request.Form(&quot;LeadTech&quot
rsJob(&quot;CustomerQuoted&quot = Request.Form(&quot;CustomerQuoted&quot
rsJob(&quot;EngineeredBy&quot = Request.Form(&quot;EngineeredBy&quot
rsJob(&quot;TechSkillLevel&quot = Request.Form(&quot;TechSkillLevel&quot
rsJob(&quot;TimeAndMaterial&quot = Request.Form(&quot;TimeAndMaterial&quot
rsJob(&quot;BudgetedHours&quot = Request.Form(&quot;BudgetedHours&quot
rsJob(&quot;ProjectManager&quot = Request.Form(&quot;ProjectManager&quot
rsJob(&quot;PermitsNeeded&quot = Request.Form(&quot;PermitsNeeded&quot
rsJob(&quot;UnionIssues&quot = Request.Form(&quot;UnionIssues&quot
rsJob(&quot;ReqReceivedOn&quot = Request.Form(&quot;ReqReceivedOn&quot
rsJob(&quot;ReqStart&quot = Request.Form(&quot;ReqStart&quot
rsJob(&quot;ReqFinish&quot = Request.Form(&quot;ReqFinish&quot
rsJob(&quot;RequiredStart&quot = Request.Form(&quot;RequiredStart&quot
rsJob(&quot;RequiredFinish&quot = Request.Form(&quot;RequiredFinish&quot
rsJob(&quot;NumOfLocs&quot = Request.Form(&quot;NumOfLocs&quot
rsJob(&quot;PhoneSys&quot = Request.Form(&quot;PhoneSys&quot
rsJob(&quot;InvoiceDate&quot = Request.Form(&quot;InvoiceDate&quot
rsJob(&quot;Schedueled&quot = Request.Form(&quot;Schedueled&quot
rsJob(&quot;FloorPlan&quot = Request.Form(&quot;FloorPlan&quot
rsJob(&quot;Invoiced&quot = Request.Form(&quot;Invoiced&quot
rsJob(&quot;Materials&quot = Request.Form(&quot;Materials&quot
rsJob(&quot;JobStatus&quot = Request.Form(&quot;JobStatus&quot
rsJob(&quot;ScopeOfWork&quot = Request.Form(&quot;ScopeOfWork&quot

rsJob.Update
rsJob.MoveLast

Session(&quot;JobCode&quot = rsJob(&quot;JobCode&quot
JobCode = Session(&quot;JobCode&quot
Session(&quot;JobID&quot = rsJob.Fields.Item(&quot;JobID&quot.Value

rsJob.Close
Set rsJob = Nothing

Set rsOrderDetails = Server.CreateObject(&quot;ADODB.Recordset&quot
rsOrderDetails.Open &quot;OrderDetails&quot;, cnTelmon, adOpenStatic, adLockOptimistic
rsOrderDetails.AddNew

rsOrderDetails(&quot;JobCode&quot = JobCode

rsOrderDetails.Update
rsOrderDetails.MoveLast

rsOrderDetails.Close
Set rsOrderDetails = Nothing

Response.Redirect &quot;PSSMaster.asp&quot;

'--------------------------------------------------------------
'--- Initialization
'--------------------------------------------------------------

'--------------------------------------------------------------
'--- Body
'--------------------------------------------------------------

'--------------------------------------------------------------
'--- Begin HTML output
'--------------------------------------------------------------
%>
<HTML>
<HEAD>
<TITLE>Untitled</TITLE>
</HEAD>

<BODY>


</BODY>
</HTML>
<%
'----------------------------------------------------------------------
'--- End HTML Output
'----------------------------------------------------------------------
cnTelmon.Close
Set cnTelmon = Nothing

'----------------------------------------------------------------------
'--- All ASP post processing code goes here, as well as
'--- sub routines and functions
'----------------------------------------------------------------------

%>

I need to write an innerjoin statement that populates the form fields with the correct info from the corrisponding tables. For example, the jobs table info is pulled and placed in the text boxes ready to be edited, but the customer and jobsite info generates an error saying the data can't be found. So how would i join 3 tables together so all the data is relivant?

 

[ufobaby]

hi, am not too sure abt the display part but if u want a select statement then -

select <<whatever>>
from (Customers AS c Inner Join Jobs AS j on c.CustomerId=j.CustomerID) Inner Join JobSite AS s on s.JobSiteID = j.JobSiteID
where
<<put conditions if any here>>

Hope this helps, if i understood your problem.

Regards
Niraj