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....Please 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=VBScript%>
<% 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_START_DATE_C, MAINTENANCE_EXPIRATION_DATE_C,downloadURL
Dim ESD_Contact, MAINTENANCE_STATUS, User_ID, Pwd
'End Variable Declaration
%>
<html><head><title>Posting Mail</title></head>
<body bgcolor=acqua leftmargin='1' topmargin='1'><table width='100%' height='100%' cellspacing='0' cellpadding='2'>
<tr align='left'><td 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(ReplaceSpecial(Request.Form("User_Email")))
Set conn = Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "DSN=SQL"
Set objRS = Server.CreateObject("ADODB.Recordset")
session("UserName")="abd"
session("Password")="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(UserName) & "') AND ((sql4.Pwd)='" & Session(Password) & "') "
strSQL= strSQL & " AND ((sql3.MAINTENACE_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("UserName") & "'"
'strSQL= strSQL & " AND sql4.[Pwd]='" & session("Password") & "'"
'strSQL= strSQL & " AND sql3.MAINTENACE_STATUS=Yes"
'strSQL ="SELECT * FROM sql1, sql2, sql3, sql4 where sql3.User Id=Session (UserName) And Pwd=Session(Password)"
'strSQL =strSQL & "And sql4.ACCOUNT NAME=sql1.ACCOUNT NAME And sql1.ACCOUNT ID=sql3.ACCOUNT ID"
'strSQL =strSQL & " And sql2.EMAIL ID=sql3.ESD Contact WHERE (sql3.MAINTENANCE 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.ESD Contact")
strUrl="http://" & objRS("surl")
'objRS("sql3.download URL")
Set objMail = Server.CreateObject("CDO.Message")
'This section provides the configuration information for the remote SMTP server.
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'Send the message using the network (SMTP over the network).
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="mail.myserver.com"
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False 'Use SSL for the connection (True or False)
objMail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'End remote SMTP server configuration section
objMail.Subject = "Download your software electronically"
objMail.From = "myID@myserver.com"
objMail.To = strEmail
'objMail.MailFormat = 0 'MIME Format
'objMail.BodyFormat = 0
strMessageBody = "Hi, You can download from this site <a href='" & strUrl & "' > " & strUrl & "</a>"
strMessageBody = strMessageBody & "<br><br>Thank You,"
strDisclaimer = "<br><br><p><font 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.HTMLBody= 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
