CODEWITHSUNDEEP
c#oracle-databasewinformsado.netexecutenonquery
Amit Bisht
Amit Bisht

Reputation: 5136

ExecuteNonQuery stuck without throwing error

It must be a silly question but don't know what's wrong with my code
I'm trying to insert some data in oracle database here is my piece of code 

try
{                
  OracleConnection con= new OracleConnection("Data Source=ANTI01T.world ;User Id=MyDB;Password=XYZ123 ;");
  con.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection =con;
  cmd.CommandText = query;
  cmd.CommandType = CommandType.Text;
  cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  MessageBox.Show(ex.Message);
}

where query is 

string query = @"INSERT INTO TB_RULE_HISTORY
                                (   N_RULE_ID,
                                    N_RULE_VERSION,
                                    DT_START_DATE,
                                    DT_END_DATE,
                                    N_CURRENCY_ID,
                                    N_VALUE1,
                                    N_VALUE2,
                                    N_PERCENTAGE,
                                    N_MONTHS)
                                    VALUES(" 
                                    + pRuleId.ToString() + " , "
                                    + pRuleVersion.ToString() + " , '"
                                    + DateTime.Now.Date.ToString("dd/MMM/yy") + "' , '"
                                    + pEndDate.ToString("dd/MMM/yy") + "' , "
                                    + pCurrencyId.ToString()+ " , "
                                    + pValue1.ToString()+ " , "
                                    + pValue2.ToString()+ " , "
                                    + pPercentage.ToString()+ " , "
                                    + pMonths.ToString() + ")";

The code is not throwing any error it got stuck don't know where
The possible check I did is that I remove the semicolon from the query by referring Link1 because executing query with ; gives ORA-00911: invalid character, but now it is not throwing any error just got stuck.
Also there is no transaction in use.
Please suggest the possible way to trace it out

Upvotes: 0

Views: 3218

Answers (5)

rohit kumar rai
rohit kumar rai

Reputation: 11

I had the same problem. In my case, I connected to my database from another system and add some columns to the existing table.

After that to verify and check, I added some new rows, deleted some rows etc, and simultaneously I was debugging/executing my code from another system.

As a result it got stuck at cmd.ExecutenNonQuery() while executing delete from tablename.

So from another PC (where I logged into my database), I tried to exit from the database.

Where it was asked that the transaction you made is unchanged..

Do you want to commit? Or rollback??

I selected commit. Then I returned to my code and executed it. There after no further hanging at ExecuteNonQuery.

Conclusion: problem with a transaction in your database

Upvotes: 1

Amit Bisht
Amit Bisht

Reputation: 5136

The issue I found with this is due to my oracle transaction is not auto commit.
Thus it was getting hanged.

Upvotes: 1

Michael Broughton
Michael Broughton

Reputation: 4055

Could be an issue with the NLS Date format on your connection as you are passing in the dates with the string value and letting the connection implicitely convert to a DATE type.

Counting on implicit conversions are always dangerous for long-term implementations, and especially risky for date formats where your code could work on one deployed instance but not another due to environment settings.

So put your date strings into a SQL TO_DATE() function, and specify the format explicitly. I'd also recommend going to the four-digit year for entering any historical dates (birth dates etc) or future dates as letting the database guess the century when they don't know the context of the data can lead to incorrect results.

string query = @"INSERT INTO TB_RULE_HISTORY
                                (   N_RULE_ID,
                                    N_RULE_VERSION,
                                    DT_START_DATE,
                                    DT_END_DATE,
                                    N_CURRENCY_ID,
                                    N_VALUE1,
                                    N_VALUE2,
                                    N_PERCENTAGE,
                                    N_MONTHS)
                                    VALUES(" 
                                    + pRuleId.ToString() + " , "
                                    + pRuleVersion.ToString() + " ,  TO_DATE('"
                                    + DateTime.Now.Date.ToString("dd/MMM/yy") + "','dd/mm/yy') , TO_DATE('"
                                    + pEndDate.ToString("dd/MMM/yy") + "','dd/mm/yy') , "
                                    + pCurrencyId.ToString()+ " , "
                                    + pValue1.ToString()+ " , "
                                    + pValue2.ToString()+ " , "
                                    + pPercentage.ToString()+ " , "
                                    + pMonths.ToString() + ")";

Upvotes: 1

are
are

Reputation: 2615

it can be an issue with your string values can you try to use parameters? it can resolve issue with string values and ...it's common practice to use parameters for number of reasons

string query = "INSERT INTO TB_RULE_HISTORY
(   
N_RULE_ID,
N_RULE_VERSION,
DT_START_DATE,
DT_END_DATE,
N_CURRENCY_ID,
N_VALUE1,
N_VALUE2,
N_PERCENTAGE,
N_MONTHS)
VALUES(:pRuleId
,:pRuleVersion
,:pDateTimeStart
,:pDateTimeEnd
,:pCurrencyId
,:pValue1
,:pValue2
,:pPercentage
,:pMonths)";    


try
{                
  OracleConnection con= new OracleConnection("Data Source=ANTI01T.world ;User Id=AML_UAT;Password=AML_UAT;");
  con.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection =con;
  cmd.CommandText = query;
  cmd.CommandType = CommandType.Text;

  cmd.Parameters.AddWithValue("pRuleId", pRuleId);
  cmd.Parameters.AddWithValue("pRuleVersion", pRuleVersion);

  ...

  cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
  MessageBox.Show(ex.Message);
}

Upvotes: 1

Elim Garak
Elim Garak

Reputation: 1817

First take the value of your string query and run that in Oracle. You have some string values that are surrounded with single quotes, and some like pValue1, pValue2 that are not. I would take the actual Insert from your string query and then run that.

Oracle will hang and do nothing if there is a problem in the script at times. This can be seen by running an invalid script in PL SQL command window. It hangs there waiting for a response.

Second, are you running the insert in PL SQL with the same ID as the connection string? If not you need to prefix the SCHEMA name in front of the insert such as INSERT INTO SCHEMAX.TABLE_NAME. There is no Initial Catalog for Oracle as another person noted, that applies to SQL Server.

Upvotes: 1

Related Questions