ADO code problme!!

hi all'
this my code and work good but when i add TreeView1_NodeClick give me error message when addnew recored
curent recoredset doesn't support update!!why

Private Sub Form_Load()
Set db = New ADODB.Connection
db.Open "provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=.\DB.MDB;"
Set rec2 = New ADODB.recordset
rec2.CursorLocation = adUseClient
rec2.CursorType = adOpenKeyset
rec2.LockType = adLockOptimistic
rec2.Open "tree", db, , , adCmdTable
With TreeView1
    .Nodes.Clear 'Clear the TreeView of any nodes
    If rec2.RecordCount > 0 Then 'make sure there are records in the table
        Do While rec2.EOF = False
     If Trim(rec2.Fields("parent")) = "0_" Then 'All root nodes have 0_ in the parent field
            Set oNodex = TreeView1.Nodes.Add(, 1, Trim(rec2.Fields("key")), _
               Trim(rec2.Fields("ACCNAME")), 1, 1)
        Else 'All child nodes will have the parent key stored in the parent field
         Set oNodex = TreeView1.Nodes.Add(Trim(rec2.Fields("parent")), tvwChild, _
             Trim(rec2.Fields("key")), Trim(rec2.Fields("ACCNAME")), 1, 2)
             .Style = tvwTreelinesPlusMinusPictureText
             .Nodes(1).Expanded = True
             .Sorted = True
             End If
    End If
    End With
End Sub

Private Sub Command2_Click()
If Text2.Text = "" Then
'add parent
skey = GetNextKey() ' Get a key for the new Node
Set nodx = TreeView1.Nodes.Add("r", tvwChild, skey, CStr(t2), 1, 2)
i = TreeView1.Nodes.Count - 1
kk = i & "_"
pp = "r"
'add child
skey = GetNextKey() ' Get a key for the new Node
 iIndex = TreeView1.SelectedItem.Index 'Check to see if a Node is selected
Set nodx = TreeView1.Nodes.Add(iIndex, tvwChild, skey, CStr(t2), 1, 2)
    nodx.EnsureVisible 'make sure the child node is visible
kk = skey
i = TreeView1.Nodes.Count
pp = TreeView1.Nodes(i).Parent.Key
    End If
rec2!AccNo = t1
rec2!start = t3
rec2!typ = kkk.Text
rec2!Parent = pp
rec2!Key = kk
rec2!ACCNAME = t2

when i add TreeView1_NodeClick give me error message
 Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
 strsql = "Select * from tree where ACCNAME='" & Node.Text & "'"
 Set rec2 = db.Execute(strsql)
 Text1.Text = rec2!accNo
 Text2.Text = rec2!ACCNAME
End Sub

thanks for help
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

TimCotteeConnect With a Mentor Commented:
Hi egkids,

> rec2.Open "tree", db, , , adCmdTable
rec2.Open "tree", db, , , adCmdTable

Is the problem, you have specified empty parameters for the cursortype and locktype elements. This means that you have opened a read-only/forward-only cursor. This will not allow updating of any sort.


rec2.Open "tree", db, adOpenStatic,adLockOptimistic , adCmdTable

Instead and see what you get.


Brainbench MVP for Visual Basic

Experts-Exchange Advisory Board Member
egkidsAuthor Commented:
hi TimCottee '
it addnew recored good but when add this
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
strsql = "Select * from tree where ACCNAME='" & Node.Text & "'"
Set rec2 = db.Execute(strsql)
Text1.Text = rec2!accNo
Text2.Text = rec2!ACCNAME
End Sub
 STOP ADD and give me error "current recoredset doesn't support update but befor this code was adding!!
i try what u said but same erorr!!

egkidsAuthor Commented:
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

maybe the problem is in the sql statement .
I had the same problem like urs. and it solved by changing the sql
what is  Node.text -->> in SQL??
make it in another varuiable,a ns assign it the sql tring .
then put it in strSql

good luck
egkidsAuthor Commented:
hi all
i solved the problme but have 2 question
if 2 user add new recoreds at same time can make problme
because i didn't use open table or adoptimistic i use mostly insert into sql

2-about this sql
INSERT INTO new_table SELECT key,SUM(value) FROM my_table ORDER BY key GROUP BY key
can use sync- update with it?
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
Moderator, my recommended disposition is:

    Accept TimCottee's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
All Courses

From novice to tech pro — start learning today.