Dear experts,
i have 2 table in my database which are "Workorder" & "Employees"."Workorder" contain a field call SMSStatus & EmployeeID2 where my "Employees" table contain a field call EmployeeNO. EmployeeNO and EmployeeID2 is exactly the same datatype and value. Below script will first look in "Workorder" table and search for SMSStatus=0 and retrieve the data that i want and send the sms out to the technician(technician phone number is from Employees table). After the sms has been send out, the SMSstatus will update to 1. Whole script work fine but there are some bugs in btw the code. i cant retrieve rs.field.item("EmployeeID2") data out from the recordset and therefore i cant get the technician handphone! Question is why i cant retrieve the rs.field.item("EmployeeID2")? Even though I have double check the field and make sure the field is exist in the database , but stil fail to retireve that field. I also tried to output the field onto the screen b4 the rs.eof execute but stil fail to do it. Help ! WHY?
<!-- #include file="config.asp" -->
<META HTTP-EQUIV="refresh" CONTENT="<%Response.Write(Rate)%>">
<%
dim Time1, Time2
Dim JID, SN, Req, Code, Logtime, ESI, ESO
set oConn=Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
set rs2 = Server.CreateObject("ADODB.Recordset")
connectionStr = "DSN="& SMSDSN &"; UID="& UserID &";PWD="& Password&";"
oConn.Open connectionStr
sql = "SELECT * FROM " & SMSWorkorder & " WHERE SMSStatus = '0'"
rs.Open sql, oConn
do until rs.EOF
Time1 = Second(Time)
Time2 = Second(Time)
Result1 = "SMS == JID: " & rs.Fields.Item("WorkorderNo") & "\n" & "SN: " & rs.Fields.Item("LocationNo") & "\n" & "Req: " & rs.Fields.Item("ProblemDescription") & "\n" & "Code: " & rs.Fields.Item("WorkStatus") & "\n" & "Lt: " & rs.Fields.Item("DateReceived") & "\n" & "ESI: " & rs.Fields.Item("EstDateStart") & "\n" & "ESO: " & rs.Fields.Item("EstDateEnd") & "<br>"
Response.Write (Result1)
'add checking
if not rs.eof then
JID = rs.Fields.Item("WorkorderNo")
SN = rs.Fields.Item("LocationNo")
Req = rs.Fields.Item("ProblemDescription")
Code = rs.Fields.Item("WorkStatus")
Logtime = rs.Fields.Item("DateReceived")
ESI = rs.Fields.Item("EstDateStart")
ESO = rs.Fields.Item("EstDateEnd")
else
Response.Write ("Event table database empty for <br>" & rs.Fields.Item("WorkorderNo") )
end if
sql2 = "SELECT * FROM " & SMSTechnician & " WHERE EmployeeNo = '" & rs.Fields.Item("EmployeeID2") & "'"
rs2.Open sql2, oConn
if not rs2.eof then
Sender = rs2.Fields(9)
Response.Write ("Send SMS:" & rs2.Fields(9)& " ")
end if
rs2.close
k = 0
while k = 0
Time2 = Second(Time)
if Time2 - Time1 = 1 then
Response.Write ("Successful!<br>")
%>
<!-- #include file="loopsms.asp" -->
<%
sql3 = "UPDATE " & SMSWorkorder & " SET SMSStatus = '1' WHERE WorkorderNo = '" & rs.Fields.Item("WorkorderNo") & "'"
oConn.Execute sql3
k = 1
end if
wend
rs.MoveNext
loop
rs.close
oConn.close
%>
