Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Question Unanswered: Current Recordset does not support updating. This may be a limitation of the provider

    I'm getting the above error... selecting records from db2 /as400 database - results from a join of two tables.
    Then i connect to another database to query it for a matching record. - if it's found, i extract a geo_id and then try to update the original recordset from db2. I'm getting the above error when I try to update db2 recordset.

    I already checked out http://support.microsoft.com/default...b;EN-US;174640 but I am not trying to update fields from more than one table. The line it crashes on is:
    ors.Fields("ZAURRF") = oGISRS.fiels("GEO_ID")
    Here's my code:

    Public Function GeoCodeCSMSIssues() As Boolean
    On Error GoTo ErrHandler
    Dim ocsms As CSMSDatabroker, schemaname As String, oGISRS As ADODB.Recordset, i As Long, strtemplocation As String
    Set oGISRS = New ADODB.Recordset
    Set ocsms = New CSMSDatabroker
    'Connect to CSMS
    If ocsms.Connect Then
    schemaname = ocsms.CSMSDBSchema
    'gstrSQL = "SELECT ZALOTN, ABALKY, ZASTAW, ZADOCO, ZAUPMJ, ZAUPMT FROM " & schemaname & ".F1755 F, " & schemaname & ".F0101 A WHERE F.ZAANC=A.ABAN8 AND f.ZAURRF IS NULL AND F.ZASTAW=2 AND F.ZACLST IN (100,110)"
    gstrSQL = "SELECT ZAURRF, ZALOTN, ABALKY, ZASTAW, ZADOCO, ZAUPMJ, ZAUPMT FROM " & schemaname & ".F1755, " & schemaname & ".F0101 WHERE ZAANC=ABAN8 AND ZASTAW=2 AND ZACLST IN (100,110) AND ZAURRF='" & String(15, " ") & "'"
    'Request all issues with no geocode that are active and the latest record.
    Set ors = New ADODB.Recordset
    ors.CursorLocation = adUseClient
    ors.CursorType = adOpenDynamic
    Set ors = ocsms.goConn.Execute(gstrSQL)
    If ors.EOF Then
    'no records returned. Log errors and exit
    WriteSQLLog ("GeoCodeCSMSIssues: " & gstrSQL)
    WriteAppLog ("GeoCodeCSMSIssues: No CSMS issues to geocode.")
    mstrError = "No CSMS issues to geocode"
    GeoCodeCSMSIssues = False
    ocsms.Disconnect
    Exit Function
    Else
    WriteAppLog ("GeoCodeCSMSIssues: " & ors.RecordCount & " geocodeable records found in csms")
    End If
    'ocsms.Disconnect
    Else
    WriteAppLog ("GeoCodeCSMSIssues: Unable to Connect to CSMS Database.")
    mstrError = "Unable to Connect to CSMS Database."
    GeoCodeCSMSIssues = False
    Exit Function
    End If
    'connect to gis
    If ConnectGIS Then
    ors.MoveFirst
    i = 1
    Do While Not ors.EOF
    strtemplocation = Trim(ors("ZALOTN"))
    strtemplocation = Replace(strtemplocation, "'", "''", 1, , vbTextCompare)
    If Trim(ors("ABALKY")) <> "" Then
    gstrSQL = "Select GEO_ID FROM " & gisSchemaName & ".ADDRESSES WHERE CIVIC_NO='" & strtemplocation & "' AND STRNMID=" & Trim(ors("ABALKY"))
    Debug.Print gstrSQL
    WriteSQLLog ("GeoCodeCSMSIssues: " & gstrSQL)
    Set oGISRS = goConn.Execute(gstrSQL)
    If oGISRS.EOF Then
    'no match for this record. write out to the log
    WriteAppLog ("GeoCodeCSMSIssues: No match for " & ors("ZASTAW") & " " & ors("ZADOCO") & " " & ors("ZAUPMJ") & " " & ors("ZAUPMT"))
    Else
    'update the CSMS recordset
    ors.Fields("ZAURRF") = oGISRS.fiels("GEO_ID")
    WriteAppLog ("GeoCodeCSMSIssues: Matched found for " & ors("ZASTAW") & " " & ors("ZADOCO") & " " & ors("ZAUPMJ") & " " & ors("ZAUPMT"))
    Debug.Print ors("ZAURRF") & oGISRS("GEO_ID")
    End If
    Else
    WriteAppLog ("GeoCodeCSMSIssues: Missing STRNMID for " & ors("ZASTAW") & " " & ors("ZADOCO") & " " & ors("ZAUPMJ") & " " & ors("ZAUPMT"))
    End If
    'should also invoke the .update method.
    ors.MoveNext
    Loop
    DisconnectGIS
    ocsms.Disconnect
    Else
    WriteAppLog ("GeoCodeCSMSIssues: Unable to Connect to GIS Database.")
    mstrError = "Unable to Connect to GIS Database."
    GeoCodeCSMSIssues = False
    End If
    GeoCodeCSMSIssues = True
    Set ocsms = Nothing
    Set oGISRS = Nothing
    Exit Function
    ErrHandler:
    GeoCodeCSMSIssues = False
    WriteErrorLog ("GeoCodeCSMSIssues: " & Err.Number & Err.Description)
    End Function
    Last edited by dotolee; 11-18-05 at 12:45.

  2. #2
    Join Date
    Nov 2005
    Posts
    2
    join of two tables!!!
    connection.exequte "update table1 set..."
    connection.exequte "update table2 set..."

Posting Permissions

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