«

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,va rDepartment,varBuilding,varOtherBuilding,varShift, varWorkersCompCode,varPayBand,varFLSA,varStatus,va rSupervisorPosID,varHIPAALevel,varGrade,varStep,va rEEOCode,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,varOtherBuildin g,varSupervisorPosID,varRoleTitle,varRoleCode,varE EOCode,varSWVTCTitle,varShift,varWorkersCompCode,v arHIPAALevel,varGrade,varStep,varPayBand,varFLSA,v arStatus,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

Joe wrote:
> 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
<snip>

> conn.InsertPosition
>
varPosID,varDepartment,varBuilding,varOtherBuildin g,varSupervisorPosID,v
arRoleTitle,varRoleCode,varEEOCode,varSWVTCTitle,v arShift,varWorkersComp
Code,varHIPAALevel,varGrade,varStep,varPayBand,var FLSA,varStatus,varSupe
rvisor,
> rs
>
> If IsEmpty(rs) = False Then
> dim varOutput
> varOutput = rs(0)

<snip>

> 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.

--
HTH,
Bob Barrows

> 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.
>
> --
> HTH,
> Bob Barrows

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