sql - Asp Classic Connection String 500 - Internal server error

Question

Hello I'm trying to connect to the oracle data source and seems like when i test the code its giving me an error. Also, I believe it might be my data source path is wrong. Is there way to check the data source path in oracle or any kind of way? Thanks

    <!--INCLUDED FILE = reset_password.asp -->

    <%
    DIM strEmail
    strEmail = Request.Form("email")

    IF strEmail <> "" THEN
    %>
    <!--#INCLUDE VIRTUAL="/includes/connection.asp"-->
    <!-- ************SQL CONNECTION INSERT HERE*********************--> 
    <!-- *******
            Set objDB = Server.CreateObject("ADODB.Connection")
            objDB.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=c:mydatabase.mdb"
    **** -->

    conn.
    <%

    DIM objDB
    objDB = "SELECT email_addr,medacist_password FROM medacist_user WHERE email_addr = '" & strEmail & "'"
    Set objDB = Server.CreateObject("ADODB.Connection")
    objDB.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE= mmsg; Persist Security Info=True; User ID=mmsg; Password=langa;"

    IF objDB.EOF THEN
    Response.Write "That email address was not found in our database. Please click Back on your browser and enter the email address you registered with."
    ELSE
    DIM strPassword
    strPassword = objDB("medacist_password")

    DIM mail, objMail
    Set objMail = Server.CreateObject("CDONTS.NewMail") 
    objMail.From = "[email protected]"
    objMail.Subject = "Your Password"
    objMail.To = strEmail
    objMail.Body = "Here is your login password: " & strEmail 
    objMail.Send

    'Set objMail to nothing to destory the mail object'
    Set objMail = nothing

    Response.Write "Your password has been sent to your email address."
    END IF

    ELSE
    Response.Write "Please click Back on your browser and enter the email address you registered with."
    END IF

Answer 1

MICROSOFT.JET.OLEDB.4.0 is the OLEDB provider for MS Access. You'll need either an ODBC or an OLEDB connection string for Oracle. See this page for options

http://www.connectionstrings.com/oracle/

After that you need a recordset object as kloarubeek suggests above. A very simple way to do this would be as follows.

    DIM objDB, rs, rssql
    Set objDB = Server.CreateObject("ADODB.Connection")
    objDB.Open "[your connection string goes here]"
    rssql = "SELECT email_addr,medacist_password FROM medacist_user WHERE email_addr = '" & strEmail & "'"
    Set rs = objDB.Execute(rsSQL) 

Also I notice you are using CDONTS to send emails. It's deprecated and you won't find it on current versions of IIS by default. Look at CDOSYS instead

http://www.w3schools.com/asp/asp_send_email.asp

Finally, I recommend this page for anyone learning Classic ASP. It explains how to get error messages which are more useful than the basic 500 internal server error page.

http://www.chestysoft.com/asp-error-messages.asp

Edit

An example of a password retrieval script using CDOSYS and a recordset.

NB The CDO configuration will depend on your smtp server. Application("conn") means that my actual connection string is in a file called global.asa. This page actually connects to a SQL Server db, but the code should work with Oracle

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<% 
If InStr(request.form("username"),"@") > 0 Then
Set objMail = Server.CreateObject("CDO.Message")
Set iConfg = Server.CreateObject("CDO.Configuration")
Set Flds = iConfg.Fields
With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "127.0.0.1"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 587
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youremailusername"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "youremailpasword"
    .Update
End With
objMail.Configuration = iConfg
objMail.To = CStr(request.form("username"))
objMail.From = "[email protected]"
objMail.Subject = "Your login details"
objMail.TextBody = "Your login details are as follows " & vbcrlf & vbcrlf
set conn = Server.CreateObject("ADODB.Connection")
conn.open Application("conn")

sql = "select ContactEmailAddress, ContactAffiliateUsername, ContactAffiliatePassword from Contacts where ContactEmailAddress ='" & request.form("username") & "'"



set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql,conn,3,1

If rs.bof And rs.eof Then
response.redirect("invalidemailpage.asp?invalidemail=2")

Else 

objMail.To = RS("ContactEmailAddress")
objMail.TextBody = objMail.TextBody & "Username = " & RS("ContactAffiliateUsername") & ", Password = " & RS("ContactAffiliatePassword") & vbcrlf


End If 

objMail.Send
Set objMail = Nothing

rs.close
set rs = nothing
conn.close
set conn = nothing 
response.redirect("login.asp?sentpassword=1")
Else
response.redirect("invalidemailpage.asp?invalidemail=1")
End If

%>