I have created table in Oracle 11g,

CREATE TABLE "TABLE1" 
(
    "COLUMN1" VARCHAR2(20 BYTE), 
    "COLUMN2" VARCHAR2(20 BYTE)
) 

connect with System.Data.OleDb, the connectionStrings is:

<connectionStrings>
    <add name="DBConnStr" connectionString="Provider=msdaora;Data Source=test;User Id=test;Password=test;" />
</connectionStrings>

and try to insert data into table TABLE1:

private void testInsert()
{
    string sql = "";
    int recordsAffected = 0;

    using (OleDbConnection openCon = new OleDbConnection(constr))
    {
        sql = @"insert into table1 (column1, column2) values (:aaa, :bbb)";
        using (OleDbCommand command = new OleDbCommand(sql))
        {
            command.Connection = openCon;

            command.Parameters.AddWithValue(":aaa", "test");
            command.Parameters.AddWithValue(":bbb", "0123456");

            openCon.Open();
            recordsAffected = command.ExecuteNonQuery(); // <-- error here
            openCon.Close();
        }
    }
}

but get ORA-01008 error at command.ExecuteNonQuery () line. Why is Oracle claiming that not all variables are bound?

If I replace the colon (:) prefix with @, I got ORA-00936 error. And if I remove all prefix, I got ORA-00984 error. What is the correct way to insert data into Oracle table?

Thanks in advance.

Related posts

Recent Viewed