ORA-01000: maximum open cursors exceeded C#

I'm trying to access to another database table during an function but this exception kept appearing @ the 2nd "drOracle = cmdOracle.ExecuteReader();"

I hope someone can have me clear this error, thanks.
try
                {
                    localSQL = "SELECT KEYID,PKEYID, PRODUCT_NAME,PRODUCT_DESC FROM PRODUCT_NODE START WITH PKEYID IS NULL CONNECT BY PRIOR KEYID = PKEYID ORDER BY DT_NODE_NAME";
                    OracleCommand cmdOracle = new OracleCommand(localSQL, localConnection);
                    cmdOracle.CommandType = CommandType.Text;
                    OracleDataReader drOracle = cmdOracle.ExecuteReader();
 
                    DataTable ordt = new DataTable();
                    OracleDataAdapter orda = new OracleDataAdapter();
                    orda.SelectCommand = cmdOracle;
                    orda.Fill(ordt);
                    drOracle.Close();
                    
                    TreeNode nodeCheck = null;
                    TreeNode[] node = null;
                    string strText = string.Empty;
                    string[] strList = null;
                    string strDesc = null;
                    
                    foreach (DataRow dr in ordt.Rows)
                    {
                        nodeCheck = new TreeNode();
                        nodeCheck.Name = dr[0].ToString();                        
                        strText = dr[2].ToString();
                        strDesc = dr[3].ToString();
                        strList = strText.Split('.');
 
                        nodeCheck.Text = strList[strList.Length - 1];
 
                        if (dr[1] != DBNull.Value)
                        {
 
                            node = treeScadaItems.Nodes.Find(dr[1].ToString(), true);
                                if (strList.Length == 5)
                                {
                                    nodeCheck.Text = nodeCheck.Text + ": " + strDesc;
                                    node[0].Nodes.Add(nodeCheck);
 
                                    localSQL = "SELECT * FROM OPC_DT_PT WHERE PKEYID LIKE '" + dr[0].ToString() + "'";
                                    cmdOracle = new OracleCommand(localSQL, localConnection);
                                    cmdOracle.CommandType = CommandType.Text;
                                    //Exception Error appearing here.
                                    drOracle = cmdOracle.ExecuteReader();
 
                                    while (drOracle.Read())
                                    {
                                    }
                                }
                                else
                                { node[0].Nodes.Add(nodeCheck); }
                        }                        
                        else
                        {
                            treeScadaItems.Nodes.Add(nodeCheck);
                        } 
                        nodeCheck.Tag = dr[0].ToString();
                    } 
                }

Open in new window

csharp_learnerAsked:
Who is Participating?
 
Obadiah ChristopherConnect With a Mentor Commented:
Well that's the first time i've seen/heard that error, googling gave me this

http://harriyott.com/2006/05/contextswitchdeadlock-was-detected.aspx
0
 
Obadiah ChristopherCommented:
while (drOracle.Read())
                                    {
                                    }
drOracle.Close();

Try this code

0
 
csharp_learnerAuthor Commented:
After trying the code you mentioned the program compile for qite some time and pop-up:
ContextSwitchDeadlock was detected @ the line "drOracle = cmdOracle.ExecuteReader();"
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Obadiah ChristopherCommented:
Does this help?

OracleDataReader drOracle1 = cmdOracle.ExecuteReader();

while (drOracle1.Read())
                                    {
                                    }
drOracle1.Close();
0
 
csharp_learnerAuthor Commented:
The same error "ContextSwitchDeadlock was detected" @ the line
"OracleDataReader drOracle1 = cmdOracle.ExecuteReader();"
0
 
Obadiah ChristopherCommented:
Just try by writing this in line no 13

cmdOracle = null;

or try by creating a new oracle command
0
 
csharp_learnerAuthor Commented:
In both cases the same error came out.
0
 
csharp_learnerAuthor Commented:
I did as the site instructed and unchecked the "ContextSwitchDeadlock " the error did not appear.
But i don't think it's the solution I'm looking for, as the compliation time took very long.
0
 
Obadiah ChristopherCommented:
Have u closed the connection?
0
 
csharp_learnerAuthor Commented:
If you mean closing the 2nd drOracle1 ,yes I've closed it.
0
 
csharp_learnerAuthor Commented:
I have narrowed down to this code...
But when i put the drOracle.Close(); before the while loop i will get an
Exception Error:"Operation is not valid due to the current state of the object"
If i put the drOracle.Close();  after the while loop it'll prompt the ContextSwitchDeadlock error.
				    drOracle = null;
                                    localSQL = "SELECT * FROM PRODUCT_PT WHERE PKEYID LIKE '" + dr[0].ToString() + "'";
                                    OracleCommand cmdOracle1 = new OracleCommand(localSQL, localConnection);
                                    cmdOracle1.CommandType = CommandType.Text;
                                    drOracle = cmdOracle1.ExecuteReader();
                                    //drOracle.Close();
                                    while (drOracle.Read())
                                    {
                                    }
                                    //drOracle.Close();

Open in new window

0
 
csharp_learnerAuthor Commented:
I've decided to go with unchecking Context error for now.
0
 
Obadiah ChristopherCommented:
Well I was just searching the net and I came across this article.

Possibly this might help u.

http://forums.devx.com/showthread.php?t=16742

http://www.dotnet247.com/247reference/msgs/38/190956.aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.