Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Location
    South Africa
    Posts
    5

    Unanswered: Recordset update error

    Hi

    I'm stuck with someone else's code that is not working and the person has already left the company so I have to sort it out.

    When trying to insert or update a record(SQL Server2000) I get the following error:

    Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype

    No I know that it's better to use a query or even better a StoredProc but time is of the essense and I have to sort this out ASAP.

    The strange thing is that it works on our DEV server running Win2000 with service pack 4, but NOT on the LIVE server running the same.

    I've also installed the latest MDAC on the server but it didn't make a difference.

    Your help would be MUCH appreciated!!

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    It would help to see some code. You could also try making it a client-side recordset:

    Code:
    objRS.CursorLocation = adUseClient
    objRS.Open <SQL>, <Connection Object>, adOpenKeySet, adLockOptimistic, adCmdText
    The values for the constants are (incase you don't have the VB constants):

    adUseClient = 3
    adOpenKeySet = 1
    adLockOptimisitc = 3
    adCmdText = 1
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Use adopendynamic. that will solve ur problem surely.

    and it would be better if u can share that piece of code.


    cyrus

  4. #4
    Join Date
    Oct 2003
    Location
    South Africa
    Posts
    5

    Code

    Thanx for the responses.

    Sorry about that, here's the code:

    <%
    Dim SelectClause
    Dim WhereClause
    Dim OrderClause
    Dim AutoNum
    Dim FilterFirst

    SelectClause = "SELECT [AdminID], [menuID], [name], [description], [url], [newwin], [imgpath], [Active] FROM [MenuLinks]"
    WhereClause = ""
    If Session("CurrentSection") <> "" Then
    If WhereClause = "" Then
    WhereClause = "WHERE "
    Else
    WhereClause = WhereClause & " AND "
    End If
    WhereClause = WhereClause & "MenuID LIKE (SELECT TOP 1 menuID FROM MenuLinks WHERE Name LIKE '" & Session("CurrentSection") & "%') + '.%'"
    End IF
    OrderClause = "ORDER BY menuID"
    AutoNum = "AdminID"
    FilterFirst = True

    recordID = Request("RecordID")
    If recordID & "" = "" Then recordID = Request("navID")

    Dim MyCon
    Dim RS, strSQL

    Set MyCon = Server.CreateObject("ADODB.Connection")
    Set RS = Server.CreateObject("ADODB.Recordset")

    MyCon.Open application("ConnectionString")

    IF NOT Request.QueryString("Mode") = "AddNew" Then
    'Description string for Security Log table
    strDescription = "Edit Menu Item: " & Request("name") & " (" & Request("menuid") & ")"

    IF WhereClause & "" = "" Then
    WhereClause = "WHERE convert(varchar," & AutoNum & ") = '" & recordID & "'"
    Else
    WhereClause = WhereClause & " AND convert(varchar," & AutoNum & ") = '" & recordID & "'"
    End If
    Else
    'Description string for Security Log table
    strDescription = "Add Menu Item: " & Request("name")
    End If
    strSQL = SelectClause & " " & WhereClause & " " & OrderClause
    'response.Write
    RS.Open strSQL, MyCon, 1, 3


    If Request.QueryString("Mode") = "AddNew" Then RS.AddNew

    'Change Each field to request.form values
    ' On error resume next

    dim item
    For each item in Request.Form
    'response.Write(Request.Form(item))
    If Left(item,3) <> "cmd" Then
    Response.Write RS(item) & " = " & Request.Form(item) & "<br>"
    If Request.Form(item) & "" = "" Then
    RS(item) = Null
    Else
    RS(item) = Request.Form(item)
    End if
    End If
    Next

    IF Err.number = 0 Then RS.Update

    RS.Close
    Set RS = nothing
    MyCon.Close
    Set MyCon = nothing
    If err.number > 0 then

    %>

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Re: Code

    Originally posted by johanp
    MyCon.Open application("ConnectionString")
    Can we see "ConnectionString"? Remove the identifying information of course (db name, server name, user ID, password), but we need to see what type of Provider you're using...
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Oct 2003
    Location
    South Africa
    Posts
    5

    Re: Code

    Originally posted by Seppuku
    Can we see "ConnectionString"? Remove the identifying information of course (db name, server name, user ID, password), but we need to see what type of Provider you're using...
    Looks like they're using this:

    "DSN=DBdsn;UID=Username ;PWD=Password"

  7. #7
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Replace the line
    RS.Open strSQL, MyCon, 1, 3
    with
    RS.Open strSQL, MyCon, 2, 3
    i.e 2 - dynamic

    cyrus

  8. #8
    Join Date
    Oct 2003
    Location
    South Africa
    Posts
    5
    Originally posted by cyrus
    Replace the line
    RS.Open strSQL, MyCon, 1, 3
    with
    RS.Open strSQL, MyCon, 2, 3
    i.e 2 - dynamic

    cyrus
    Thanx! I'll give it a try and let U know if it worked.

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Re: Code

    Originally posted by johanp
    Looks like they're using this:

    "DSN=DBdsn;UID=Username ;PWD=Password"
    Where'd you see that?.. unless my "Find" isn't working, I saw no actual connection string...
    That which does not kill me postpones the inevitable.

  10. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Originally posted by cyrus
    Replace the line
    RS.Open strSQL, MyCon, 1, 3
    with
    RS.Open strSQL, MyCon, 2, 3
    i.e 2 - dynamic

    cyrus
    KeySet and Dynamic are virtually the same thing, with one difference... new records are not immediately visible in KeySet recordsets, where they are in Dynamic.. the draw back is, that added functionality decreases its performance

    Using Active Servicer Pages SE (Que)
    "The KeySet cursor is a read-write cursor type that can move throughout a recordset. Its largest limitation is its inability to see the additional records that are created. You still see changes and deletions, but additional records are a no-no.
    .
    .
    .
    The dynamic cursor is the heavyweight of cursor types. It is the most powerful. But this power comes at the cost of slower performance. Basically, use the dynamic cursor when no other cursor type will work.
    .
    .
    .
    For instance, you may have a recordset use this cursor when moving through a recordset based on all the employees in a table with the last name "Smith." If another recordset adds a new Smith before you are done, you will be able to see this new record and display it also."
    That which does not kill me postpones the inevitable.

  11. #11
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    sepakku I am aware of that but once a similar problem was faced And the soln was the same.
    Any way it was just a try and not a perfect soln

    cyrus

  12. #12
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I wasn't attempting to prove you wrong, but instead, explain the difference between the two... no more no less...
    That which does not kill me postpones the inevitable.

  13. #13
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    it ok SIR.
    I just wanted to tell u I am aware of it

    Sorry if u feeled bad

    Cyrus

  14. #14
    Join Date
    Oct 2003
    Location
    South Africa
    Posts
    5
    Thanx for the advice guys!

    I changed the connection string from DSN to OleDB and it worked

Posting Permissions

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