Desperately Seeking Data (ASP/ADO)

I am trying to retrieve a recently inserted record. The insert (setDept) works. The get (getDeptByName) fails (EOF) only when retrieving newly inserted records. It works otherwise. What am I doing wrong? Please Help!!!

THIS CODE IS INCLUDED:
--------------------------------
<SCRIPT LANGUAGE="vbscript" RUNAT="SERVER">
private oConn, rs, errNum, errDesc

'//////////////////////////////////////////////////////////////
' errHandler
'//////////////////////////////////////////////////////////////
function errHandler()
errNum = " " & errNum & Err.Number
errDesc = errDesc & Err.Description
Err.Clear
end function ' end errHandler

'//////////////////////////////////////////////////////////////
' establish a connection to the database - read
'//////////////////////////////////////////////////////////////
function openConn()
On Error Resume Next
errNum = ""
errDesc = ""

set oConn = server.CreateObject("ADODB.Connection")
If oConn Is Nothing Then
set oConn = server.CreateObject("ADODB.Connection")
With oConn
.Mode = adModeRead
.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Mike2\teams97.mdb; Persist Security Info=False"
End With
ElseIf oConn.State = 0 Then
set oConn = server.CreateObject("ADODB.Connection")
With oConn
.Mode = adModeRead
.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Mike2\teams97.mdb; Persist Security Info=False"
End With
End If

if err <> 0 then
errhandler
openConn = false
exit function
end if
openConn = true
end function ' end openConn

'//////////////////////////////////////////////////////////////
' get Dept by Dept name
'//////////////////////////////////////////////////////////////
function getDeptByName(ByVal deptName)
On Error Resume Next

errNum = ""
errDesc = ""
set oConn = nothing
if Not openConn then
exit function
end if

set rs = server.createObject("ADODB.Recordset")
set rs = oConnDept.Execute("select * from dept where dept_name = '" & deptName & "'")
if err <> 0 then
errhandler
getDeptByName = false
exit function
end if
getDeptByName = true
end function ' end getDeptByName

'//////////////////////////////////////////////////////////////
' set Dept
'//////////////////////////////////////////////////////////////
function setDept(deptName)
On Error Resume Next
errNum = ""
errDesc = ""
dim lAffected, oCmd, oParam
if Not openConn then
exit function
end if
set oCmd = server.createobject("ADODB.Command")
oCmd.CommandText = "spInsDept"
oCmd.CommandType = 4
set oCmd.ActiveConnection = oConn
set oParam = oCmd.CreateParameter ("[newEntry]", 129, 1, 35, deptName)
oCmd.Parameters.Append oParam
oCmd.Execute lAffected, , adExecuteNoRecords
if err <> 0 then
errhandler
setDept = false
exit function
end if
setDept = lAffected
end function ' end setDept

</SCRIPT>

THIS COSE IS THE ASP:
-----------------------------
<%@ LANGUAGE=VBScript %>
<%
Response.Expires = 0
Response.Buffer = true
%>
<!-- #INCLUDE FILE="teamsutil.js" -->
<!-- #INCLUDE FILE="dbutils.vbs" -->
<%
If Request.ServerVariables("CONTENT_LENGTH") <> 0 then
dim nRecs, success
success = true

'handle dept
if Request.Form("dept") = "-1" Then
nRecs = setDept(Request.Form("other_dept"))
if nRecs = 0 Then
Response.Write ("Unable to Create a new Department record (" & errNum & ": " & errDesc & "). ")
success = false
elseif getDeptByName(Request.Form("other_dept")) then
if not rs is nothing then
if not rs.eof then
Request.Form("dept") = rsDept("dept_id")
else
Response.Write ("Unable to Retrieve Departments (recordset is eof). Please report this message.")
success = false
end if
else
Response.Write ("Unable to Retrieve Departments (recordset is nothing). Please report this message.")
success = false
end if
else
Response.Write ("Unable to Retrieve Departments (" & errNum & ": " & errDesc & "). Please report this message.")
success = false
end if
end if
set rs = nothing

End If
%>
<!-- #INCLUDE FILE="intranet_tip.htm" -->

are you sure of the contents of "other_dept"?
While calling this function...
elseif getDeptByName(Request.Form("other_dept")) then

Is it returning False or ?

I would remove 'On Error Resume Next' untill the bugs are ironed out.

Yes, I performed a response.write and other_dept is valued.

The getDeptByName(Request.Form("other_dept")) returns True. The problem is rs.EOF is True. This is the crux of my problem. The recordset should have the newly created record. It should not be EOF. I have placed response.write's to pull the sql and execute it in MS Access and the new record is returned.

I have run with On Error Resume Next commented out. Nothing changes.

The record is created. I just cannot pull the new record. I can only pull pre-existing records.

Ok, so the function does work with existing records, but not the new one.

I'll have another look.

BTW, are you sure that the new records are there?

hmm, in setDept you did...
Dim lAffected and then executed it, but the variant is not assigned a value.

I just had a quick look and it seems that you are using a connection string that isn't set anywhere.

You have set rs twice in a row and the first should be set oConnDept.

Jim, since he mentioned .... "The insert (setDept) works" , we can assume the connection is fine.