Update limited number of rows (Syntax error in UPDATE statement)

Question

is there any way how to update limited number of rows using OleDb connection in C# (I am connectiong to Excel file)?

I tried following:

private static string GetConnectionString = (string path)
{
  var connectionDictionary = new Dictionary<string, string>();
  connectionDictionary["Provider"] = "Microsoft.ACE.OLEDB.12.0";
  connectionDictionary["Extended Properties"] = "Excel 12.0 XML";
  connectionDictionary["Data Source"] = path;

  var sb = new StringBuilder();

  foreach (var property in connectionDictionary)
  {
    sb.Append(property.Key);
    sb.Append("=");
    sb.Append(property.Value);
    sb.Append(";");
  }

  return sb.ToString();
}

public static void DoTheMagic(string path) 
{
  var connectionString = GetConnectionString(path)

  using (var connection = new OleDbConnection(connectionString))
  {
    try 
    {
      connection.Open();
      var cmd = new OleDbCommand { Connection = connection };
      cmd.CommandText = "UPDATE TOP (1) [TableName] 
                         SET [Column] = 'value'
                         WHERE [Column] IS NULL";
      cmd.ExecuteNonQuery();
}
    finally
    {
      connection.Close();
    }
  }
}

but this throws an error:

SystemData.OleDb.OleDbException occured in SystemData.Dall

Additional information: Syntax error in UPDATE statement

Answer 1

No. OLEDB doesn't offer row numbering or limiting functionalities in combination with Excel as 'database engine'. It has a very stripped down SQL engine.

You could add an ID field in your worksheets and update with steps of X. The problem is that if you have gaps, it might not update anything.