How can I delete a row from an Oracle database in C#?

Question

I have a problem when I want to delete an entire row based on id_curse. This is my code and this is my error I get:

An unhandled exception of type 'Oracle.DataAccess.Client.OracleException' occurred in Oracle.DataAccess.dll.

Additional information:

External component has thrown an exception.

Can you help me to fix the problem? I think the code from the SQL-command is not good for this.

private void button3_Click(object sender, EventArgs e)
{
    Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(provider);
    Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();

    conn.Open();
    cmd = new Oracle.DataAccess.Client.OracleCommand(" DELETE * from CURSE  WHERE ID_CURSA  = '" +   textBox1.Text + "'", conn);
    cmd.ExecuteNonQuery();
}

Braud

Your code is highly insecure! Please read up on SQL injection & how to protect against it. – Menswear

Whats the innerexception? – Stanfield

Answer 1

The DELETE syntax is the following

DELETE   from CURSE  WHERE ID_CURSA  = xxxxx
       ^ no * here

But you should use parameterized queries not string concatenations. The string concatenation leads to Sql Injection and parsing problems (string with quotes, decimals and dates not recognized)

string cmdText = "DELETE from CURSE WHERE ID_CURSA  = :ID"
using(OracleConnection conn = new OracleConnection(provider))
using(OracleCommand cmd = new OracleCommand(cmdText, conn))
{
     conn.Open();
     cmd.Parameters.AddWithValue("ID", textBox1.Text);
     cmd.ExecuteNonQuery();
}

As a side note, are you sure that the ID_CURSA is a text database field? (You are putting quotes around the value so I assume that is a text field)

Australopithecus