CONTENT OF PIRCONNECTION.ASP

<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
'MM_capacity_STRING = "dsn=Test_UGN_DB_DSN;uid=sa;pwd=tiger;"
MM_capacity_STRING = "dsn=UGN_DB_DSN;uid=sa;pwd=tiger;"
%>


You need to create DSN: UGN_DB_DSN in control Panel, ODBC.

then use following code to play with Database.


----



<%
MM_editConnection = MM_capacity_STRING
set conn = Server.CreateObject("ADODB.Command")
conn.ActiveConnection = MM_editConnection

set conn_temp = Server.CreateObject("ADODB.Connection")
conn_temp.open = MM_editConnection
set rs = Server.Createobject("ADODB.RecordSet")


'Searching Role of User
set rs = Server.Createobject("ADODB.RecordSet")
RoleStr = "Select RoleId from User_Role_M where userid = '" & UserId & "' and roleid like 'PIR%'"
'Response.write (RoleStr)
rs.open RoleStr , conn_temp
If not rs.EOF then
UserRole = rs("RoleID")
Else
UserRole = "Unknown"
End if
rs.Close




%>


<%

'Insert OR Update Record
IF Actiontobetaken = "InsertRec" then
str = "select max(PIRNo) from PIR_M"
rs.open str, conn_temp
If rs.eof then
PIRNo = 1
Else
PIRNo = rs(0) + 1
End if
rs.close

'Inserting Record
'Original SqlText = " Insert into PIR_M (PIRNo, PartType, InitiatedBy, InitiatedDate, OEMName, CustPartNumber, CustNo, CustAbbrev, DestNo, DestAbbrev, BPCSPartNumber, BPCSDescription, PartName, ManuUGNFacility, PartWeight, StdPackQty, ContainerSize, TareWeight, SalesPriceinsystem) values (" & PirNo & " , '" & PartType & "', '" & InitiatedBy & "', GetDate(), '" & OEMName & "', '" & CustPartNumber & "', " & CustNo & ", '" & CustAbbrev & "'," & DestNo & ", '" & DestAbbrev & "','" & BPCSPartNumber & "','" & BPCSDescription & "', '" & PartName & "', " & ManuUGNFacility & ", '" & PartWeight & "', " & StdPackQty & ", '" & ContainerSize & "', " & TareWeight & ", " & SalesPriceinsystem & ")"
SqlText = " Insert into PIR_M (PIRNo, PartType, InitiatedBy, InitiatedDate, OEMName, CustPartNumber, CustNo, CustAbbrev, DestNo, DestAbbrev, BPCSPartNumber, BPCSDescription, PartName, ManuUGNFacility, PartWeight, StdPackQty, ContainerSize, TareWeight, SalesPriceinsystem, QECheckbox, QEInitials, QEDate, PlantCntrlrCheckbox, PlantCntrlrInitials, PlantCntrlrDate, BillingCheckbox, BillingInitials, BillingDate, QE2Checkbox, QE2Initials, QE2Date, PackagingCheckbox, PackagingInitials, PackagingDate, EDICheckbox, EDIInitials, EDIDate ) values (" & PirNo & " , '" & PartType & "', '" & InitiatedBy & "', GetDate(), '" & OEMName & "', '" & CustPartNumber & "', " & CustNo & ", '" & CustAbbrev & "'," & DestNo & ", '" & DestAbbrev & "','" & BPCSPartNumber & "', '" & BPCSDescription & "', '" & PartName & "', " & ManuUGNFacility & ", '" & PartWeight & "', " & StdPackQty & ", '" & ContainerSize & "', " & TareWeight & ", " & SalesPriceinsystem & ", " & QECheckbox & ", " & QEInitials & ", '" & QEDate & "', " & PlantCntrlrCheckbox & ", " & PlantCntrlrInitials & ", '" & PlantCntrlrDate & "', " & BillingCheckbox & ", " & BillingInitials & ", '" & BillingDate & "', " & QE2Checkbox & ", " & QE2Initials & ", '" & QE2Date & "', " & PackagingCheckbox & ", " & PackagingInitials & ", '" & PackagingDate & "', " & EDICheckbox & ", " & EDIInitials & ", '" & EDIDate & "' )"
'response.write(" Insert ::: " & SqlText)
'response.end
conn.CommandText = SqlText
conn.execute

ELSE 'UpdateRec
PIRNo = Request.form("PIRNo")
SqlText = " Update PIR_M Set PartType = '" & PartType & "', InitiatedBy = '" & InitiatedBy & "', InitiatedDate = '" & InitiatedDate & "', OEMName = '" & OEMName & "', CustPartNumber = '" & CustPartNumber & "', CustNo = " & CustNo & ", CustAbbrev = '" & CustAbbrev & "', DestNo = " & DestNo & ", DestAbbrev = '" & DestAbbrev & "', BPCSPartNumber = '" & BPCSPartNumber & "', BPCSDescription = '" & BPCSDescription & "', PartName = '" & PartName & "', ManuUGNFacility = " & ManuUGNFacility & ", PartWeight = '" & PartWeight & "', StdPackQty = " & StdPackQty & ", ContainerSize = '" & ContainerSize & "', TareWeight = " & TareWeight & ", SalesPriceinsystem = " & SalesPriceinsystem & ", QECheckbox = " & QECheckbox & ", QEInitials = " & QEInitials & ", QEDate = '" & QEDate & "', PlantCntrlrCheckbox = " & PlantCntrlrCheckbox & ", PlantCntrlrInitials = " & PlantCntrlrInitials & ", PlantCntrlrDate = '" & PlantCntrlrDate & "', BillingCheckbox = " & BillingCheckbox & ", BillingInitials = " & BillingInitials & ", BillingDate = '" & BillingDate & "', QE2Checkbox = " & QE2Checkbox & ", QE2Initials = " & QE2Initials & ", QE2Date = '" & QE2Date & "', PackagingCheckbox = " & PackagingCheckbox & ", PackagingInitials = " & PackagingInitials & ", PackagingDate = '" & PackagingDate & "', EDICheckbox = " & EDICheckbox & ", EDIInitials = " & EDIInitials & ", EDIDate = '" & EDIDate & "' Where PIRNo = " & PIRNo
'response.write(" Update ::: " & SqlText)
'response.end

conn.CommandText = SqlText
conn.execute

End if



%>

<%

'Send email to QE Enginner Email

'Searching From Address
set UGNQEEmployeeEmail = Server.Createobject("ADODB.RecordSet")
UGNQEEmployeeStr = "Select empemail from employee_M where EmpId = " & EDIInitials
UGNQEEmployeeEmail.open UGNQEEmployeeStr , conn_temp
EmailFromAddress = UGNQEEmployeeEmail("empemail")
UGNQEEmployeeEmail.close

'Searching To Address
set UGNQEEmployeeEmail = Server.Createobject("ADODB.RecordSet")
UGNQEEmployeeStr = "Select empemail from employee_M where EmpId = " & QEInitials
UGNQEEmployeeEmail.open UGNQEEmployeeStr , conn_temp
EmailToAddress = UGNQEEmployeeEmail("empemail")
UGNQEEmployeeEmail.close

Subject = "PIR Notification for PIR # " & PirNo
Body = "EDI Coordinator has created PIR. PIR Notification for PIR #: " & PirNo & ". For BPCS Part Number: " & BPCSPartNumber & ", BPCS Description: " & BPCSDescription & ", Customer Part Number: " & CustPartnumber

'response.write(" From address = " & EmailFromAddress)
'response.write(" To address = " & EmailToAddress)

Set MailObj = Server.CreateObject ("CDONTS.NewMail")
MailObj.BodyFormat = 1
MailObj.MailFormat = 0

'MailObj.Send EmailFromAddress, EmailToAddress, Subject, Body
Response.write ("Email sent to QE Person successfully. From: " & EmailFromAddress & ", To: " & EmailToAddress )


%>