Somebody Please Help Me Convert this ASP to VBS

[andy.mcvicker]

Hi Gang

I'm not sure what needs to be changed when converting an asp to vbs.
I'm not sure if I can with my code below. Can someone PLEASE convert
the text below to vbs?

Thanks a million.
Andy


<!--
METADATA
TYPE="typelib"
UUID="CD000000-8B95-11D1-82DB-00C04FB1625D"
NAME="CDO for Windows 2000 Library"
-->

<%
' Create a connection to the Notification database and open it.
Set NotificationDatabaseConn = Server.CreateObject("ADODB.Connection")
NotificationDatabaseConn.Open "Data Source=" &
Server.Mappath("../cadim/data/notifications.mdb") &
";Provider=Microsoft.Jet.OLEDB.4.0;"

lcSQL = "SELECT CADIMUser, CADIMUserPassword, SMTPServer, eMailAdmin
FROM SystemConfig"
Set RSSystemDefaults = NotificationDatabaseConn.Execute(lcSQL)

lcCADIMUser = RSSystemDefaults ("CADIMUser")
lcCADIMUserPassword = RSSystemDefaults ("CADIMUserPassword")
lcSMTPServer = RSSystemDefaults ("SMTPServer")
lceMailAdmin = RSSystemDefaults ("eMailAdmin")

' Create a connection to the CADIM database and open it.
Set CADIMDatabaseConn = Server.CreateObject("ADODB.Connection")
CADIMDatabaseConn.Open "dsn=CADIMProductionDB;uid=" & lcCADIMUser &
";pwd=" & lcCADIMUserPassword & ";"

' Delete all the previous notifications
lcSQL = "DELETE * FROM Notifications"
Set RSDeleteNotifications = NotificationDatabaseConn.Execute(lcSQL)


' Create a simple SQL Select statement for testing.
lcSQL = "SELECT C_ID, ELEM_ID, S010000_AEND_ART_ENG FROM
PLMAD_PROD.T_EWO_DAT WHERE S010000_ORG_EINH='013060' AND (LEV_IND =
'605' OR LEV_IND = '610') AND S010000_AEND_ART_ENG <> 'Normal change A
doc'"

Set RSECOList = CADIMDatabaseConn.Execute(lcSQL)

Do While Not RSECOList.EOF
lcECOType = RSECOList("S010000_AEND_ART_ENG")
lcECOID = RSECOList("C_ID")
lcECONum = RSECOList("ELEM_ID")

lcSQL = "SELECT BEARB_PRS_ID, FUNKTION, BEREICH, MAIL_AUSG, OK_KZ
FROM PLMAD_PROD.T_S010000EWOM WHERE C_ID_1 = '" & lcECOID & "' AND
OK_KZ = '_'"
Set RSECODetails = CADIMDatabaseConn.Execute(lcSQL)

lcApplicantSentRequestForApproval = "False"
Do While Not RSECODetails.EOF
If Not IsNull(RSECODetails("MAIL_AUSG")) Or
Len(RSECODetails("MAIL_AUSG")) <> 0 Then
lcApplicantSentRequestForApproval = "True"
End If
RSECODetails.MoveNext
Loop


' Loop through the details again now we know someone has been
' notified once to approve the change and build the notification
table

If lcApplicantSentRequestForApproval = "True" Then
RSECODetails.MoveFirst
Do While Not RSECODetails.EOF

lcDeptFunction = RSECODetails("BEREICH")
lcJobRole = RSECODetails("FUNKTION")

If lcJobRole <> "Applicant" Then

lcSQL = "SELECT COUNT(ecotype) AS ecocount FROM
notificationconfig " & _
"WHERE ecotype = '" & lcECOType & "' AND jobrole =
'" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
Set RSECOCount =
NotificationDatabaseConn.Execute(lcSQL)

If RSECOCount("ecocount") > 0 Then

lcSQL = "SELECT sequence, changestage, ecotype FROM
notificationconfig " & _
"WHERE ecotype = '" & lcECOType & "' AND jobrole =
'" & lcJobRole & "' AND deptfunction = '" & lcDeptFunction & "'"
Set RSECOConfigDetails =
NotificationDatabaseConn.Execute(lcSQL)

lcSequence = RSECOConfigDetails("sequence")
lcChangeStage = RSECOConfigDetails("changestage")
lcECOType = RSECOConfigDetails("ecotype")

lcECOPersonId = RSECODetails("BEARB_PRS_ID")
lcSQL = "SELECT S_USER, S_FIRST_NAME, S_EMAIL FROM
PLMAD_PROD.T_PRS_DAT WHERE EDB_ID = '" & lcECOPersonId & "'"
Set RSPersonDetails = CADIMDatabaseConn.Execute(lcSQL)

lcFirstName = RSPersonDetails("S_FIRST_NAME")
lcLastName = RSPersonDetails("S_USER")
lceMailAddress = RSPersonDetails("S_EMAIL")


'Insert the new notification into the notification
table

lcSQL = "INSERT INTO notifications " &_
"(econum, changestage, sequence, firstname, lastname,
emailaddress, jobrole, deptfunction, ecotype) " &_
"VALUES ('" & lcECONum & "', '" & lcChangeStage & "', " &
lcSequence & ", '" & lcFirstName & "', '" & lcLastName & "', '" &
lceMailAddress & "', '" & lcJobRole & "', '" & lcDeptFunction & "', '"
& lcECOType & "')"
Set RSInsert = NotificationDatabaseConn.Execute(lcSQL)

Else

' There was a dept function and/or job role that was not
recognized by
' the notificationconfig table.

lcBodyError = "<html><head><meta http-equiv='Content-Language'
content='en-us'><meta http-equiv='Content-Type' content='text/html;
charset=windows-1252'><title>CADIM EC Action
Requirements</title></head><body><font face='Arial' style='FONT-SIZE:
9pt'>"
lcBodyError = lcBodyError & "The following signature is not a
default signature. The user will not be sent a notification and this
needs to be corrected.<br><br>"
lcBodyError = lcBodyError & "<b>ECO Number: </b>" & lcECONum &
"<br>"
lcBodyError = lcBodyError & "<b>Department Function: </b>" &
lcDeptFunction & "<br>"
lcBodyError = lcBodyError & "<b>Job Role: </b>" & lcJobRole &
"<br>"
lcBodyError = lcBodyError & "</font></body></html>"

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = lcSMTPServer
.Update
End With

Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
Set .Configuration = cdoConfig
.From = lceMailAdmin
.To = "(e-mail address removed)"
.Subject = "CADIM Comm List Member Can't Be Notified"
.HTMLBody = lcBodyError
.Send
End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing

End If

End If

RSECODetails.MoveNext
Loop
End If

RSECOList.MoveNext
Loop


' Setup the send status for each notification. We only want to send
' an email to a user once with all the action.

lcSQL = "SELECT * FROM notifications ORDER BY econum, sequence"
Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)

lcOldSequence = ""
lcOldECONum = ""

Do While Not RSNotifications.EOF
lcBodyTableRecs = ""
lcECONum = ""
lcChangeStage = ""
lcECOType = ""
lcAction = ""

If lcOldECONum <> RSNotifications("econum") Then
lcOldSequence = RSNotifications("sequence")
End If

If lcOldSequence = RSNotifications("sequence") Then
lcECONum = RSNotifications("econum")
lcChangeStage = RSNotifications("changestage")
lcECOType = RSNotifications("ecotype")
lcJobRole = RSNotifications("jobrole")
lcDeptFunction = RSNotifications("deptfunction")
lcSequence = RSNotifications("sequence")
lcFirstName = RSNotifications("firstname")
lcLastName = RSNotifications("lastname")
lceMailAddress = RSNotifications("emailaddress")

lcSQL = "UPDATE notifications SET sendstatus = 'Y' " &_
"WHERE econum = '" & lcECONum & "' And " & _
"changestage = '" & lcChangeStage & "' And " & _
"sequence = " & lcSequence & " And " & _
"jobrole = '" & lcJobRole & "' And " & _
"deptfunction = '" & lcDeptFunction & "' And " & _
"firstname = '" & lcFirstName & "' And " & _
"lastname = '" & lcLastName & "' And " & _
"emailaddress = '" & lceMailAddress & "'"

Set RSUpdate = NotificationDatabaseConn.Execute(lcSQL)
End If

lcOldECONum = RSNotifications("econum")
' lcOldSequence = RSNotifications("sequence")
RSNotifications.MoveNext
Loop



'Send out the notifications.

lcSQL = "SELECT header, body, bodytable, footer FROM eMailConfig"
Set RSeMailDefaults = NotificationDatabaseConn.Execute(lcSQL)

lcHeader = RSeMailDefaults("header")
lcBody = RSeMailDefaults("body")
lcBodyTable = RSeMailDefaults("bodytable")
lcFooter = RSeMailDefaults("footer")


lcSQL = "SELECT * FROM notifications WHERE sendstatus = 'Y' ORDER BY
emailaddress, sequence DESC"
Set RSNotifications = NotificationDatabaseConn.Execute(lcSQL)


lcOldeMail = ""
lceMailBody = ""
lcBodyTableRecs = ""
lcFirst = "True"

Do While Not RSNotifications.EOF

If lcOldeMail <> RSNotifications("emailaddress") And lcFirst <> "True"
Then

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = lcSMTPServer
.Update
End With

Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
Set .Configuration = cdoConfig
.From = lceMailAdmin
.To = "(e-mail address removed)"
.Cc = "(e-mail address removed)"
.Subject = lceMailSubject
.HTMLBody = lceMailBody
.Send
End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing

lceMailBody = ""
lcBodyTableRecs = ""
lceMailSubject = ""

End If

lcFirst = "False"
lceMailSubject = "Required CADIM EC Actions For " &
RSNotifications("firstname") & " " & RSNotifications("lastname")
lcECONum = RSNotifications("econum")
lcChangeStage = RSNotifications("changestage")
lcECOType = RSNotifications("ecotype")
lcAction = "Review as the " & RSNotifications("jobrole") & " - " &
RSNotifications("deptfunction") & " representative."

lcBodyTableRecs = lcBodyTableRecs & "<tr>"
lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
style='FONT-SIZE: 9pt'>" & lcECONum & "</font></td>"
lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
style='FONT-SIZE: 9pt'>" & lcAction & "</font></td>"
lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
style='FONT-SIZE: 9pt'>" & lcECOType & "</font></td>"
lcBodyTableRecs = lcBodyTableRecs & "<td><font face='Arial'
style='FONT-SIZE: 9pt'>" & lcChangeStage & "</font></td>"
lcBodyTableRecs = lcBodyTableRecs & "</tr>"

lceMailBody = lcHeader
lceMailBody = lceMailBody & RSNotifications("firstname")
lceMailBody = lceMailBody & lcBody
lceMailBody = lceMailBody & lcBodyTable
lceMailBody = lceMailBody & lcBodyTableRecs
lceMailBody = lceMailBody & lcFooter


lcOldeMail = RSNotifications("emailaddress")
RSNotifications.MoveNext
Loop


'Get the last user to send the email to

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = lcSMTPServer
.Update
End With

Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
Set .Configuration = cdoConfig
.From = lceMailAdmin
.To = "(e-mail address removed)"
.Cc = "(e-mail address removed)"
.Subject = lceMailSubject
.HTMLBody = lceMailBody
.Send
End With

Set cdoMessage = Nothing
Set cdoConfig = Nothing


' Close the connection to the databases
CADIMDatabaseConn.Close
NotificationDatabaseConn.Close
%>

 

[Evertjan.]

wrote on 29 jun 2006 in microsoft.public.inetserver.asp.general:

I'm not sure what needs to be changed when converting an asp to vbs.

ASP is a platform for writing serverside code in VBS or Jscript [or ...].

VBS is a scripting language.

You cannot convert a platform INTO one of it's supported languages.

 

[Stefan Berglund]

On 29 Jun 2006 21:22:41 GMT, "Evertjan." <[email protected]>
wrote:

wrote on 29 jun 2006 in microsoft.public.inetserver.asp.general:

I'm not sure what needs to be changed when converting an asp to vbs.

ASP is a platform for writing serverside code in VBS or Jscript [or ...].

VBS is a scripting language.

You cannot convert a platform INTO one of it's supported languages.

Isn't ASP also an acronym for active server page? Then you'd have to
take it's meaning from context and in this case the OP was obviously
referring to an asp page. Ah, the Department of Redundancy Department.

 

[Aaron Bertrand [SQL Server MVP]]

That's a pretty narrow-minded answer -- you must be taking lessons from
Celko? Of course, Andy is asking about converting an ASP page (not the
platform, duh) to a VBS script. Which is a pretty common task, in fact one
I have performed many times.

 

[Aaron Bertrand [SQL Server MVP]]

Andy, the most complicated part is getting all of your constants from the
CDO library defined in the VBS script, since you cannot use that metadata
tag in VBS as far as I know. So far the only constant I see in use is
cdoSendUsingPort, and you can simply change these to a hard-coded value of
2, or at the beginning of the VBS script, place this line:

const cdoSendUsingPort = 2

The most common pitfalls when switching over:
- forgetting to change response.write to either msgbox or
logfile.writeline
- forgetting to take out any HTML, css or client-side script output
And forgetting that you're no longer in the context of a "server":
- changing references such as server.createobject to createobject
- removing things like server.transfer / response.redirect - you're no
longer in a browser
- similarly with server.mappath, this no longer makes sense

Basically, most of your code looks fine, except changing things like
Server.CreateObject("ADODB.Connection") to CreateObject("ADODB.Connection")
and changing Server.MapPath("../cadim/data/notifications.mdb") to a local
path (e.g. c:\inetpub\wwwroot\cadim\data\notifications.mdb).