Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    12

    Unanswered: Current Recordset does not support updating

    Below is my code:

    Code:
    Public Sub cmdbtt_Add_Click()
    Dim con As New ADODB.Connection
    Dim lsDSN As String
    Dim objRS As New ADODB.Recordset
    lsDSN = "Provider=sqloledb;" & _
            "Network Library=DBMSSOCN;" & _
            "Data Source=127.0.0.1,1433;" & _
            "Initial Catalog=abc;" & _
            "User ID=sa;" & _
            "Password=aC852088"
    Dim numberid As Integer
    Dim sex As String
    
    con.Open lsDSN
    SQL = "SELECT * FROM id ORDER BY id"
    objRS.Open SQL, con, , 3
    objRS.AddNew
    objRS("IC") = txtb_Ic.Text
    objRS.Update
    numberid = objRS("id")
    objRS.Close
                Set objRS = Nothing
                con.Close
                Set con = Nothing
    
    con.Open lsDSN
    SQL = "SELECT * FROM Profile ORDER BY IC"
    objRS.Open SQL, con, , 3
    objRS.AddNew
    objRS("id") = numberid
    objRS("Name") = txtb_Name.Text
    objRS("IC") = txtb_Ic.Text
    objRS("Birthdate") = txtb_Birthdate.Text
        
        If op_SexM = True Then
        sex = "Male"
        Else
        sex = "Famale"
        End If
        
    objRS("Sex") = sex
    objRS("Address") = txtb_Address.Text
    objRS("HomePhone") = txtb_HomePhone.Text
    objRS("MobilePhone") = txtb_MobilePhone.Text
    objRS("ComAdd") = txtb_ComAdd.Text
    objRS("OfficePhone") = txtb_OfficePhone.Text
    
    objRS("PatPhoto").AppendChunk (Pat_PhotoAdd.Picture)
    
    objRS.Update
    objRS.Close
                Set objRS = Nothing
                con.Close
                Set con = Nothing
    MsgBox "Add", vbInformation, "Detail Add"
    
    
    End Sub

    We run the application, and add data but cause the exclamation below:

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

    How to fix it?

    Thanks

  2. #2
    Join Date
    Jun 2005
    Posts
    61
    hi
    this problem happened to me once
    and i solve it.

    i think this problem is in the size of the datafields
    for example:

    here is my data base
    ------------------------------------------------------------------
    Field Type |
    ------------------------------------------------------------------ TXT Text
    |
    |
    ------------------------------------------------------------------
    Size 50 crachters



    in ur form u entered more that 50 characters
    so this problem will appear
    ok so high ur caracters size
    i hope that will work
    Sami Al Damiri ...

  3. #3
    Join Date
    Dec 2005
    Posts
    12
    i try to enter few charater in the form but also can not work....

  4. #4
    Join Date
    Dec 2005
    Posts
    1
    1st i am newbie
    2nd i'm not sure if here is the same as MySQL so i think:

    you should Define the open type and lock type.

    and you did not define the record in the open recordset syntax.

    i'm just tring to help .. LOL

    inform me if i'm wrong.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Locking type is obvious first thing to look at as Conan mentioned. Also make sure you have a primary key defined on your target table. ADO gets pissy if it can't uniquely identify the record.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Please provide the cursor type and lock type

    Hi,
    I think you haven't specified the cursor type and lock type.
    The default cursor type is Forwardonly.So it will not allow you to update data through this cursor.Do it as below-
    rs.open"select * from table1",conn,adopenstatic,adlockoptimistic
    I think this is work for your problem.
    Best of Luck
    Joydeep

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also

    You are mixing approaches in the way you instanciate and use your database objects.

    IF you:

    Dim objCN as New ADODB.Connection

    then you should NOT set it to nothing inside your code. releasing the object is then handled automatically by VB. The only time you explicitly release an object is if you explicitly instanciate it.

    The preferred method, from an effeciency viewpoint is to:
    Code:
    Dim objCN as ADODB.Connection
    Set objCN = New ADODB.Connection
    
    ' perform your database operations, THEN
    
    objCN.Close
    set objCN = Nothing
    The reason for this is that, when declaring an ado object as new (Dim obj as New Connection), EVERY TIME the object is referenced, a block of code is run that checks to see if the object is empty, and if so, instanciates a new object, before the actual desired operation takes place. In a loop, this can add up. It also means that you can NEVER explicitly check to see if an object IsEmpty if you declare it as new, because, if it is empty, it gets instanciated automatically.

    Also, continuously opening and closing a connection is ineffecient. Open it, do all your tasks, then close it. I wouldn't recommend opening a connection and leaving it open for a long time, but, if you have multiple queries running sequentially, take care of all of them with one open/close.
    Last edited by loquin; 01-13-06 at 14:28.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Aug 2012
    Posts
    1

    Talking gud

    thanks rudhra................... its works

Posting Permissions

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