Results 1 to 15 of 15

Thread: Adding & deleting Recordsets-HELP!

  1. #1

    Thread Starter
    Hyperactive Member vbuser1976's Avatar
    Join Date
    Sep 2000
    Location
    Yonkers, NY
    Posts
    404

    Angry Adding & Deleting Recordsets-HELP!

    I am trying to save some data in a table, but I keep getting this error:
    "Run-time error '3251': Object provider is not capable of performing requested operation"

    VB debug stops at the line that says "rs.AddNew"

    Is there something I need to add at the code below that would solve this problem? Thanks a lot. Also, I need to find out how to delete a recordset.

    ######################### CODE ###########################
    Private Sub SaveData() 'this procedure saves the data.
    Dim response As Integer
    Dim rs As ADODB.Recordset
    Dim cm As ADODB.Command
    Dim p As ADODB.Parameter

    Set cm = New ADODB.Command
    Set cm.ActiveConnection = cn
    Set rs = New ADODB.Recordset
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    cm.CommandType = adCmdStoredProc
    cm.CommandText = "sp_get_atm_info"
    Set p = cm.CreateParameter("@AtmId", adChar, _ adParamInput, 10, cbAtmId.Text)
    cm.Parameters.Append p
    Set rs = cm.Execute

    If rs.EOF Then
    response = MsgBox("adding new", vbOKOnly)
    rs.AddNew
    End If

    With rs
    .Fields("AtmId") = cbAtmId.Text
    .Fields("Name") = txtCompany.Text
    .Fields("Address") = txtAddress.Text
    End With

    rs.Update
    rs.Close
    Set rs = Nothing
    End Sub

  2. #2
    Addicted Member
    Join Date
    Aug 2000
    Location
    Fremont Ohio 43420
    Posts
    253
    Ok here is what I did. I think you were forgetting to Open your recordset. Even though you might of opened in your form load or another sub you have to open it in the sub you want add, delete, or update. When you add, delete, or update the database should automatically save the data in the table.

    Private Sub cmdAdd_Click()
    On Error GoTo ErrorAdd
    Set rsMachineOperator = New ADODB.Recordset
    rsMachineOperator.Open "Select * From MachineOperator", cnn, adOpenDynamic, adLockOptimistic

    num_recs = rsMachineOperator.RecordCount

    If num_recs > 1 Then
    rsMachineOperator.MoveFirst
    rsMachineOperator.MoveLast
    Else
    If num_recs < 1 Then
    rsMachineOperator.AddNew
    rsMachineOperator!OperName = txtOpername
    rsMachineOperator!Shift = txtShift
    rsMachineOperator!Date = txtDate
    rsMachineOperator.Update
    End If
    End If

    txtOpername = ""
    txtShift = ""
    txtDate = ""
    txtOpername.SetFocus

    ExitAdd:
    Exit Sub

    ErrorAdd:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
    Resume ExitAdd

    This is how you can do the delete for records

    Private Sub cmdDelete_Click()
    On Error GoTo ErrorDelete
    Set rsMachineOperator = New ADODB.Recordset
    rsMachineOperator.Open "Select * From MachineOperator", cnn, adOpenDynamic, adLockOptimistic

    With rsMachineOperator
    .Delete
    .MoveNext
    If .BOF Then .MoveLast
    End With

    txtOpername = ""
    txtShift = ""
    txtDate = ""

    ExitDelete:
    Exit Sub

    ErrorDelete:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
    Resume ExitDelete
    End Sub

    Hope this helps you out!!

    Good Luck
    aja
    End Sub

  3. #3

    Thread Starter
    Hyperactive Member vbuser1976's Avatar
    Join Date
    Sep 2000
    Location
    Yonkers, NY
    Posts
    404

    Unhappy Nope

    It seems to not work. It keeps giving me the same error message. Any more ideas? By the way, I'm using VB6 SP4 and SQL Server 7.0.

  4. #4
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    yep

    I got this far then information stopped, ....IS ANY BODY OUT THERE... its only good manners to say "I'm sorry, i can no longer help.."


  5. #5
    Fanatic Member Ianpbaker's Avatar
    Join Date
    Mar 2000
    Location
    Hastings
    Posts
    696
    hi all ( sorry for the earlier thread gsc1ugs, I forgot all about it and haven't checked my mail)

    what Database are you using, becuase All I can find on the msdn site relating your problem is with Oracle. If you are using oracle reply back and I'll tell you what I found

    all the best

    Ian
    Yeah, well I'm gonna build my own lunar space lander! With blackjack aaaaannd Hookers! Actually, forget the space lander, and the blackjack. Ahhhh forget the whole thing!

  6. #6
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    ACCESS 2000

    Hello again..

    still got this annoying problem

    db = access 2000
    front end asp
    script = vbscript (obviously)

    here's a low-down.

    set rst = cn.Execute (strQuery)
    set rst = createObject("ADODB.Recordset")
    rst.ActiveConnection = cn
    rst.AddNew
    FOR i = 1 to rst.Fields.Count -1
    rst.Fields(i).Value = rst.Fields(i).Name
    NEXT
    rst.update

  7. #7
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    more info

    db name = contact

    I open on the fly so i want to add on the fly.

    <form name="aspweb/MLAdmin.asp?pageid=view" action="/mlintranetsite/MLAdmin.asp?pageid=view" method="POST">
    <table border=1 bordercolor="#e70f7f" cellspacing=1 cellpadding=1 rules=box>
    <tr>
    <%
    FOR i = 1 to rst.Fields.Count -1
    Response.Write "<td><B><font size=1>" & rst(i).Name & "</font></B></td>"
    NEXT
    %>
    </tr>
    <tr>
    <%
    FOR i = 1 to rst.Fields.Count -1
    Response.Write "<td><input type=text name=" & rst(i).Name & " value=" & rst(i).Name & "></B></td>"
    NEXT
    %>
    </tr>
    </table>
    <br><br>
    </form>

  8. #8
    Fanatic Member Ianpbaker's Avatar
    Join Date
    Mar 2000
    Location
    Hastings
    Posts
    696
    Hi gsc1ugs an all

    The only thing I could as I said was with Oracle, but give it a go any way

    try opening the recordset clientside using adUseClient(or 3)
    as the cursor.

    If this doesn't work then I'm out of ideas

    all the best

    Ian
    Yeah, well I'm gonna build my own lunar space lander! With blackjack aaaaannd Hookers! Actually, forget the space lander, and the blackjack. Ahhhh forget the whole thing!

  9. #9
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    HOW?

    How does one do this?

  10. #10
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    no

    I get this

    ADODB.Recordset error '800a0e78'

    Operation is not allowed when the object is closed.

    /mlintranetsite/MLAdmin.asp, line 126

    how can i make sure the object is open.

  11. #11
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    SELECT STATEMENT

    SQL = "SELECT * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%'"

    Where tblItem is declared, is it possible to use a wildcard to list all tables within the database?

    Gary

  12. #12
    Fanatic Member Ianpbaker's Avatar
    Join Date
    Mar 2000
    Location
    Hastings
    Posts
    696
    The way you need to set out you connection is as follows


    Set cn = createObject("ADODB.Connection")
    Set rst = createObject("ADODB.Recordset")

    cn.Open "your connection string"
    rst.Open strQuery,cn,3 'open as clientside cursor


    rst.AddNew
    FOR i = 1 to rst.Fields.Count -1
    rst.Fields(i).Value = rst.Fields(i).Name
    NEXT
    rst.update


    Ian

    Yeah, well I'm gonna build my own lunar space lander! With blackjack aaaaannd Hookers! Actually, forget the space lander, and the blackjack. Ahhhh forget the whole thing!

  13. #13
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    ERROR

    from th4e famous words
    " i dont believe it..!"

    ADODB.Recordset error '800a0cb3'
    Object or provider is not capable of performing requested operation.

    /mlintranetsite/MLAdmin.asp, line 146

    rst.addnew this error is pointing to

    I'm buggered..

  14. #14
    Fanatic Member Ianpbaker's Avatar
    Join Date
    Mar 2000
    Location
    Hastings
    Posts
    696
    I'm buggered too, that's me out of ideas. Only one very stupid question left (forgive me for asking) The database isn't set to read only ?

    Ian
    Yeah, well I'm gonna build my own lunar space lander! With blackjack aaaaannd Hookers! Actually, forget the space lander, and the blackjack. Ahhhh forget the whole thing!

  15. #15
    Hyperactive Member
    Join Date
    Mar 2000
    Location
    Manchester
    Posts
    446

    D'OH

    ...just kiddin, database is fine, i have a search facility
    using the RECORDSET THING.. sorry... and that works fine its adding the stuff that does'nt work.

    Gaz.

Posting Permissions

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



Click Here to Expand Forum to Full Width