CODEWITHSUNDEEP
c#sqloracle-database
Kevin Callagher
Kevin Callagher

Reputation: 61

ORA-00904 : invalid identifier error when updating

My Global Class : 

class Global
{
    public static OleDbDataAdapter adapter;

    public static DataTable dt;
}

The procedure I'm using to fill my DataGridView :

    OleDbConnection connection;

    OleDbCommandBuilder builder;

    void gridfill()
    {

        connection = new OleDbConnection("Provider=MSDAORA;Data Source=XXX;" 
                                       + "user id=XXX;password=XXX;"
                                       + "persist security info=false;");


        Global.adapter = new OleDbDataAdapter("select \"Id\", \"UserComputer\", \"GuralID\", \"Type\", \"CreatedOn\", \"State\" from COMPUTERS", connection);

        builder = new OleDbCommandBuilder(Global.adapter);

        Global.dt = new DataTable();

        Global.adapter.Fill(Global.dt);

        dataGridView1.DataSource = Global.dt;

        dataGridView1.ReadOnly = true;

    }

The procedure I'm using to update a field in a row in my Oracle DB :

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            if (comboBox1.Text == "New")
            {
                Global.dt.Rows[rowId]["State"] = 0;
            }
            else if (comboBox1.Text == "Old")
            {
                Global.dt.Rows[rowId]["State"] = 1;
            }
            else if (comboBox1.Text == "Junk")
            {
                Global.dt.Rows[rowId]["State"] = 2;
            }
            Global.adapter.Update(Global.dt);
            this.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

When I run, I get my DataGridView filled. That part is okay. And then I double click on a row and another form shows up. That form gets values of the selected row. There's an update button on it. I use it to change the value of a field of the selected row. 3rd code I shared with you is the one to do that. But I get ORA-00904: "STATE" invalid identifier.

I debugged it. The error comes in this line : 

Global.adapter.Update(Global.dt);

TIPS:

Tables are actually created by ORM classes.

As I know this is something about double quotes.

Ex : 'Select State from COMPUTERS' does not work but 'Select "State" from COMPUTERS' does.

I used '\' prefix in my SQL query after having the same issue when filling DataGridView. The problem solved.

But I cannot use it when trying to assign a new value to the field. And I need a way to do that.

I guess the problem is here : 

Global.dt.Rows[rowId]["State"] = 0;

What can I do? Thanks.

Upvotes: 3

Views: 1780

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Try setting the QuotePrefix and QuoteSuffix on your OleDbCommandBuilder object to ".

By default, the OleDbCommandBuilder doesn't know what quotation system the database system it's talking to uses.

Upvotes: 3

Related Questions