ADO code problme!!

Posted on 2003-03-12
Medium Priority
Last Modified: 2008-02-01
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
Question by:egkids
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 43

Accepted Solution

TimCottee earned 200 total points
ID: 8118678
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

Author Comment

ID: 8118706
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!!


Author Comment

ID: 8119008
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 8119848
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

Author Comment

ID: 8145580
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?

Expert Comment

ID: 8900240
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?
LVL 49

Expert Comment

ID: 9664454
Moderator, my recommended disposition is:

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

DanRollins -- EE database cleanup volunteer

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question