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
– Ryan S, Commented Sep 17, 2012 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
– Justin Harvey, Commented Sep 17, 2012 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
– Ryan S, Commented Sep 17, 2012 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
– Ryan S, Commented Sep 17, 2012 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
– Justin Harvey, Commented Sep 17, 2012 at 11:07

Aghilas Yakoub · Accepted Answer · 2012-09-17 12:35:43Z

1

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

edited Sep 17, 2012 at 12:35

answered Sep 17, 2012 at 10:43

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

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

2 Comments

Ryan S Over a year ago

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);

Aghilas Yakoub Over a year ago

Ryan you can read this link : msdn.microsoft.com/fr-fr/library/…

Community · Accepted Answer · 2017-05-23 12:14:16Z

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, 2017 at 12:14

CommunityBot

11 silver badge

answered May 18, 2015 at 8:31

GeorgDangl

2,2021 gold badge33 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related