Click here to Skip to main content
14,869,378 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all
I want to insert a new row into my table if does not exist.
When I write this code for example:
It gives me an error
Quote:
(Oracle.DataAccess.Client.OracleException: 'ORA-00911:)
Characters are not appropriate ')


What I have tried:

C#
using (OracleConnection connection = new OracleConnection(strConnection))
            {
                char quote = '"';
              string strCommand = @"insert into tableinfo( "+quote+"name"+quote+","+quote+"age"+quote+") "+
                "select :name,:age from dual where not exists (select "+quote+"age"+quote+" from tableinfo where "+quote+"age"+quote+" = :age);";
                     
                OracleCommand command = new OracleCommand(strCommand, connection);
                command.Parameters.Add( new OracleParameter("name", "Zoxit"));
                command.Parameters.Add( new OracleParameter("age", 15));

                connection.Open();
                int res = command.ExecuteNonQuery();
//Problem is here(Oracle.DataAccess.Client.OracleException: 'ORA-00911:)
//Characters are not appropriate '
                Console.WriteLine("Result = " + res.ToString());
                Console.ReadKey();

                //insert into tableinfo( "name","age") values(:name,:age)
            }
Posted
Updated 3-Oct-18 22:12pm
v2

The problem is with
string strCommand = @"insert into tableinfo( "+quote+"name"+quote+","+quote+"age"+quote+") "+
                "select :name,:age from dual where not exists (select "+quote+"age"+quote+" from tableinfo where "+quote+"age"+quote+" = :age);";
There is no need for all of those quotes

See this CP article on how to do it properly Oracle Parameterized Queries for the .NET Developer[^]
   
Thank you and I solved this problem by using. I removed last semicolon than it worked.
strCommand = @"insert into tableinfo( "+quote+"name"+quote+","+quote+"age"+quote+") "+
                "select :name,:age from dual where not exists (select "+quote+"age"+quote+" from tableinfo where "+quote+"age"+quote+" = :age)";
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900