Problem with cmd.ExecuteNonQuery()

Question

I am inserting values in to Database from a Webform using ADO.NET, C#. DB I am using is Oracle Database. Values are not being inserted and the program gets struck at the cmd.ExecuteNonquery()

Here is my Code below, Please let me know If I am doing any mistake.. I am using some Static Methods will that be any problem ?..

public Boolean AddDivCo(Int32 UserNo,String ID, String Role, String DivName )
{
    Boolean ret = false;


    OracleCommand cmd = new OracleCommand();
    OracleConnection conn = new OracleConnection();
    int i = 0;

    try
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["Conn_RIS"].ConnectionString;
        conn.Open();

        cmd.Connection = conn;
        String mySQL = "INSERT INTO R4CAD_ADMIN (AdminUserNo, AdminID, AdminRole, AdminDivName)VALUES(:AdminUserNo,:AdminID,:AdminRole,:DivName)";

        OracleParameter p1 = new OracleParameter("AdminUserNo", OracleType.Number);
        p1.Value = UserNo;
        cmd.Parameters.Add(p1);

        OracleParameter p2 = new OracleParameter("AdminID", OracleType.VarChar);
        p2.Value = ID;
        cmd.Parameters.Add(p2);

        OracleParameter p3 = new OracleParameter("AdminRole", OracleType.VarChar);
        p3.Value = Role;
        cmd.Parameters.Add(p3);

        OracleParameter p4 = new OracleParameter("DivName", OracleType.VarChar);
        p4.Value = DivName;
        cmd.Parameters.Add(p4);

        cmd.CommandText = mySQL;

        i = cmd.ExecuteNonQuery();

        if (i != 0)
        {
            ret = true;
        }
        else
        {
            ret = false;
        }
    }
    catch (Exception err)
    {
        Console.WriteLine(err.Message.ToString());
    }
    finally
    {
        cmd.Dispose();
        //cmd = null;
        //conn = null;
        conn.Close();
    }
    return ret;
}

DCookie · Accepted Answer · 2010-07-12 14:47:06Z

3

Is there a primary key defined on this table? If so, then my guess is that you have another session that already has inserted a record with this key, but has not yet terminated the transaction with a commit or rollback. I don't see a commit as part of your code - I assume you're doing that somewhere else?

Execute your code above once more, and while it's hung run the following query from another session:

SELECT
      (SELECT username FROM v$session WHERE sid=a.sid) blocker,
       a.sid,
      ' is blocking ',
       (SELECT username FROM v$session WHERE sid=b.sid) blockee,
           b.sid
  FROM v$lock a JOIN v$lock b ON (a.id1 = b.id1 AND a.id2 = b.id2)
 WHERE a.block = 1
   AND b.request > 0;

This should tell you if you're being blocked by another session and what the SID is of that session.

answered Jul 12, 2010 at 14:47

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

msbyuva Over a year ago

Thanks for your response. Yes, I do have a Primary Key on that Table but I am not inserting the values from any other way because when I run select Query on that Table from my Database it doesn't return any rows. As said by you while its hung, I ran the SELECT SQL provided by you. I got the OUTPUT as: 121 FOIA is blocking FOIA 129 121 FOIA is blocking FOIA 133 I did not understand from that OutPut.... Please can you Help ?

msbyuva Over a year ago

121 FOIA is blocking FOIA 129 121 FOIA is blocking FOIA 133

msbyuva Over a year ago

Thank You So much Sir... When I ran that Query, I realized that there is one more Instance running. I shutdown my system ran my program again, this time I was able to INSERT Successfully and when I ran the SQL Code, I didn't see any OUTPUT. From that I understood there was another Instance of SQL Developer was opened. Am I right ?..

DCookie Over a year ago

Hard to say from here what it was. The basic rule is this: If session "A" inserts a row in a table with a primary key, session "A" will block ANY other session attempting to insert the same primary key until session "A" commits or rolls back the insert. All other attempts to insert will "hang" until then.

msbyuva Over a year ago

Yeah, Makes sense from your above sentences and the Output I got when I ran the SQL. Thank You!..

|

Collectives™ on Stack Overflow

Problem with cmd.ExecuteNonQuery()

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related