Conditional SQL statement - switching between insert/update query

Question

Is there a way to insert if/else clause into the following line here:

command.CommandText = "UPDATE Table1 SET ID=value1,Team=value2 WHERE ID=value3";

In a case where ID=value3 (in other words the row already exists) can be found then an update query takes place but where it cannot be found I want an insert query to take place:

command.CommandText = "INSERT INTO Table1 (ID,Team) VALUES (value1,value2)";

to take place... how can I do that?

private void button1_Click(object sender, EventArgs e)
{
    // save to access database when user clicks on the save button
    using (OleDbConnection conn = new OleDbConnection())
    {
        //the file path of mdb
        string filepath = @"C:\Users\sy\Visual Studio 2008\Projects\demo\demo\CE_Database.mdb";
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath + ";";
        OleDbCommand command = new OleDbCommand();
        command.Connection = conn;
        //your update satemenet
        command.CommandText = "UPDATE Table1 SET ID=value1,Team=value2 WHERE ID=value3";
        conn.Open();
        //update to ms access
        command.ExecuteNonQuery();
        conn.Close();
    }
}

Answer 1

Since command.ExecuteNonQuery(); will return the number of rows affected, you can check what this returns from the update command, if the record does not exist the method will return 0, then you can then progress to the insert method:

private string filepath = @"C:\Users\sy\Visual Studio 2008\Projects\demo\demo\CE_Database.mdb";
private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath + ";";

private void button1_Click(object sender, EventArgs e)
{       
    if (this.Update() == 0)
    {
        this.Insert();
    }
}

private int Update()
{   
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    using (OleDbCommand command = new OleDbCommand("UPDATE Table1 SET ID=value1,Team=value2 WHERE ID=value3", conn);
    {
        conn.Open();
        return command.ExecuteNonQuery();
    }
}
private int Insert()
{       
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    using (OleDbCommand command = new OleDbCommand("INSERT INTO Table1 (ID,Team) VALUES (value1,value2)", conn);
    {
        conn.Open();
        return command.ExecuteNonQuery();
    }
}

---

To clear up comments on the answer, the OleDbConnection.Dispose() method looks like this:

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

So the connection will be closed by the Dispose method, so there is no need to explicitly call Close() when your connection is in a using block