asp.NET C# - Problems with ExecuteNonQuery() with Oracle Commands

Question

I have this problem with a query I have, I am using Oracle Commands and Parameters. I have hundreds of other queries in my code, but just this one is failing to execute. It is a very simple update query and it works in SQL Navigator because I tried it.

Within the code, the execute non query method seems like it freezes and i get "Waiting for http:// localhost:8081/MaintainUserProfile.aspx" in my webpage.

I appreciate your help because I've wasted hours on it and I'm clueless at this stage.

Let me know if I should state more information.

The code below (Please note I work with oracle parameters, but in this case I am just using plain strings to debug the problem, the same happens with oracle parameters, it get stuck on execute non query):

string sqlQuery = @"UPDATE schema_name.table_name
                    SET
                       officer_name = '" + fullName + 
                       "', channel_code = " + channelCode +
                       ", male_female_ind = '" + maleFemale + 
                       "', user_status_code = '" + userStatusCode +
                       "', identity_number = '" + idNumber +
                       "', extension_number = " + extensionNumber +
                       " WHERE user_profile_id = " + userProfileID;

 OracleCommand oraCommand2 = new OracleCommand(sqlQuery, db);
 oraCommand2.ExecuteNonQuery();

@Justing Harvey - I am not getting any error, it fails to execute and remains like it is executing and I end up waiting endlessly. — Ryan S, Sep 17 '12 at 10:39
How much data is in that table? I am wondering if it is possible if it is hanging just finding the row to update. — Justin Harvey, Sep 17 '12 at 10:42
@Justing Harvey - This should update a single row, it is updating a user, from a list of around 1,200 users. — Ryan S, Sep 17 '12 at 10:43
@Justin Harvey - Having said that, a similar update query in a different page works perfectly, without hanging. This is the funny thing. — Ryan S, Sep 17 '12 at 10:43
Are you totally sure it is the SQL that is hanging and not something else in the aspx page request? — Justin Harvey, Sep 17 '12 at 11:07
@Justin Harvey - I'm sure because the code is stopping in that executeNonQuery() and not proceeding to the rest of the code when debugging. — Ryan S, Sep 17 '12 at 11:23
I wonder if it is hanging waiting on a lock then. Have a look at this thread http://stackoverflow.com/questions/1172378/oracle-update-hangs — Justin Harvey, Sep 17 '12 at 12:30

Aghilas Yakoub · Answer 1 · 2012-09-17T12:35:43.093

You can try with this code - Based on AddWithValue

oraCommand2.CommandText="UPDATE schema_name.table_name SET
                       officer_name = :fullName , channel_code = :channelCode, 
                       male_female_ind = :male_female_ind, user_status_code = :user_status_code,
                       identity_number = :idNumber, extension_number = :extensionNumber 
                       WHERE user_profile_id = :userProfileID";

oraCommand2.Parameters.AddWithValue(":fullName", fullName);
oraCommand2.Parameters.AddWithValue(":channelCode", channelCode);
oraCommand2.Parameters.AddWithValue(":male_female_ind", male_female_ind );
oraCommand2 .Parameters.AddWithValue(":user_status_code", user_status_code );
oraCommand2 .Parameters.AddWithValue(":identity_number", identity_number );
oraCommand2 .Parameters.AddWithValue(":extension_number", extension_number );
oraCommand2 .Parameters.AddWithValue(":user_profile_id", user_profile_id );

Link : http://msdn.microsoft.com/fr-fr/library/system.data.oracleclient.oracleparametercollection.addwithvalue.aspx

Yaoub - Are you sure there is an AddWithValue in the Oracle Commands API, I can only see and Add method. This is how I usually work which still doesn't cut it: OracleParameter oraParamater6 = new OracleParameter(":param6", OracleDbType.Varchar2, 10); oraParamater6.Value = idNumber; oraCommand.Parameters.Add(oraParamater6); — Ryan S, Sep 17 '12 at 11:27
Ryan you can read this link : http://msdn.microsoft.com/fr-fr/library/system.data.oracleclient.oracleparametercollection.addwithvalue.aspx — Aghilas Yakoub, Sep 17 '12 at 12:35

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

Kenneth answered it in this Post, it may be due to an uncommitted operation in SQLDeveloper (or any other uncommitted pending database change).

edited May 23 '17 at 12:14

Community

1
1

answered May 18 '15 at 08:31

GeorgDangl

2,146
1
29
37

asp.NET C# - Problems with ExecuteNonQuery() with Oracle Commands

2 Answers2