I try to write something in a oracle database with c# (winforms) and I can only get the SELECT Statement to work, not the INSERT Statement

Question

I wrote the following code for connecting to an oracle database with my c# code:

private string GenerateConnectionString()
{
    return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = 192.168.X.XXX)( PORT = 1521 ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = XXXX ) ) ); User Id= xxxxxx; Password = xxxxxx;";
}

private void button1_Click_1(object sender, EventArgs e)
{
    try
    {
        using (OracleConnection connection = new OracleConnection(GenerateConnectionString()))
        {
            connection.Open();
            lblState.Text = connection.State.ToString();
            OracleCommand oc = connection.CreateCommand();
            oc.CommandText = "INSERT INTO TABLE (NO1, NO2, NO3, NO4, NO5, NO6, NO7, NO8, NO9, NO10, NO11, NO12, DATE) VALUES(1,2,3,1,1,1,'{txb_Textbox1.Text}',5,0.5,10,11,12,TO_DATE('09.07.2020 16:24:00', 'DD.MM.YYYY HH24:MI:SS'))";
            oc.ExecuteNonQuery();
         
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show( "Exception: " + ex.Message );
        lblState.Text = ex.Message;
    }
}

I also installed all the necessary drivers for connecting to the oracle database and added the System.Data.OracleClient.dll as a reference to my c# project and added the "oraocci19.dll" and "oraocci19d.dll" file to the project file. I also added the oracle client to the system environment variables under PATH. Furthermore, I declared using System.Data.OracleClient;at the beginning of my overall code.

Please don't tell me that I do not use the latest Oracle Data Access Components (ODACs). I know that. We have a very old Oracle Database and I like the idea that I only need to install a few oracle dll's for it to work.

I just don't know what to do and spent the whole Friday and the whole weekend researching so that I could write to the Oracle database. I hope that someone experienced recognizes the problem directly and can help me. Thank you very much in advance! :) Best regards

Edit1: Maybe I should try the other Oracle Data Access Components (ODACs) and their dlls. But normally my dll files should also work. A colleague of mine used my ODAC Installation and he said everything worked with it. But, he only had to read data from an Oracle table and not write in one.

Edit2: I got the problem! I was able to find the solution. Their was a mistake in my Oracle Prompt in the string. The C# code was correct. Here on stackoverflow I have of course reformulated and generalized the Oracle prompt string because it contains trusted data. The error was in the Oracle Command. This thread can be closed. Pete -S- got the right answer!

Perhaps you need to put the INSERT statement before executing the command? :-) — Steve, Jul 12 '20 at 14:38
@ChetanRanpariya: I get "System.InvalidOperationException" in System.Data.OracleClient.dll in the console. and the Exception: The "CommandText" property was not set. But I don't see the mistake in the command text. If I use the same command text in NaviCat, I can write in the oracle database. Also in the C# code I should pass the string as if I would type the query into a database program like NaviCat. — s950mpc2000, Jul 12 '20 at 14:48
Side note, if _{txb_Textbox1}_ is meant to insert the context of a textbox it will not work. You need txb_Textbox1.Text instead. However inserting a string typed by your user inside another string that is used to perform an sql command is a well know security weakness called Sql Injection. You should use parameters for this. — Steve, Jul 12 '20 at 14:51
@Steve: what to do you mean exactly? And why does ``oc.CommandText = "SELECT * FROM TABLE";`` works? — s950mpc2000, Jul 12 '20 at 14:52
The second command works because is executed by ExecuteReader, the first command doesn't work because you set it AFTER calling ExecuteNonQuery — Steve, Jul 12 '20 at 14:53
@Steve : Thanks for your fast answer! :) Ah ok, yeah it should take the text of a textbox from winforms as value for the oracle INSERT. Concerning paramters: this is not so important in the application case, since it is only a transfer table. I want to get the Insert command running first. It has to work tomorrow. But for another revision, I will look at parameters and solve it like this. How would you solve my case with parameters? Sorry, I am very new to this. This is the first time I'm trying to write to an Oracle database with C# code. So forgive my ignorance. — s950mpc2000, Jul 12 '20 at 14:59
@Steve : Ah thanks! I will write it before ``ExecuteNonQuery();`` I didn't saw that. I guess I need a coffee right now :D. I didn't sleep much. — s950mpc2000, Jul 12 '20 at 15:01
@Steve : I changed it. Now, it throws the exception: "ORA-00928: missing SELECT keyword" at me. I will google it. Somehow he awaits the SELECT Query, but I also deleted the lines ``OracleDataReader reader = oc.ExecuteReader();`` and the DataGrid Part concerning the Select, i.e. reading from the oracle Database. — s950mpc2000, Jul 12 '20 at 15:07

Pete -S- · Accepted Answer · 2020-07-12T15:34:51.930

1

You could try this:

//Do the insert
 oc.CommandText = "INSERT INTO TABLE (NO1, NO2, NO3, NO4, NO5, NO6, NO7, NO8, NO9, NO10, NO11, NO12, DATE) VALUES(1,2,3,1,1,1,'{txb_Textbox1}',5,0.5,10,11,12,TO_DATE('09.07.2020 16:24:00', 'DD.MM.YYYY HH24:MI:SS'))";
oc.ExecuteNonQuery;

//Retrieve in a separate action (you have to update your command to SELECT from INSERT)
oc.CommandText = "SELECT * FROM TABLE"; Statement
OracleDataReader reader = oc.ExecuteReader();

Another thing you can look at, is the CommandBuilder; but, it's the easy way out then a good solution. You can then specify the SELECT and the command builder will create the INSERT, UPDATE and DELETE commands.

Other thoughts I don't think you can bind a data reader to a .DataSource. You can load a data table from a data reader, see this example.

Here is more information on DataAdapters and DataReaders

To UPDATE/INSERT: use .ExecuteNonQuery
To SELECT: there are different options, one is to build a DataTable via DataAdapaters and bind the data source using the data table.

edited Jul 12 '20 at 15:34

answered Jul 12 '20 at 15:09

Pete -S-

542
5
13

thanks, but the I can get all the values into the dataGrid if I use „Select * from table“. The problem is that the „INSERT“ does not work and I already used the .ExecuteNonQuery method. – s950mpc2000 Jul 12 '20 at 15:14
You should split out the INSERT and the SELECT. Those are two different database operations. You can insert and select using the same code methods. – Pete -S- Jul 12 '20 at 15:15
Typically, I would have a method does the INSERT, handles exceptions and then a separate method that returns the data because you don't want to mix (decouple)your database code from your user interface. – Pete -S- Jul 12 '20 at 15:20
I know that I have to split it:) I just left the code for the "SELECT" query in my example code, because I wanted to say, that the SELECT works but not the INSERT. In the end I just need to get the INSERT to work in my code. I used the SELECT in order to check if the connection to the database works, i.e. if the connection string in my c# code was correct. I edit my example code right now. – s950mpc2000 Jul 12 '20 at 15:24
1

I think trying to do this example in one confusing thing. Does my updated code sample work for you? What I'm trying to get across is INSERTS and UPDATES are executing using one method vs SELECT uses another method. – Pete -S- Jul 12 '20 at 15:29
@ Pete -S- I used your code, but unfortunately it does not work. Of course I got a error message first, because the ``()`` were missing at ``oc.ExecuteNonQuery();``. But it still says "Exception: ORA-00928: missing SELECT keyword". But I do not understand this because I deleted everything concering SELECT in my code. I also just checked the database. Nothing new was written in it as I pressed the winforms button. – s950mpc2000 Jul 12 '20 at 15:34
You have the order of execution incorrect: you need to set the Command.Text first and then Execute: oc.CommandText = "INSERT INTO TABLE (NO1, NO2, NO3, NO4, NO5, NO6, NO7, NO8, NO9, NO10, NO11, NO12, DATE) VALUES(1,2,3,1,1,1,'{txb_Textbox1}',5,0.5,10,11,12,TO_DATE('09.07.2020 16:24:00', 'DD.MM.YYYY HH24:MI:SS'))"; oc.ExecuteNonQuery(); – Pete -S- Jul 12 '20 at 15:37
yeah I did that. It still does not work, unfortunately. Same exception. See my first post. I changed the code in that regard. – s950mpc2000 Jul 12 '20 at 16:30
What is the complete exception ex.ToString(); – Pete -S- Jul 12 '20 at 19:36

Z . · Answer 2 · 2020-07-12T15:31:50.613

You are doing it in the wrong order, you need to set the command text first and then execute the command (with ExecuteNonQuery() or ExecuteReader()):

 OracleCommand oc = connection.CreateCommand();
 oc.CommandText = "INSERT INTO TABLE (NO1, NO2, NO3, NO4, NO5, NO6, NO7, NO8, NO9, NO10, NO11, NO12, DATE) VALUES(1,2,3,1,1,1,'{txb_Textbox1}',5,0.5,10,11,12,TO_DATE('09.07.2020 16:24:00', 'DD.MM.YYYY HH24:MI:SS'))";
 OracleDataReader reader = oc.ExecuteReader();

I try to write something in a oracle database with c# (winforms) and I can only get the SELECT Statement to work, not the INSERT Statement

2 Answers2