CODEWITHSUNDEEP
c#sqlfirebird
Pacijent
Pacijent

Reputation: 149

Firebird unknown token where

I am executing UPDATE OR INSERT command with WHERE statement and I've got this error:

enter image description here

Here is my code:

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
  try
  {
     FbConnection con = new FbConnection(@"User = SYSDBA; Password = masterkey; Database = D:\TDWORK.fdb; DataSource = localhost; Port = 3050; Dialect = 3; Charset = NONE; Role = admin; Connection lifetime = 15; Pooling = true; MinPoolSize = 0; MaxPoolSize = 50; Packet Size = 8192; ServerType = 0; ");
     FbCommand cmd = new FbCommand("UPDATE OR INSERT INTO ZAPOSLENI (ULOGA) VALUES (" + dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() + ") WHERE ZAPID = " + dataGridView1.Rows[e.RowIndex].Cells[0].Value + " ", con);
     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
  }
  catch (Exception ex)
  {
     MessageBox.Show(ex.ToString());
  }
}

And here is how command looks like when debugger insert values in it:

UPDATE OR INSERT INTO ZAPOSLENI (ULOGA) VALUES (1) WHERE ZAPID = 0

Upvotes: 0

Views: 447

Answers (1)

scher
scher

Reputation: 1923

You cannot use WHERE in an insert or update clause (see UPDATE OR INSERT). If you want to insert a row, when there is no record with ZAPID = 0 use the following statement:

UPDATE OR INSERT INTO ZAPOSLENI (ZAPID, ULOGA) VALUES (0, 1) MATCHING (ZAPID)

EDIT: Complete example

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    try
    {
        FbConnection con = new FbConnection(@"User = SYSDBA; Password = masterkey; Database = D:\TDWORK.fdb; DataSource = localhost; Port = 3050; Dialect = 3; Charset = NONE; Role = admin; Connection lifetime = 15; Pooling = true; MinPoolSize = 0; MaxPoolSize = 50; Packet Size = 8192; ServerType = 0; ");
        FbCommand cmd = new FbCommand("UPDATE OR INSERT INTO ZAPOSLENI (ZAPID, ULOGA) VALUES (" + dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() + ", " + dataGridView1.Rows[e.RowIndex].Cells[0].Value + ") MATCHING (ZAPID)", con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Upvotes: 2

Related Questions