Updating recordset using OLEDB

deltatrend · #1 (**permalink**) 03-30-04, 16:13

This a strange. I am using a normal oledb connection to an Access DB:

Code:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\Databases\Demo\Relotracker.mdb;User Id=;Password=;"

The problem is that whenever I change a stored parameter query in Access, the next time I get a recordset with it and try to update it, I get this:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

The way I have to fix it is to run the stored query in Access to retrieve a live record. Then I am able to update the recordset withoutany problems. The strange thing is that there is not problem when I connect using a DSN.

Here is the code for the update routine, although I suspect it is the connection that is at fault:

Code:

' Get Employee Record from Cur_Emp table
  strStoredProc = "qCurrEmp" & ProgName
  strParamName = "parE_NO"
  strParamValue = NTopic
  
  Call ExeStoredProcUpd(strStoredProc,strParamName,strParamValue,objRSUpd)
  
' Loop Through Each Value and Update it
  For each inputField in Request.Form
    Select Case inputField
      Case "Submit","E_NO","EmpType","Action","AgentProp"
        i = 1
      Case Else
        For each inputValue in Request.Form(inputField)
          If inputValue = "" Then 
            objRSUpd.Fields(inputField).Value = null 
          Elseif
          (code for adjusting date strings omited)
          Else
            objRSUpd.Fields(inputField).Value = inputValue
          End if
        Next
    End Select
  Next

  objRSUpd.Update
  objRSUpd.Close
  Set objRSUpd = Nothing

And here is the subroutine:

Code:

Sub ExeStoredProcUpd(strStoredProc,strParamName,strParamValue,strRecordset)
  ' executes a Stored procedure that requires a parameter based on a
  ' value from the page. The result passed to the named recordset object.
  ' Then the Recordset is not disconnected so that it can be updated.
  
  On Error Resume Next

  Set objCmd = Server.CreateObject("ADODB.Command")
  objCmd.CommandText = strStoredProc
  objCmd.CommandType = adCmdStoredProc
  Set objCmd.ActiveConnection = objConn

  Set objParam = Server.CreateObject("ADODB.Parameter")
  objParam.Name = strParamName
  objParam.Direction = adParamInput
  objParam.Type = adVarChar
  objParam.Size = Len(strParamValue)
  objParam.Value = strParamValue
  objCmd.Parameters.Append objParam

  Set strRecordset = Server.CreateObject("ADODB.Recordset")
  strRecordset.CursorLocation = adUseServer
  strRecordset.CursorType = adOpenDynamic
  strRecordset.LockType = adLockOptimistic
  strRecordset.Open objCmd
End Sub

I sure hope someone has some ideas, because this is causing a lot of grief.

Davids
DeltaTrend

gyuan · #2 (**permalink**) 03-30-04, 18:14

What does the stored procedure do? From the error message, the item to be updated does not match the one you selected.

deltatrend · #3 (**permalink**) 03-30-04, 18:44

Quote:

Originally posted by gyuan
What does the stored procedure do? From the error message, the item to be updated does not match the one you selected.

Yes, I know that's what it says. The stored queries are usually something like:

Code:

SELECT Emp_Cur.E_NO, Emp_Cur.SURNAME,  ProgBillMan.BMFieldA, ProgBillMan.BMFieldB, ProgBillMan.BMFieldC
FROM Emp_Cur INNER JOIN ProgBillMan ON Emp_Cur.E_NO = ProgBillMan.E_NO
WHERE (((Emp_Cur.E_NO)=[parE_NO]));

Nothing complicated. And it's not just on this one, but on all of them, unless I change the connection to DSN.

The recordset just retrieves the information that was in the db and tries to update it, like this:
Page
Get RS based on stored query
Disconnect RS
Show Form
Show RS in form
Close RS
Submit

Get changes to Form
Get RS based on stored query
Update RS*
Close RS

Get RS based on stored query
Disconnect RS
Show Form
Show RS in form
Close RS
End Page

All fairly mundane, except that if I have changed the underlying stored query in Access, it won't update the RS, unless I open Access and run the query and get a live result. After that it works fine.

DavidS

deltatrend · #4 (**permalink**) 04-01-04, 18:06

No Ideas?

Internet Services

Web Development

Digital Marketing

Consumer Tech