Desperately Seeking Data (ASP/ADO)

beach
07-09-2002, 02:32 PM
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" -->

Robby
07-09-2002, 03:19 PM
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.

beach
07-09-2002, 04:31 PM
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.

Robby
07-09-2002, 09:17 PM
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?

Robby
07-09-2002, 09:25 PM
hmm, in setDept you did...
Dim lAffected and then executed it, but the variant is not assigned a value.

JIMcFadyen
07-10-2002, 02:55 AM
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.

Robby
07-10-2002, 10:02 AM
Jim, since he mentioned .... "The insert (setDept) works" , we can assume the connection is fine.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum