Tanya,
I'm not sure about inner join, but the following should give the same effect and I use it all the time:
- strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
-
strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
-
strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
-
strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
-
strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
-
strSQL= strSQL & " AND ((sql3.MAINTENACE_STATUS)=Y))"
As far as I understand innerjoin, it sets up the same relationship as this. Try it out and tell me if it works.
Jared
=============== =====
Jared, Thanks for replying my questions so patiently...
It didnt work...just gives an error Internal Server Error -500
I dont know for some reason, its not establishing connection to db, is what I feel...
I have a asp page- where customers enter thier user name and the pwd stored in sql server...Once they are logged in- they are taken to another page 2, where they would click on a link to download the software. I have redirected this link to another asap page-3, where I have connected to an access db and running above code....
So- I am not sure, where I am making mistake....Plea se help..I can send you the page where I am writing all this code. I am pretty sure, that there is no link between asp page-1 where the customer logs in and clicks on this link...
The asp page code where I have written the above code is:
------------------------------
<%@ language=VBScri pt%>
<% Option Explicit
'Declare Local Variables
Dim conn, strSQL
Dim strEmail,strUrl
Dim objMail, objRS
Dim strMessageBody, strDisclaimer
Dim sql1, sql2, sql3, sql4, UserName, Password
Dim CONTRACT NUMBER, MAINTENANCE_STA RT_DATE_C, MAINTENANCE_EXP IRATION_DATE_C, downloadURL
Dim ESD_Contact, MAINTENANCE_STA TUS, User_ID, Pwd
'End Variable Declaration
%>
<html><head><ti tle>Posting Mail</title></head>
<body bgcolor=acqua leftmargin='1' topmargin='1'>< table width='100%' height='100%' cellspacing='0' cellpadding='2' >
<tr align='left'><t d height='100%' width='145' valign='top'></td><td width='555' valign='top' align='left'>
'<%'get form elements and compose email
'Function ReplaceSpecial( str)
'If NOT IsNull(str) Then
'ReplaceSpecial = Replace(str, "'", "''")
'End If
'End Function
'strEmail = Trim(ReplaceSpe cial(Request.Fo rm("User_Email" )))
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Provider=" Microsoft.Jet.O LEDB.4.0"
conn.Open "DSN=SQL"
Set objRS = Server.CreateOb ject("ADODB.Rec ordset")
session("UserNa me")="abd"
session("Passwo rd")="123"
strSQL = "SELECT * FROM sql1, sql2, sql3, sql4 WHERE "
strSQL = strSQL & "sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME] AND "
strSQL= strSQL & "sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID])"
strSQL= strSQL & " AND sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
strSQL= strSQL & "AND (((sql4.[User Id])='" & session(UserNam e) & "') AND ((sql4.Pwd)='" & Session(Passwor d) & "') "
strSQL= strSQL & " AND ((sql3.MAINTENA CE_STATUS)=Y))"
'strSQL="SELECT sql4.[User Id] as UId, sql4.Pwd as pwd, sql1.[ACCOUNT NAME] as AccName, "
'strSQL= strSQL & " sql1.[ACCOUNT ID] as AccID , sql2.[EMAIL ID] as email, sql3.[download URL] as surl "
'strSQL= strSQL & " sql3.MAINTENACE _STATUS, sql3.[CONTRACT NUMBER] "
'strSQL= strSQL & " FROM ((sql4 INNER JOIN sql1 ON sql4.[ACCOUNT NAME] = sql1.[ACCOUNT NAME]) "
'strSQL= strSQL & " INNER JOIN sql2 ON sql1.[ACCOUNT ID] = sql2.[ACCOUNT ID] ) "
'strSQL= strSQL & " INNER JOIN sql3 ON sql2.[ACCOUNT ID] = sql3.ACCOUNTID "
'strSQL= strSQL & " WHERE sql4.[User Id]='abd' AND sql4.[Pwd]='123'"
'strSQL= strSQL & " WHERE sql4.[User Id]='" & session("UserNa me") & "'"
'strSQL= strSQL & " AND sql4.[Pwd]='" & session("Passwo rd") & "'"
'strSQL= strSQL & " AND sql3.MAINTENACE _STATUS=Yes"
'strSQL ="SELECT * FROM sql1, sql2, sql3, sql4 where sql3.User Id=Session (UserName) And Pwd=Session(Pas sword)"
'strSQL =strSQL & "And sql4.ACCOUNT NAME=sql1.ACCOU NT NAME And sql1.ACCOUNT ID=sql3.ACCOUNT ID"
'strSQL =strSQL & " And sql2.EMAIL ID=sql3.ESD Contact WHERE (sql3.MAINTENAN CE CONTRACT)='Y'"
objRS.open strSQL,conn
'response.write strEmail
'response.write strSQL
If NOT objRS.EOF Then
'CREATE THE MESSAGE OBJECT
response.write objRS("UId")
response.write objRS("Pwd")
response.write objRS("AccName" )
response.write objRS("AccID")
response.write objRS("email")
response.write objRS("surl")
strEmail=objRS( "email")
'objRS("sql3.ES D Contact")
strUrl="http://" & objRS("surl")
'objRS("sql3.do wnload URL")
Set objMail = Server.CreateOb ject("CDO.Messa ge")
'This section provides the configuration information for the remote SMTP server.
objMail.Configu ration.Fields.I tem ("http://schemas.microso ft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).
objMail.Configu ration.Fields.I tem ("http://schemas.microso ft.com/cdo/configuration/smtpserver") ="mail.myserver .com"
objMail.Configu ration.Fields.I tem ("http://schemas.microso ft.com/cdo/configuration/smtpserverport" ) = 25
objMail.Configu ration.Fields.I tem ("http://schemas.microso ft.com/cdo/configuration/smtpusessl") = False 'Use SSL for the connection (True or False)
objMail.Configu ration.Fields.I tem ("http://schemas.microso ft.com/cdo/configuration/smtpconnectiont imeout") = 60
'End remote SMTP server configuration section
objMail.Subject = "Download your software electronically"
objMail.From = "myID@myserver. com"
objMail.To = strEmail
'objMail.MailFo rmat = 0 'MIME Format
'objMail.BodyFo rmat = 0
strMessageBody = "Hi, You can download from this site <a href='" & strUrl & "' > " & strUrl & "</a>"
strMessageBody = strMessageBody & "<br><br>Th ank You,"
strDisclaimer = "<br><br><p><fo nt color='#777777' size='-1.5' face='Arial'><i >This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.</i></font>"
'objMail.Body = strMessageBody & strDisclaimer
objMail.HTMLBod y= strMessageBody & strDisclaimer
response.write strMessageBody
'objMail.Send
objRS.Close
Set objRS = Nothing
%>
<table>
<tr><td class="normal"> Your request has been processed. Email address is: <%=strEmail%> Software URL is: <%=strUrl%>
</td></tr>
<tr><td class="normal"> </td></tr>
<tr><td class="normal"> Thank You</td></tr>
</table>
<%Else%>
<table>
<tr><td class="normal"> <font size='+1' color='Red'><b> Your request could not be processed. You will need to check the information that was submitted </b></font></td></tr>
</table>
<%End If%>
</td>
</tr>
</table>
<hr>
</body>
</html>
------------------------------------------
46
