Error on page, but when you navigate back and resubmit, no Error

[Joe]

I am trying to track down an error I am having on one of my
interfaces. The form takes the data, formats it and runs it through a
SP on SQL Server 2000, the SP outputs a number depending on whether or
not the insert was made and returns to the application to show the
user a message.

Here is my code,

If Request.Form("action") = "insert" Then

set conn = CreateObject("ADODB.Connection")
conn.open MM_EmpCore_STRING

'Submit data to SP if submit is clicked
dim
varPosID,varRoleTitle,varRoleCode,varSWVTCTitle,varDepartment,varBuilding,varOtherBuilding,varShift,varWorkersCompCode,varPayBand,varFLSA,varStatus,varSupervisorPosID,varHIPAALevel,varGrade,varStep,varEEOCode,varSupervisor

varPosID = CStr(Request.Form("PosID"))
varRoleTitle = CStr(Request.Form("RoleTitle"))
varRoleCode = CStr(Request.Form("RoleCode"))
varSWVTCTitle = CStr(Request.Form("SWVTCTitle"))
varDepartment = CInt(Request.Form("DeptID"))
varBuilding = CInt(Request.Form("BuildingID"))
varOtherBuilding = CInt(Request.Form("OtherBuildingID"))
varShift = CStr(Request.Form("Shift"))
varWorkersCompCode = CInt(Request.Form("WorkersCompCode"))
varPayBand = CInt(Request.Form("PayBand"))
varFLSA = CStr(Request.Form("FLSA"))
varStatus = CStr(Request.Form("Status"))
varEEOCode = CStr(Request.Form("EEOCode"))
varSupervisorPosID = CStr(Request.Form("SupervisorID"))
varHIPAALevel = CInt(Request.Form("HIPAALevel"))
varGrade = Request.Form("Grade")
If varGrade <> "" Then
varGrade = CInt(varGrade)
Else
varGrade = 0
End If
varStep = Request.Form("Step")
If varStep <> "" Then
varStep = CInt(varGrade)
Else
varStep = 0
End If
varSupervisor = CBool(Request.Form("Supervisor"))

set rs = createobject("adodb.recordset")

'Response.Write("InsertPosition " & varPosID & "," & varDepartment &
"," & varBuilding & "," & varOtherBuilding & "," & varSupervisorPosID
& "," & varRoleTitle & "," & varRoleCode & "," & varEEOCode & "," &
varSWVTCTitle & "," & varShift & "," & varWorkersCompCode & "," &
varHIPAALevel & "," & varGrade & "," & varStep & "," & varPayBand &
"," & varFLSA & "," & varStatus & "," & varSupervisor)
conn.InsertPosition
varPosID,varDepartment,varBuilding,varOtherBuilding,varSupervisorPosID,varRoleTitle,varRoleCode,varEEOCode,varSWVTCTitle,varShift,varWorkersCompCode,varHIPAALevel,varGrade,varStep,varPayBand,varFLSA,varStatus,varSupervisor,
rs

If IsEmpty(rs) = False Then
dim varOutput
varOutput = rs(0)
End If

'Close Connection
conn.close: set conn = nothing

dim varMsg,varNotInserted
varNotInserted = 0

Select Case varOutput
Case 2
'Employee inserted successfully, so redirect
Response.Redirect("http://swvtc06/swvtc/DB/Emp/EmpCore_New/
default.asp?action=AddPosition")
Case 0
varMsg = "The position was not added because there is already a
position with that position number. You can either change the
position number and try to add it again, or you can go <a
href=""http://swvtc06/swvtc/DB/Emp/EmpCore_New/changePosition.asp?
PosID=" & varPosID & """>change the existing position</a>."
varNotInserted = 1
Case Else
Response.Write("<" & "script>alert('*Error Inserting Position*
Contact Drew Laing at extension 311');")
Response.Write("<" & "/script>")
End Select
End If

On the line that specifies the output number, varOutput = rs(0), I get
the following error, "Item cannot be found in the collection
corresponding to the requested name or ordinal", but if I click Back
and then click Submit again, the application works fine (either
returns a message that the Position ID is already used, or redirects).

What the heck am I doing wrong?

Thanks,
Drew

 

[Bob Barrows]

I am trying to track down an error I am having on one of my
interfaces. The form takes the data, formats it and runs it through a
SP on SQL Server 2000, the SP outputs a number depending on whether or
not the insert was made and returns to the application to show the
user a message.

Here is my code,

If Request.Form("action") = "insert" Then

set conn = CreateObject("ADODB.Connection")
conn.open MM_EmpCore_STRING

'Submit data to SP if submit is clicked
varPosID,varDepartment,varBuilding,varOtherBuilding,varSupervisorPosID,v
arRoleTitle,varRoleCode,varEEOCode,varSWVTCTitle,varShift,varWorkersComp
Code,varHIPAALevel,varGrade,varStep,varPayBand,varFLSA,varStatus,varSupe
rvisor,
rs

If IsEmpty(rs) = False Then
dim varOutput
varOutput = rs(0)

On the line that specifies the output number, varOutput = rs(0), I get
the following error, "Item cannot be found in the collection
corresponding to the requested name or ordinal", but if I click Back
and then click Submit again, the application works fine (either
returns a message that the Position ID is already used, or redirects).

What the heck am I doing wrong?

Your procedure may be sending informational messages in the form of
closed recordsets due to your failure to include the line
SET NOCOUNT ON
in the procedure. Make sure that line is there

It is insufficient to check for IsEmpty. You need to check the EOF
property of the recordset.

 

[Joe]

Your procedure may be sending informational messages in the form of

closed recordsets due to your failure to include the line
SET NOCOUNT ON
in the procedure. Make sure that line is there

It is insufficient to check for IsEmpty. You need to check the EOF
property of the recordset.

DOH! Thanks Bob, I wasn't even looking in that direction... also the
Employee form works fine, and upon further investigation, it has SET
NOCOUNT ON....

Thanks a bunch!

Drew