Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Belgium
    Posts
    6

    Unanswered: Updating recordset using OLEDB

    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

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    What does the stored procedure do? From the error message, the item to be updated does not match the one you selected.

  3. #3
    Join Date
    Aug 2003
    Location
    Belgium
    Posts
    6
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Belgium
    Posts
    6
    No Ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •